Peer authentication failed for user "db_user"

Question

I am trying to set-up automatic backup for postgres db on a local headless server on Debian. I have a script:

#!/bin/bash
export PGPASSFILE='/home/mtn/.pgpass'
pg_dumpall -U db_user --verbose 2>/var/log/postgresql/pgdump.log | gzip > /mnt/bulk-data/db_backup/db_bak.gz

Have a .pgpass file:

-rw-------  1 mtn  mtn     47 Nov 13 10:14 .pgpass

with:

*:*:*:postgres:guest
*:*:*:db_user:guest

And a sudo crontab -e job:

20 0 * * * /home/mtn/backup.sh >/dev/null 2>&1

pg_hba:

local   all             postgres                                peer

When i try to run it i get:

pg_dumpall: error: could not connect to database "template1": FATAL:  Peer authentication failed for user "db_user"

Where's the mistake?

PS Everything works if i change the script to run as root sudo -u postgres pg_dumpall.

UPDATE: What worked for me in the end is adding this line to pg_ident.conf:

omicron           root                    postgres

Then to pg_hba.conf before everything else:

local   all      all                 ident map=omicron

And changing script to run pg_dumpall as user postgres (only because db_user didn't have all necessary privilegies to dumpall).

jjanes · Accepted Answer · 2020-11-13 23:48:47Z

2

In the absence of a user map via pg_ident.conf, only a linux user named "db_user" is allowed to log in as the database user "db_user". That is what peer authentication means. Your .pgpass doesn't matter, as peer authentication doesn't use passwords.

The one line from pg_hba you show also doesn't matter, because "postgres" != "db_user" so that line doesn't match. But clearly you have other lines as well which you haven't shown us. If you look in the server's log file you should find more details about the error, and which line of pg_hba was used.

There are lots of solutions. You could change your linux username to match the postgres username, or change the postgres username to match the linux username, or use pg_ident.conf to map between them, or change your pg_hba (the correct line of it!) to use md5 rather than peer.

answered Nov 13, 2020 at 23:48

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Peer authentication failed for user "db_user"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related