Fixing Authentication Issues with pgpass on Debian

crondebianpostgresql

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

Best Answer

peer authentication means postgresql accepts connections over unix socket from specified unix system user without password as specified database user, so trying to use PGPASS is pointless. More about peer access here: https://www.postgresql.org/docs/current/auth-peer.html In your case, your pg_hba.conf allows only system user postgres to connect all databases as database user postgres. I can't see your db_user in your pg_hba.conf, so it can't connect. sudo -u postgres pg_dumpall means you switch to system user postgres and run pg_dumpall as that user connecting as database user postgres. That's why it works.

Related Topic