2

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.

2 Answers 2

3

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)

3
  • Is it bad security to add this line: "host all 127.0.0.1/32 trust" ?
    – Ian
    Oct 29, 2009 at 17:35
  • Ye,s that's a very bad idea unless you trust everybody on your machine. Including potential attackers coming in through other systems. Oct 29, 2009 at 18:24
  • Also, doing host 127.0.0.1/32 is inferior performance wise to local authentication. Local connections use a UNIX domain socket. When you use that, you get the ability to use another auth mechanism called ident (the OS can tell a program the name of the user at the other end of the pipe). So for local connections, ident is safer than trust. TCP/IP connections to localhost are slower and you can't do ident auth with them.
    – James F
    Oct 30, 2009 at 7:39
1

Note that if a postgresql account is configured to be able to use both ident and md5, then by default it will try to use ident.

You can force it to use username/password (ie md5), by adding the option --host=localhost to the end of the psql commandline or similar.

This forces the connection to go through sockets, which disables ident, and therefore forces the username/password authentication.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .