How Can I Access the postgres database in a Script Executing pg_dumpall?

Question

I want to run the following script using my role which includes the postgres database.

pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql

Here are the settings in pg_hba.conf:

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections: 
host all all ::1/128 md5

Initially when I wrote this script I had the methods in pg_hba.conf set to trust. The script ran fine at that time. However when I changed the methods to md5 the script fails with an authentication error when it tries to access the postgres database. The password on my role works when access the databases that I created.

I need to either connect as user postgres to access the postgres database in my script or give access to the postgres database to my role. I would prefer the latter since my script is executed by my role.

I found the following example in the PostgreSQL documentation for pg_hba to allow access to the postgres database to all users with a valid password which is what I want to do.

# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.12.10/32 md5

I am using a Mac Mini Server where my Rails applications using PostgreSQL are hosted. I have the external IP address (IPv4) that I use to remotely access my server at the colocation site. Is this the IP address I should use in pg_hba.conf or should I use the router IP address? If I add this statement to pg_hba.conf will I need to modify the existing IPv4 statement to accommodate this new rule?

Answer 1

I decided to modify my pg_hba.conf file to the following:

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host postgres all 127.0.0.1/32 md5
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

I'm now able to execute my script using my role and successfully access the postgres database.

Answer 2

If you want to run the pg_dumpall without passing the user (-U) and the password (-W) you must change your pg_hba.conf from:

local all all md5

to

local all all trust 

Assuming that you want to allow all unix users/accounts that is in the database host will be access your Postgres via Unix Domain Sockets.

If you want to go secure, and keep with md5 in all rules that will mean that you need send the user and thte password through the parameters of pg_dumpall command. Please look also: -h, -U, -W,