psql: FATAL: PAM authentication failed for user

Question

PostgreSQL 9.2.4

I cannot login anymore with a user that has worked in the past. I assume a configuration problem. So for testing I created a test user role 'testing' with the same options as the role in question (as the 'postgres' user who is still possible to login!):

CREATE ROLE testing WITH SUPERUSER NOCREATEDB NOCREATEROLE LOGIN PASSWORD 'pw';

The role was created successfully:

                                  List of roles
     Role name  |                   Attributes                   | Member of
    ------------+------------------------------------------------+-----------
     postgres   | Superuser, Create role, Create DB, Replication | {}
     testing    | Superuser                                      | {}
     ...

However, a login results in this message:

# psql -h 127.0.0.1 -p 5434 -U testing
Password for user testing:
psql: FATAL:  PAM authentication failed for user "testing"
FATAL:  PAM authentication failed for user "testing"

I also get this error when I try with the original role.

pg_hba.conf:

# "local" is for Unix domain socket connections only
local   all         all                               pam
# IPv4 local connections:
host    all         all         0.0.0.0/0             pam

Does anybody have a clue what the problem might be?

Treat

It turns out that for PAM authentication a linux user with that name has to exist. So I created a database role for an existing linux user and was able to log in. But I'm still not able to log in with the original user... – Treat

Did you try the reverse ? Create a linux user for your existing database user – Ormond

Answer 1

The main problem was that the linux user didn't exist for the database role that tried to login. There were a few other minor configuration issues as well (linux groups, rights) that needed to be fixed. Login was then possible again.

Treat

Could you please give some details on what did you do to solve the problem besides creating unix user? I'm struggling with the same issue and trying to figure out what happened. – Brusque

Sorry, it's some time ago. Check the following things: user exists, user in correct group(s), file permissions, folder access rights. All these things must be correct else you'll get the described error. If you're using a .pgpass file it must have file permission 600. – Treat

Thanks, ultimately it helped :) I've found that when I did useradd or gpasswd several days before, it reseted ACL for /etc/shadow which was configured to allow postgres to read it. Restoring this permissin solved the problem. – Brusque