How can I get pg_dump to authenticate properly

Question

I have tried using host variable PGPASSWORD and .pgpass and neither of these two will allow me to authenticate to the database. I have chmod'd .pgpass to appropriate permissions and also tried:

export PGPASSWORD=mypass and PGPASSWORD=mypass

The password DOES contain a \ however I was encasing it in single quotes PGPASS='mypass\' and it still will not authenticate.

I'm running:

pg_dump dbname -U username -Fc

and I still receive

pg_dump: [archiver (db)] connection to database "dbname" failed: FATAL:  Peer authentication failed for user "username"

Puto

That "...Peer authentication..." part of the error message means that it's not using password authentication at all. – Moneybags

Answer 1

The Quick Solution

The problem is that it's trying to perform local peer authentication based on your current username. If you would like to use a password you must specify the hostname with -h.

pg_dump dbname -U username -h localhost -F c

Explanation

This is due to the following in your pg_hba.conf

local   all             all                                     peer
host    all             all             127.0.0.1/32            md5

This tells Postgres to use peer authentication for local users which requires the postgres username to match your current system username. The second line refers to connections using a hostname and will allow you to authenticate with a password via the md5 method.

My Preferred Development Config

NOTE: This should only be used on single-user workstations. This could lead to a major security vulnerability on a production or multi-user machine.

When developing against a local postgres instance I like to change my local authentication method to trust. This will allow connecting to postgres via a local unix socket as any user with no password. It can be done by simply changing peer above to trust and reloading postgres.

# Don't require a password for local connections
local   all             all                                     trust

Watershed

For anyone unsure where your conf file is: use sudo locate pg_hba.conf -- it has to be sudo since the postgres user will be the only one with access to the directory (I think). – Thebes

your command worked for me but i can not find out where it is, or what is the name of file, how can i find that file? – Structuralism

If I configure my Postgres inside Docker, will this setup work for connections coming my localhost? – Uplift

Answer 2

Sometimes you can use

sudo -u postgres pg_dump ...

Sosa

For the purposes a scheduling a cron to perform routine backups as root this won't work. – Inainability

... but still works for the purposes a scheduling a cron to perform routine backups as postgres. – Crenelation

@RabbanKeyak Sure, but what good is that if you need to copy it to the cloud or backup directly to a mounted network folder that postgres isn't the owner of? Correct me if I'm wrong, but unless you modify the postgres user there is no password to perform sudo actions. But then if you need to do anything cron-related that involves sudo then you should just create a root cron. – Inainability

@Inainability In case you are copying it to the cloud, it will be request for authorization from the cloud not depending on which user you are at the moment: root, postgres, whatever. In general cases. In case you need to make dump directly to a directory without permissions it's usually possible to add permissions; to make it through intermediate directory (and setup another root cron to copy the dump from it to destination directory); to use root cron with something like su - -c 'pg_dump ...' postgres > /path/for/dump. – Crenelation

@RabbanKeyak That has not been my experiences. You just configure rclone and then you can just copy to the cloud pretty effortlessly. I could probably reduce the number of steps, but I use pg_dump to backup the database to a local directory on the server then use rclone to send it to the cloud (i.e., Backblaze B2, AWS, etc.). This can be set up as a daily cron job. – Inainability

Answer 3

I had to look this up again after figuring it out a few weeks ago for work (but trying to set up a postgresql database at home now), but you may want to modify your pg_hba.conf file.

What I did is comment out the default "local" specification as it authenticates all local users using the peer method. This post from Database Administrators helped shed some light on this.

In the end, your pg_hba.conf file should look something like this:

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   <db_name>    <user>                                   password

Then, if you want to schedule the backup with cron or something all you have to do after restarting the service is:

PGPASSWORD=$(cat /home/<user>/.pg-pass) pg_dump -Fc -U <pg_user> -d <db_name> > /home/<user>/<db_name>.dump

Where the dump file is output is irrelevant so I just put it in my home folder for the time being, but for work I have this same style code setup in a script that creates a daily backup and then rclone to the cloud.

Inainability