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?
You're not trying to connect as
root
but asdbadmin
. Your entry inpg_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