Thread: ident authentication problems postgresql 9.2
I have just done an update on my Debian servers running postgresql 9.2 (Postgres is from 9.2.4-2.pgdg70+1 from apt.postgresql.org) and suddenly can't login to postgresql as postgres with the normal peer/ident authentication over a local Unix socket. I've worked around it (I'm using md5 for the time being) but I wonder if anyone else has experienced this problem which is affecting all 3 of my 9.2 servers. I can't find anything in the recent Debian security updates that could have broken this (i.e. recent stuff at http://www.debian.org/security/) By the way there is a key expired error on http://apt.postgresql.org. Regards Rory
On 10/16/2013 06:56 AM, Rory Campbell-Lange wrote: > I have just done an update on my Debian servers running postgresql 9.2 > (Postgres is from 9.2.4-2.pgdg70+1 from apt.postgresql.org) and suddenly > can't login to postgresql as postgres with the normal peer/ident > authentication over a local Unix socket. So is there any pertinent information in either the Postgres or system logs? > > I've worked around it (I'm using md5 for the time being) but I wonder if > anyone else has experienced this problem which is affecting all 3 of my > 9.2 servers. > > I can't find anything in the recent Debian security updates that could > have broken this (i.e. recent stuff at http://www.debian.org/security/) > > By the way there is a key expired error on http://apt.postgresql.org. An explanation and fix for the key expired error can be found here: http://blog.endpoint.com/2013/10/postgresql-debian-apt-repository-gnupg.html > > Regards > Rory > > -- Adrian Klaver adrian.klaver@gmail.com
On 16/10/13, Adrian Klaver (adrian.klaver@gmail.com) wrote:
> On 10/16/2013 06:56 AM, Rory Campbell-Lange wrote:
> >I have just done an update on my Debian servers running postgresql 9.2
> >(Postgres is from 9.2.4-2.pgdg70+1 from apt.postgresql.org) and suddenly
> >can't login to postgresql as postgres with the normal peer/ident
> >authentication over a local Unix socket.
>
> So is there any pertinent information in either the Postgres or system logs?
I've just turned the setting in pg_hba.conf from md5 back to peer and
reloaded the server so that it now reads as:
local all postgres peer
Attempting to log in fails with the following error on the console:
psql: FATAL: Peer authentication failed for user "postgres"
Thanks for the suggestion for checking the logs. My apologies for not
checking that. The logs show:
2013-10-16 16:21:16 BST template1 LOG: local user with ID <abc> does not exist
2013-10-16 16:21:16 BST template1 FATAL: Peer authentication failed for user "postgres"
However /etc/passwd has
postgres:x:<abc>:<def>:PostgreSQL administrator,,,:/db:/bin/bash
I do not have an ident server running and never have had as far as I
recall.
> >By the way there is a key expired error on http://apt.postgresql.org.
>
> An explanation and fix for the key expired error can be found here:
> http://blog.endpoint.com/2013/10/postgresql-debian-apt-repository-gnupg.html
Thanks very much. Removing /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg
fixed the problem I was experiencing.
Thanks very much
Rory
--
Rory Campbell-Lange
Rory Campbell-Lange <rory@campbell-lange.net> writes:
> Thanks for the suggestion for checking the logs. My apologies for not
> checking that. The logs show:
> 2013-10-16 16:21:16 BST template1 LOG: local user with ID <abc> does not exist
> 2013-10-16 16:21:16 BST template1 FATAL: Peer authentication failed for user "postgres"
> However /etc/passwd has
> postgres:x:<abc>:<def>:PostgreSQL administrator,,,:/db:/bin/bash
Hm. A look at the PG source code shows conclusively that the "local user
does not exist" error comes out only if getpwuid() fails. Why it's
failing isn't too clear though. Perhaps your upgrade accidentally made
/etc/passwd non-world-readable, or some such?
regards, tom lane
On 10/16/2013 8:34 AM, Rory Campbell-Lange wrote:
> I've just turned the setting in pg_hba.conf from md5 back to peer and
> reloaded the server so that it now reads as:
>
> local all postgres peer
>
> Attempting to log in fails with the following error on the console:
>
> psql: FATAL: Peer authentication failed for user "postgres"
>
> Thanks for the suggestion for checking the logs. My apologies for not
> checking that. The logs show:
>
> 2013-10-16 16:21:16 BST template1 LOG: local user with ID <abc> does not exist
> 2013-10-16 16:21:16 BST template1 FATAL: Peer authentication failed for user "postgres"
>
> However /etc/passwd has
>
> postgres:x:<abc>:<def>:PostgreSQL administrator,,,:/db:/bin/bash
>
> I do not have an ident server running and never have had as far as I
> recall.
# su - postgres
$ psql
does that give the same error?
re ident server, domain sockets ('local') use a different mechanism, not
authd aka identd... authd/identd would only be used if you specified
ident on a HOST connection, something I wouldn't recommend.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
On 16/10/13, Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Rory Campbell-Lange <rory@campbell-lange.net> writes:
> > 2013-10-16 16:21:16 BST template1 LOG: local user with ID <abc> does not exist
> > 2013-10-16 16:21:16 BST template1 FATAL: Peer authentication failed for user "postgres"
>
> > However /etc/passwd has
> > postgres:x:<abc>:<def>:PostgreSQL administrator,,,:/db:/bin/bash
>
> Hm. A look at the PG source code shows conclusively that the "local user
> does not exist" error comes out only if getpwuid() fails. Why it's
> failing isn't too clear though. Perhaps your upgrade accidentally made
> /etc/passwd non-world-readable, or some such?
Hi Tom. Thanks for your response.
I'm sure I'm doing something silly, but /etc/password is 0644, and I've
checked my etckeeper logs for changes over the past month and the passwd
file hasn't changed.
...
reset
...
I've just done an upgrade to 9.2.5 and the ident issue is resolved. The
package changes were:
-libpq5 9.3.0-2.pgdg70+1
+libpq5 9.3.1-1.pgdg70+1
-postgresql-9.2 9.2.4-2.pgdg70+1
+postgresql-9.2 9.2.5-1.pgdg70+1
-postgresql-client-9.2 9.2.4-2.pgdg70+1
+postgresql-client-9.2 9.2.5-1.pgdg70+1
-postgresql-contrib-9.2 9.2.4-2.pgdg70+1
+postgresql-contrib-9.2 9.2.5-1.pgdg70+1
Regards
Rory