How Can I Access the postgres database in a Script Executing pg_dumpall? 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?
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.