0

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?

0

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.

0

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,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.