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?
2 Answers 2
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.
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,