Postgresql – psql, I try to connect to a database and I get “psql: FATAL: Peer authentication failed for user ”, why

authenticationpasswordpostgresqlpostgresql-9.2psql

I'm using psql (9.2.23) on my Dell Latitude 3540 laptop running Linux CentOS 7.
I want to do a simple thing: create a database username and log in as that username.

My operating system username is "davide", and the username I'd like to create and use in psql is "davide3". My laptop name is "laptop".

Here are the steps I followed, after having installed PostgreSQL:

[davide@laptop ~]$ sudo su - postgres

[sudo] password for davide:

Last login: XXX

-bash-4.2$ psql

psql (9.2.23)

Type "help" for help.

postgres=# CREATE USER davide3 WITH PASSWORD 'davide3' CREATEDB
CREATEUSER;

CREATE ROLE

postgres=# \q

-bash-4.2$ exit

logout

Then I try to login to the database by using the username "davide3":

[davide@laptop ~]$ psql -d postgres -U davide3

psql: FATAL: Peer authentication failed for user "davide3"

Here's the trouble: I was expecting to get a password-insertion message, and to insert the 'davide3' password, but I did not get anything instead.

What did I do wrong?
How to solve this issue?

Thanks

Best Answer

You're not asked for a password because the pg_hba.conf configuration file in CentOS says that a connection through a Unix domain socket implies the peer authentication. peer succeeds only if your OS user is the same as your database user. It's a reasonable default.

You may edit pg_hba.conf to indicate that anyone can connect locally to davide3 to certain databases or all databases, or more generally whatever policy is best suitable in your case.

The file is documented at: https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Alternatively, keep the default policy and try to add -h localhost to psql invocation. This should trigger a different authentication rule (md5) in the default pg_hba.conf that will ask for the password.

Related Question