6

I'm using Postgres 9.4 on Debian Linux. I created a database with a user, cindex with access to the database. Yet when I try and login at the command line, I'm not even prompted for a password:

myuser@myuserserver:~ $ psql -Ucindex cindex
psql: FATAL:  Peer authentication failed for user "cindex"

What else do I need to do to enable the user? Below you can see the privileges that I have already set up:

postgres@myuserserver:~$ psql
psql (9.4.13)
Type "help" for help.

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA cindex TO cindex;
GRANT
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cindex    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =T/postgres          +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | cindex=c/postgres
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 cindex    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
3
  • 1
    You need to check the contents of your pg_hba.conf. Think of it as the PostgreSQL server firewall configuration. Sep 10, 2017 at 17:13
  • And what am I looking to change in there?
    – Dave
    Sep 10, 2017 at 17:19
  • The pg_hba file specifies peer or ident authentication. Those don't use passwords, so there is no point in asking you for a password. You are looking to change peer or ident to be md5, which does use passwords. (or perhaps to ldap, or `scram-sha-256 (v10 only), or maybe one of the more obscure methods which uses passwords)
    – jjanes
    Sep 10, 2017 at 22:27

2 Answers 2

7

Peer authentication

This means it's using a unix socket connection, and connections for unix sockets are set to use peer authentication in pg_hba.conf. It just checks that the unix user name is the same as the postgres user name requested, and doesn't care about passwords.

If you want password auth, use md5 auth in pg_hba.conf instead.

See the manual.

The way PostgreSQL splits authentication between SQL configuration and a config file is definitely confusing, so you're not alone. Being able to set a password for a user, but having that password be ignored in some contexts and used in others, takes some getting used to. Once you understand the system it makes sense but it's definitely not discoverable and intuitive.

0

I had similar problems, the answer was incorrect rules order in pg_hba.conf. Before:

local    all     all     peer
local    myuser  mydb    md5

since for local connections "all-all" rules was the first, and myuser with mydb was a part of that all, psql assumed that connection type is peer and denied me access.

After

local    myuser  mydb    md5
local    all     all     peer

This way, for a specific user, password auth is used, just as I want it to be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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