Thread: ident authentication problems postgresql 9.2

ident authentication problems postgresql 9.2

From
Rory Campbell-Lange
Date:
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


Re: ident authentication problems postgresql 9.2

From
Adrian Klaver
Date:
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


Re: ident authentication problems postgresql 9.2

From
Rory Campbell-Lange
Date:
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


Re: ident authentication problems postgresql 9.2

From
Tom Lane
Date:
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


Re: ident authentication problems postgresql 9.2

From
John R Pierce
Date:
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



Re: ident authentication problems postgresql 9.2

From
Rory Campbell-Lange
Date:
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