ident authentication problems postgresql 9.2

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general