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?

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

| improve this answer | |
  • When it's that way round I get the error "psql: FATAL: role "root" does not exist " – RLBChrisBriant Jun 9 '19 at 5: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 9 '19 at 19:04
  • It has the line "local all postgres peer", before. – RLBChrisBriant Jun 10 '19 at 6: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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.