PostgreSQL User Permissions

accountspermissionspostgresqlusers

I'm trying make a transition from MySQL to PostgreSQL, but find the user accounts/permissions system to be quite different.

With mysql, all accounts are handled internally by mysql. With postgres is seems I need to make an account within the system, as well as within postgres.

Is there a guide somewhere as to how system user accounts relate to postgres user accounts? The postgres manual didn't seem to elaborate much on this.

Best Answer

You only need to create system users if you want to use local authentication.

For network connections, you use CREATE USER and GRANT much as you are used to in MySQL.

The main difference is that in unlike MySQL where the authentication and authorization parts are all inside of tables in the mysql database, part of the authentication rules for Postgres are in an external file pg_hba.conf).

To let user foo connect to database bar from 10.1.1.0/24, you want a line like this in that file:

host bar foo 10.1.1.0/24 md5

After editing pg_hba.conf, remember to run

pg_ctl reload

to reload the config.

When the user tries to connect, they will be required to provide a password (which you provided when you created the user).

Also remember that in PostgreSQL everything is a role. A role that you define as 'WITH LOGIN' is a user while one that does not have this flag is more like a group. For flexibility, I tend to create users with passwords and WITH LOGIN. I then create roles without passwords and grant this role to the login users. My grants are to the group-like role rather than to individual users.

In pg_hba.conf, use +role to match anyone who has the role assigned (instead of foo in the above example)

Related Topic