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
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.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’?