How can I get pg_dump to authenticate properly
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"
Answer
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