1

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

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            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     127.0.0.1/32    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

9
  • 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 '13 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 '13 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. – Milen A. Radev Nov 28 '13 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 '13 at 21:03
  • 3
    Then move your original line just before this one: host all all 127.0.0.1/32 md5. – Milen A. Radev Nov 28 '13 at 21:20
0

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:

# TYPE  DATABASE USER   ADDRESS METHOD
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:

# MAPNAME                SYSTEM-USERNAME   PG-USERNAME
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, privacy policy and cookie policy

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