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.

Have you actually added the user (with CREATE USER or an equivalent method)? — jcaron
– jcaron, Commented Aug 11, 2017 at 10:28
The user was created through an ansible script: - name: "Grant All Privileges to {{ DB_USER }}" become: yes become_user: postgres postgresql_privs: db: "{{ DB_NAME }}" role: "{{ DB_USER }}" privs: ALL type: schema objs: public — user1919
– user1919, Commented Aug 11, 2017 at 10:33
This is the user specs from pgadmin: CREATE ROLE sparc2 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; — user1919
– user1919, Commented Aug 11, 2017 at 10:34

Amir reza Riahi · Accepted Answer · 2023-04-28 11:36:39Z

10

PostgreSQL has 2 connection entry points:

TCP/IP (host in pg_hba.conf)
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.

edited Apr 28, 2023 at 11:36

Amir reza Riahi

2,4702 gold badges16 silver badges49 bronze badges

answered Aug 11, 2017 at 11:18

filiprem

7,2131 gold badge34 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1919 Over a year ago

Great. Thanks a lot for the answer. The fist option worked. I am not an expert with these customizations. Are there any risks doing this?

filiprem Over a year ago

@user1919 the only risk of changing peer to md5: some automation scripts that rely on "peer" auth will stop working. They will start asking for password.

Collectives™ on Stack Overflow

Peer authentication failed for user in postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related