skip to Main Content

My cluster is configured to make use of the peer authentication on local connections, which is described as follows:

The peer authentication method works by obtaining the client’s
operating system user name from the kernel and using it as the allowed
database user name (with optional user name mapping).

So if I try to connect to a database cluster not being logged in as a user with a name that has its correspondence among the database roles, I will get the expected error:

john@john:~$ psql -d postgres
psql: error: FATAL:  role "john" does not exist

On the other hand, there is psql‘s -U username option, which enables us to

Connect to the database as the user username instead of the default.
(You must have permission to do so, of course.)

But when I repeat my previous steps with this option I get the error which is somewhat different (there is a role with the name postgres)

john@john:~$ psql -d postgres -U postgres
psql: error: FATAL:  Peer authentication failed for user "postgres"

My question is about how the peer authentication and the -U option work together. It seems that if I use the latter the server is not interested in my client’s operating system user name anymore (in the opposite case I would see the first error). But under what basis I then do not allowed to connect to the database?

Probably it’s due to the peer authentication it is not just about matching up OS/-U user names with PostgreSQL roles but also about restricting some OS users from database access at all (so, the OS user john can’t gain it even by providing the name which the PostgreSQL server actually knows – postgres)

Have I got it right?

2

Answers


  1. There are two different user names:

    • the name of the operating system user you used to log into the database machine

    • the name of the database user that you specify with -U

    You can only connect with peer authentication if those two names are identical.

    For advanced purposes, you can specify a mapping in pg_ident.conf that defines which OS user can connect as which DB user.

    Login or Signup to reply.
  2. If you set up a user mapping (in pg_ident.conf) and activate it (in pg_hba.conf) which says john is allowed to log in as postgres, then he will be able to do that.

    If any OS user could log in as any PG user unconditionally, how would ‘peer’ be different than ‘trust’?

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search