I've got a working postgreSQL database with following pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all               md5
host    all             all             ::1/128                 md5
hostssl all             dbadmin         my.ip.at.home/32       md5

Which, to my understanding is correct, and only allows local connections and from my home-ip via SSL. Both via a md5 password.

I now want a local bash script to run some things on the databases (backups). For this I created a new user backup_user; as the script needs to run autonomous my thoughts were to create a line for this user to trust from the the lo:

host   all           backup_user    trust

But when I login via SSH and run this command I have to type the password....

psql -U backup_user -p 5454

What is the best way to let the script do its thing? If it matters, I want this script to sun: Automated Backup

  • 2
    Why not set the auth method to md5 for the backup user, and use a .pgpass file? postgresql.org/docs/current/static/libpq-pgpass.html
    – bma
    Nov 28, 2013 at 19:48
  • Hmm because I didn't kow of the existence off .pgpass.... Where on my (ubunutu) server should I find this file?
    – stUrb
    Nov 28, 2013 at 20:33
  • 3
    For connecting through Unix domain socket (and that's how you connect as you don't specify a host via -h) replace that line with local all backup_user peer and move it at just after the first line. Nov 28, 2013 at 20:35
  • O.K. I don't want to use .pgpass as the password is stored in there as plain text.
    – stUrb
    Nov 28, 2013 at 21:03
  • 3
    Then move your original line just before this one: host all all md5. Nov 28, 2013 at 21:20

1 Answer 1


Since you want the backup user to connect locally from the database server. I assume the backup is running on a dedicated backup account on the OS.

Using pg_hba.conf you can specify like:

local   all      backup_user    peer map=local_peers_backup_user

(method peer (with local/peer an address is not needed))

combine this with a pg_ident.conf like:

local_peers_backup_user  postgres          backup_user
local_peers_backup_user  OSBKPUSER         backup_user

This allows the os user postgres and OSBKUSER to connect to all databases in the cluster as the postgres database user named backup_user, using peer authentication. This works using sockets, on linux.

connected as your OSBKPUSER running psql suffices to get connected as backup_user, assuming that you already prepared the environment variables for the OSBKPUSER and OSBKPUSER has only 1 map. Otherwise run psql -U backup_user.

This connect method is secure, if you trust that the access to your dbserver is secured.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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