How can I get pg_dump to authenticate properly
pg-dump, postgresql
Solution
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
Problem
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" ```