psql, I try to connect to a database and I get "psql: FATAL: Peer authentication failed for user ", why?

Question

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

Answer 1

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.

Answer 2

If you encounter the error: psql: error: FATAL: Peer authentication failed for user "davide3", you will need to allow the new SQL user to be authenticated.

To add the Client Authentication for a new user in PostgreSQL and allow it to authenticate, the steps are the following:

• Edit the Client authentication file named pg_hba.conf this can generally be done with the command (in this case, I use PostgreSQL version 12): sudo nano /etc/postgresql/12/main/pg_hba.conf
• Add a line toward the bottom of the file, following the previous lines spacing with the following content: local all davide3 md5
• Save the file. Then restart PostgreSQL with: sudo service postgresql restart

You should now be able to connect to your new user with psql -U davide3. If you want help with the whole process of creating a PostgreSQL user and setting it up (as well as a new database) I have written a whole tutorial on it at: https://medium.com/p/44a38362bad9