3

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.