PostgreSQL: FATAL - Peer authentication failed for user (PG::ConnectionBad)

Question

I am working with PostgreSQL and I have a user with password matching the one specified in database.yml

postgres=# select * from pg_user
;
  usename   | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig 
------------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 goodsounds |    16386 | t           | t        | t         | t       | ******** |          | 
 postgres   |       10 | t           | t        | t         | t       | ******** |          | 
(2 rows)

But when I try creating a database by running the command

rails db:create

I get the error

FATAL: Peer authentication failed for user "goodsounds"

Here is my pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

Previously "trust" above was md5 but I changed to see if that would help.

Here is my database.yml:

# PostgreSQL. Versions 8.2 and up are supported.
#
# Install the pg driver:
#   gem install pg
# On Mac OS X with macports:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
development:
  adapter: postgresql
  encoding: unicode
  database: goodsounds_development
  pool: 5
  username: goodsounds
  password: test

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  host: localhost
  port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # The server defaults to notice.
  #min_messages: warning

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  encoding: unicode
  database: goodsounds_test
  pool: 5
  username: goodsounds
  password: test

production:
  adapter: postgresql
  encoding: unicode
  database: goodsounds_production
  pool: 5
  username: goodsounds
  password: test

Craig Ringer Craig Ringer · Accepted Answer · 2013-03-09T07:48:36

"Peer authentication" means that it's using a unix socket and expecting the connecting unix user to have the same unix username as the postgresql username.

Since your local unix username is funkdified and you're trying to connect as user goodsounds over a unix domain socket (local) connection where your pg_hba.conf specifies peer authentication, Pg correctly rejects your connection attempt.

This is the default behaviour for many installs when using unix sockets.

You can:

Connect via TCP/IP by specifying a hostname in your database connection settings;
edit pg_hba.conf to use md5 password authentication instead of peer authentication for unix sockets (local connection type) so Pg accepts password authentication; or
Connect with a PostgreSQL username the same as your unix username and create the user in PostgreSQL if it doesn't exist yet.

See the docs for pg_hba.conf and the rest of the client authentication chapter of the documentation.

Note that changes to pg_hba.conf do not take effect immediately, you must restart or at least reload PostgreSQL to get it to reread pg_hba.conf.

Oh, also, if you have multiple PostgreSQL versions installed you might have a libpq from one version and a server from another. In this case make sure the location for the unix socket that libpq connects to by default is the same as the server's unix_socket_directories or override it with (e.g.) host=/tmp in your connection string.

PostgreSQL: FATAL - Peer authentication failed for user (PG::ConnectionBad)

5 Answers