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

psql: fatal: peer authentication failed for user "postgres"
psql fatal peer authentication failed for user alfresco
pg::connectionbad: fatal: password authentication failed for user "postgres"
pg connectionbad fatal peer authentication failed for user pguser
pg_restore fatal: peer authentication failed for user postgres
psql: fatal: password authentication failed for user
fatal: password authentication failed for user postgres windows
psql: fatal peer authentication failed for user docker

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

How to fixing error: Peer authentication failed for user “username” in , I try to using Postgresql with Ruby on Rails to test production in my I run “rails db:create” showed “PG::Peer authentication failed for user� In this article we will learn how to get rid of FATAL: Peer authentication failed for user “postgres” error by enforcing password authentication over Unix sockets peer method, First, navigate to the /etc/postgresql/10/main directory. cd /etc/ postgresql /10/ main Note that 10 is the PostgreSQL version it can be different for you.

I was facing same problem on Ubuntu machine so I removed this error by following some steps. Switch to postgres user

$ sudo su - postgres

it will ask for password and by default password is postgres

After switch the user to postgres, open psql console

$ psql

so check the version of postgres if multiple versions are available

psql=# select VERSION();

PostgreSQL 9.1.13 on x86_64-unk....         # so version is 9.1

Now Open postgres user

vim /etc/postgresql/9.1/main/pg_hba.conf

9.1 is version return form upper command

and replace

local   all             postgres                                peer

to

local   all             postgres                                md5

Restart the service

sudo service postgresql restart

I write steps on my blog also

http://tarungarg402.blogspot.in/2014/10/set-up-postgresql-on-ubuntu.html

FATAL: Peer authentication failed for user (postgres), If you're trying to create or setup your rails postgreSQL daatabase and getting this error, there may 3 possible reasons for this error. Let's go� Now I've only installed postgres once, so I may be wrong here, but I think postgres automatically creates an administration role with the same credentials as the user you installed postgres as. 4b) So this means you need to change to the user that installed postgres to use the psql command and start the shell:

If "peer authentication" does not work, try md5 authenticaion.

To specify host try something like this:

 psql -d <dbname> -U <username> -h <hostname>

or this:

 psql -d <dbname> -U <username> -h <hostname> -W

Solution of psql: FATAL: Peer authentication failed for user “postgres , psql: FATAL: Peer authentication failed for user “postgres” (or any user). The connection failed because by default psql connects over UNIX� Your connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user dev and then login as dev or use sudo -u dev psql test_development for accessing the database (and psql should not ask for a password).

edit /etc/postgresql/9.3/main/pg_hba.conf

# "local" is for Unix domain socket connections only
    local   all             all                                     peer

change to below line and it works for me

# "local" is for Unix domain socket connections only
local   all             all                                     md5

"FATAL: Peer authentication failed for a database user" when , PG::ConnectionBad: FATAL: Peer authentication failed for user "myuser" maclover7 added activerecord PostgreSQL labels on Dec 19, 2016. The application was started by the students from previous year and now it's me and my colleagues turn to continue work on it. I took the application from github, I run bundle install, but when to run rake db:migrate I got this PG::ConnectionBad: FATAL: password authentication failed for user "alphauser". In database.yml I have these

Here's how I solved it;

Run the command below to confirm your PostgreSQL version.

psql --version

Navigate to the PostgreSQL configuration directory on your server, which is in /etc/postgresql/10/main. Take note that 10 is the version of my PostgreSQL installation on my server. Your version might be 9.5, 11 or 12 or any other version.

cd ~
cd /etc/postgresql/10/main

Once you navigate into the /etc/postgresql/10/main directory, open the file pg_hba.conf using the command below. This file controls: which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use, which databases they can access:

sudo nano pg_hba.conf

Replace the following line below:

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

with the line below:

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

Also, replace the following line below:

# "local" is for Unix domain socket connections only
local       all       all            peer

with the line below:

# "local" is for Unix domain socket connections only
local       all       all            md5

Additionally, we may want to allow inbound connections to the PostgreSQL database in production (to allow host connections from all databases, all users, all addresses using the md5 method).

To achieve this, add the line below at the end of the file, and then save the file:

# remote connections
host      all      all      all      md5

Still within the /etc/postgresql/10/main directory, open and edit the file postgresql.conf using the command below:

sudo nano postgresql.conf

Replace the line # listen_address='127.0.0.1' or the line listen_address='127.0.0.1' or the line # listen_address='localhost' or the line listen_address='localhost' with the line below, in order to allow PostgreSQL database to listen to connections from all addresses:

listen_addresses = '*'

Save the file, and navigate to the root directory of your server:

cd ~

Restart or reload the PostgreSQL server using the command below:

sudo systemctl restart postgresql       # To restart
sudo systemctl reload postgresql        # To reload

psql: FATAL: Peer authentication failed for user, In a fresh install from a few days ago, the second line of my pg_hba.conf is local all all peer. I believe this is the one that makes your connection attempt fail. Peer authentication. The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections. Password authentication. The password-based authentication methods are md5 and password.

PostgreSQL Database: PG::ConnectionBad: FATAL: Peer , up a PostgreSQL 9.3 database, you run into the error 'PG::ConnectionBad: FATAL: Peer authentication failed for user' when trying to connect� The connection failed because by default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user postgres and then login as postgres or use sudo -u postgres psql database-name for accessing the database (and psql should not ask for a password).

Wayne's Blog, sudo vim /etc/postgresql/9.5/main/pg_hba.conf 加一行: ``` local all PG:: ConnectionBad: FATAL: Peer authentication failed for user XXX. # Database administrative login by Unix domain socket local all postgres md5 # 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 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a

Postgres error message: FATAL: Ident authentication failed for user , It means that Postgres is trying to authenticate a user using the Ident protocol, and can't. Ident auth works like this: You have database role 'foo' on database 'db' �

Comments
  • Does 'specifying a hostname in your database connection settings' mean 'add host: localhost to database.yml'? Because that didn't work for me.
  • @MaarrenSep yep. If it "doesn't work" for you, post a new question with details (versions, pg_hba.conf and database.yml contents, exact error messages, etc). Link back to this one for content and add a comment with a link to your new question here.
  • It solved my problem when I put "host:'localhost'" to my connection setting. Thank you!
  • sudo -u postgres psql dbname would be a way for the peer method where postgres is the relevant user.
  • The third point is so easily overlooked but so important. Solved my problem once I created a Postgres username that matched my Linux user name and then set it in my rails application. I did not have to add an entry for username or password then.
  • The question has nothing to do with psql, so specifying psql command-line options isn't a useful answer to the question. This is asking why rake db:create isn't working with a seemingly reasonably-configured Rails application.
  • The question has nothing to do with psql, so specifying psql command-line options isn't a useful answer to the question. This is asking why rake db:create isn't working with a seemingly reasonably-configured Rails application.