POSTGRES - psql: FATAL: Peer authentication failed for user "root"

Question

I am trying to configure peer authentication for root on Postgres v9.5. My aim is to allow the cron jobs to run pgdump to create backups.

My pg_hba.conf file looks as follows:

local all postgres md5 local all root peer map=mymap 

My pg_ident.conf file looks like below:

# MAPNAME SYSTEM-USERNAME PG-USERNAME mymap root dbadmin 

The error when running psql to test as root user is:

psql: FATAL: Peer authentication failed for user "root"

I have seen things like this on here, but they suggest the settings I am already trying, but it doesn't work. Is anyone able to help or advise on a different solution?

I have tried creating a 'backup' user with the commands below on postgres:

CREATE ROLE backup WITH LOGIN PASSWORD 'backuppasswd'; GRANT CONNECT ON DATABASE mydb To backup; GRANT USAGE ON SCHEMA public TO backup; GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup; 

I then have run below:

export PGPASSWORD="backuppasswd" 

I have then created a crontask with the entry below:

14 11 * * * pg_dump -U backup -h localhost mydb > /<mypath> 

If I run "pg_dump -U backup -h localhost mydb > /<mypath>" from the command line then it creates the backup, but if I let the cron job run then it creates an empty file.

Can someone help?

Answer 1

You're not trying to connect as root but as dbadmin. Your entry in pg_hba.conf should look like this:

local all dbadmin peer map=mymap

then you need to run psql with the user aswell: # psql -U dbadmin