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?

Improve this question

1 Answer 1

Reset to default
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

Improve this answer
5
  • When it's that way round I get the error "psql: FATAL: role "root" does not exist "
    – RLBChrisBriant
    Jun 9, 2019 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, 2019 at 19:04
  • It has the line "local all postgres peer", before.
    – RLBChrisBriant
    Jun 10, 2019 at 6:03
  • Hm, that line is supposed to be there. I'm out of ideas then, sorry.
    – flwd
    Jun 13, 2019 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, 2019 at 18:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .