Thread: Re: Test mail for pgsql-general
I am confused about authentication. I understand that in the local connection case, I have choices of “peer”, and “md5” (password).
In pg_hba.conf, I have the lines:
local all all peer
local all all md5
I have an OS user “postgres”, and I can “su – postgres”, which brings me to a shell and I can invoke psql successfully.
I believe that, as root, I should be able to “psql -U postgres -W” and logon with a password. I can’t. When I try, I get:
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
Notice I am failing “peer” authentication. Seems to me that if I explicitly ask for a password, “-W”, I should be using “md5” authentication.
Can anybody straighten me out?
Hi Folks,I am confused about authentication. I understand that in the local connection case, I have choices of “peer”, and “md5” (password).
In pg_hba.conf, I have the lines:
local all all peer
local all all md5
On 9/10/24 16:21, Chris Miller wrote: > Hi Folks, > > I am confused about authentication. I understand that in the local > connection case, I have choices of “peer”, and “md5” (password). > > > In pg_hba.conf, I have the lines: > > > local all all peer > > local all all md5 > > > I have an OS user “postgres”, and I can “su – postgres”, which brings me > to a shell and I can invoke psql successfully. > > > I believe that, as root, I should be able to “psql -U postgres -W” and > logon with a password. I can’t. When I try, I get: > > > psql: error: connection to server on socket > "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication > failed for user "postgres" > > > Notice I am failing “peer” authentication. Seems to me that if I > explicitly ask for a password, “-W”, I should be using “md5” authentication. First match wins loses in this case. The entries are processed top to bottom the first the one matches in this case: local all all peer Per https://www.postgresql.org/docs/16/auth-pg-hba-conf.html "The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied." The -W is a no-op per: https://www.postgresql.org/docs/16/app-psql.html -W --password Force psql to prompt for a password before connecting to a database, even if the password will not be used. > > > Can anybody straighten me out? > > > Thanks for the help, > -- > Chris. -- Adrian Klaver adrian.klaver@aklaver.com
The second line is pointless, the first three columns are compared against the incoming connection host/user/dbname to find out how authentication should be handled. The first match wins. So for every local connection peer, and only peer, is going to be used since everything matches all/all.There is no way to give a user a choice of how to authenticate. There will be one accepted option for a given set of connection values.
Hi Adrian, > First match wins loses in this case. The entries are processed top to > bottom the first the one matches in this case: > > local all all peer > > Per This answers my question. Thanks for the help, -- Chris.