PostgreSQL create user with login

Question

I have an app that access multiple databases on a single PostgreSQL server. I liked very much the Microsoft SQL Server concept where you create a CREATE LOGIN app_user WITH PASSWORD 'xxx' on the server-level. Then, you create CREATE USER app_user WITH LOGIN app_user on database level.

You manage security issues like password retention, disable/enable user etc on server-level (which is handy feature if the database count is more than 20)

Is this doable also on PostgreSQL?

Thanks a lot!

• sticky bit: Users/roles are also valid per server in Postgres.

Answer 1

Postgres works differently, but you can achieve the same.

There is no distinction between a "user" and a "login".

Postgres only has roles - a role with the "login" privilege is commonly referred to as a "user" (see the manual for create user)

Access to a database is granted based on the connect privilege that enables a user (=a role with the login privilege) to connect to a specific database.

By default newly created users can connect to any database in the instance (aka "cluster") because the role public has the privilege connect granted by default. This privilege is granted per database.

If you want to allow every created user to connect to every database, there is nothing you need to do - this is the default. Just create the users (note that those users won't be able to do anything meaningful with the databases as long as they don't they get additional privileges to access or create objects in those databases).

If you want more fine grained control, remove the connect privilege of the public role for every database.

Then grant the connect privilege to the roles (users) you want to allow to connect to a specific database.

---

There is a second level of access control based on the host based authentication and controlled through editing the pg_bha.conf. I prefer opening up all access for regular users (on the internal network of course) through pg_hba.conf and controlling concrete access through SQL and explicit GRANTs - this is a bit easier to manager as you don't need to edit a server side file. But this is very much a matter of personal preferences. If you need other level of access control e.g. based on the client IP protocol or authentication method, then pg_hba.conf is the only way to do that.