2

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 | {}
  • 1
    You need to check the contents of your pg_hba.conf. Think of it as the PostgreSQL server firewall configuration. – Colin 't Hart Sep 10 '17 at 17:13
  • And what am I looking to change in there? – Dave Sep 10 '17 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 '17 at 22:27
4

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.

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.