2

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?

RLBChrisBriant
  • 595
  • 1
  • 7
  • 22

1 Answers1

2

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

flwd
  • 121
  • 1
  • When it's that way round I get the error "psql: FATAL: role "root" does not exist " – RLBChrisBriant Jun 09 '19 at 05:04
  • It works for me when using these exact entries. Do you have other entries in `pg_hba.conf` before the ones you have posted? Postgres will only check the first entry that matches the connection so if there's a different entry that does not allow you to connect it will stop there and deny access. – flwd Jun 09 '19 at 19:04
  • It has the line "local all postgres peer", before. – RLBChrisBriant Jun 10 '19 at 06:03
  • Hm, that line is supposed to be there. I'm out of ideas then, sorry. – flwd Jun 13 '19 at 18:42
  • I have tried something new, but still can't get it to work. I have added to my original post, can anyone help? – RLBChrisBriant Jun 16 '19 at 18:47