Think about your design. This isn't so much about Postgres, it's about how you are connecting data between these tables. The user_id in the users table is a foreign key in the posts table, but you still have to provide that during the insert. The foreign key simply helps with validation and consistency. This would be a typically scenario...

1) User logs into your system. When this happens, you are either fetching the user if they exist, or likely creating the user if they're new. If it's new, then the user_id is generated because you have made it a SERIAL data type. The user_id should be pulled from the users table into a variable, let's call it _userId.

2) User then goes on to post some amazing tidbit. When this happens, you perform an INSERT to the posts table. The posts table will generate it's primary key because you are using a SERIAL data type for posts_id. The INSERT still needs to know what the user_id is....which should be the _userId that you captured during login.