PostgreSQL returns password authentication failed for user although password is correct

Written by - 1 comments

Published on - Listed in PostgreSQL Database


On a new PostgreSQL server, the following error message was received, when a user tried to use psql:

$ psql -h 127.0.0.1 -U dbuser
Password for user dbuser: *****
psql: FATAL:  password authentication failed for user "dbuser"
FATAL:  password authentication failed for user "dbuser"

The logs showed the following entries:

[11834] dbuser@dbuser FATAL:  password authentication failed for user "dbuser"
[11834] dbuser@dbuser DETAIL:  Password does not match for user "dbuser".
    Connection matched pg_hba.conf line 92: "host    all             all             127.0.0.1/32            md5"
[11835] dbuser@dbuser FATAL:  password authentication failed for user "dbuser"
[11835] dbuser@dbuser DETAIL:  Password does not match for user "dbuser".
    Connection matched pg_hba.conf line 92: "host    all             all             127.0.0.1/32            md5"

Verifying authentication configuration

Obviously the first question in such a situation is: Was the entered password correct? And in the special case of PostgreSQL a second question should always come into mind: Is there a correct configuration in pg_hba.conf for the attempted authentication?

The authentication obviously correctly matched a line in pg_hba.conf, as could also be seen in the log file:

# grep 127.0.0.1 /etc/postgresql/9.6/main/pg_hba.conf
host    all             all             127.0.0.1/32            md5

After it was verified that the entered password was actually correct, the creation of the SQL user was once more analyzed. This turned out to be a manual user creation using an SQL query (old habits):

postgres=# CREATE USER dbuser WITH PASSWORD 'mysecretpassword';

Spotting the difference in user creation

When using the createuser command, a special parameter -e can be used. This will show the actual SQL queries sent to PostgreSQL:

postgres@pgserver:~$ createuser -P -E -e dbuser
Enter password for new role: *****
Enter it again: *****
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE dbuser ENCRYPTED PASSWORD 'md5b3e88aa92b0943f1d2eed5cc618255e8' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

A significant difference is the usage of "ENCRYPTED PASSWORD" vs. "WITH PASSWORD" from the manual SQL query. Would that mean that the md5 password encryption (as defined in pg_hba.conf) didn't work because the password from the manual user creation (using SQL query) was understood as plain text?

This can be verified again, now that the user was created using createuser:

$ psql -h 127.0.0.1 -p 5432 -U dbuser
Password for user dbuser: *****
psql (9.6.16)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

dbuser=> 

Indeed, the password authentication worked this time and the user is logged in!

Encrypted vs unencrypted passwords

The documentation of the createuser command shows that there are two different options available whether or not to use an encrypted password:

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SYSID uid
    | CREATEDB | NOCREATEDB
    | CREATEUSER | NOCREATEUSER
    | IN GROUP groupname [, ...]
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'abstime' 

Particularly these keywords are described as:

ENCRYPTED | UNENCRYPTED

    These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

It also says that if neither of these keywords was used, the system default would be applied. To check the current PostgreSQL setting whether or not to encrypt passwords by default, the following SQL query can be run:

postgres@pgserver:~$ psql -qAtc "SELECT name, setting FROM pg_settings WHERE name = 'password_encryption'"
password_encryption|on

It can also be checked in the configuration file, in case the default should be changed:

root@pgserver:~# grep password_encryption /etc/postgresql/9.6/main/postgresql.conf
#password_encryption = on

TL;DR: Now it makes sense

The manual user creation using an SQL query did not contain either ENCRYPTED nor UNENCRYPTED keywords. This means that PostgreSQL automatically applied the default: ENCRYPTED. The given password ("mysecretpassword") was therefore encrypted and stored in the database. Logins with exactly this plain password ("mysecretpassword") would of course fail, because it does not match the encrypted stored value.

Although the manual user creation using an SQL query still works, the newer createuser command should be used primarily. This will avoid errors or mistakes from remembered SQL queries from earlier PostgreSQL versions. As mentioned, old habits.



Add a comment

Show form to leave a comment

Comments (newest first)

Mert from wrote on Jan 2nd, 2021:

Thank's for clarification i was trying wrap my mind around this for whole evening. More people should be aware of subtle difference between authentication mechanisms, especially newcomers.


RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder