Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

postgresqlpostgresql-9.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

Best Answer

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.

Related Question