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?

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... — Markus L, Dec 18 '14 at 13:10
Did you try the reverse ? Create a linux user for your existing database user — nos, Dec 18 '14 at 14:30

score 2 · Accepted Answer · answered Dec 19 '14 at 07:34

2

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.

answered Dec 19 '14 at 07:34

Markus L

932
2
20
38

8

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. – krvkir Oct 18 '16 at 12:50
1

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. – Markus L Oct 18 '16 at 13:13
1

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. – krvkir Oct 18 '16 at 14:45

psql: FATAL: PAM authentication failed for user

1 Answers1

Linked