Peer authentication failed for user in postgresql

Question

I am trying to run some postgresql commands through a fabric script. When I execute the script I get:

out: psql: FATAL:  Peer authentication failed for user "sparc2"

This is how my pg_hba.conf file looks like:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
# added
local   sparc2          sparc2                                  md5
host    sparc2          sparc2           127.0.0.1/32           md5
host    sparc2          sparc2           10.0.2.2/32            md5
host    all             all              all                    password

I have also modified the postgresql.conf file with adding this line:

listen_addresses = '*'

After applying the changes I restarted postgresql. But the error is still the same.

Answer 1

PostgreSQL has 2 connection entry points:

1. TCP/IP (host in pg_hba.conf)
2. Unix sockets (local in pg_hba.conf)

Your server is configured to use peer auth which works only for Unix sockets, and means - ask the kernel if the OS username matches DB username.

You have following options:

• change pg_hba.conf to use md5 auth for local socket connections, or
• change connection settings in your script to use IP connection (127.0.0.1 should work) instead of socket connection. [ This may not require editing the files - sometimes setting PGHOST variable is enough ], or
• make your script to run from OS user sparc2, not postgres.

---

Risks / drawbacks

• if you change peer to md5, some automation scripts that run from "postgres" OS user, and rely on "peer" auth, will stop working. They will start asking for password
• if you change peer to md5, and forget database superuser password, you may have to re-enable peer auth to reset it.

In general, the "peer" auth is OK. Ease and security of kernel-based local auth is the reason why many distributions choose it for local admin connections. It is useful especially on multi-user shell servers. You can disable it for selected accounts only:

#CHANNEL  DB    USER     METHOD
local     all   sparc2   md5
local     all   all      peer

More details: here and here.