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 | {}
Best Answer
This means it's using a unix socket connection, and connections for unix sockets are set to use
peer
authentication inpg_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 inpg_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.