PostgreSQL returns password authentication failed for user although password is correct

Written by Claudio Kuenzler - 1 comments

Published on February 14th 2020 - 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.

---