psql: error: could not connect to server: FATAL: Peer authentication failed for user “postgres”

Question

i installed and setup postgresql-12 with the commands from here [https://www.postgresql.org/download/linux/redhat/]

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql12
yum install postgresql12-server
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

i then tried to log into the database

psql postgres postgres

i got the error

psql: error: could not connect to server: FATAL:  Peer authentication failed for user "postgres"

there are two pg_hba.conf files on my system. i checked the authentication settings, shown below, as recommended here [psql: FATAL: Ident authentication failed for user "postgres"

/data/pg_data_dir/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# 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     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

/var/lib/pgsql/12/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

the authentication settings look OK, so i'm not sure what to do from here.

you need to reload the conf before you can use the updated pg_hba.conf file -- you can just restart postgres or su - postgres before trying psql. Once you get into psql, issue select pg_reload_conf(); — richyen
Your error message states that peer authentication failed. The pg_hba.conf files you show do not include rules using the peer authentication method. Are you sure there is no other pg_hba.conf file? Check the hba_file entry in your postgresql.conf file. — clamp

adrianmih adrianmih · Accepted Answer · 2019-10-29T21:09:34

EDIT - To make this more structured and usable.

First, you need to understand what each of those authentification methods mean, there is a ton on infromation in here about this. Long story short, at this stage, the one you'll likely be interested in is md5 which is a hashed password, or trust which is no password.

After installing the postgres on your machine, you'll need to become the postgres user and then attempt psql

~# su -l postgres --assume you're postgres
~$ psql --connect

psql (xx.x ))
Type "help" for help.

postgres=#

Once you're in, you can set a new password for that user:

postgres=# \password postgres

To prevent this from happening, you'll need to ask postgres where the hba file is:

postgres=# SHOW hba_file;

Use your favourite text editor and modify it to fulfill your needs.

Also you need to make sure you reload the postgresql service, the command is:

sudo systemctl reload postgresql-12.service

psql: error: could not connect to server: FATAL: Peer authentication failed for user “postgres”

1 Answers