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:
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?