How can I get pg_dump to authenticate properly

Solution 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

Solution 2:

Sometimes you can use

sudo -u postgres pg_dump ...