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?
Best Answer
I decided to modify my pg_hba.conf file to the following:
I'm now able to execute my script using my role and successfully access the postgres database.