CODEWITHSUNDEEP
postgresql
Viktor
Viktor

Reputation: 489

Peer authentication failed for user "db_user"

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).

Upvotes: 0

Views: 1018

Answers (1)

jjanes
jjanes

Reputation: 44137

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.

Upvotes: 2

Related Questions