124

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"
Jim Mitchener
  • 8,835
  • 7
  • 40
  • 56
Kosmonaut
  • 2,154
  • 2
  • 18
  • 19
  • 2
    That "...Peer authentication..." part of the error message means that it's not using [password authentication](http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-PEER) at all. – Milen A. Radev May 03 '12 at 12:47

2 Answers2

245

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
Jim Mitchener
  • 8,835
  • 7
  • 40
  • 56
  • 6
    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). – jcollum Jun 25 '14 at 18:15
  • 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? – Sobhan May 12 '20 at 12:01
  • If I configure my Postgres inside Docker, will this setup work for connections coming my localhost? – Jack Chi Sep 04 '20 at 22:52
18

Sometimes you can use

sudo -u postgres pg_dump ...
Roland Pihlakas
  • 4,246
  • 2
  • 43
  • 64