Login to PostgreSQL using md5 encrypted password and not plaintext password

Question

I have created an user using md5 encrypted password as follows:

create user testuser with encrypted password 'md54ca03099a7cd3945e0260801ff5972a3';

The encrypted password is combination of "md5" + md5(password+username)

password=test
username=testuser

Added entry for testuser in pg_hba.conf file with md5 method

Now I am trying to login using above created user as follows:

psql -d dbexpress -U testuser

It prompts for password. I have provided above encrypted password so it is giving me error as:

psql: FATAL:  password authentication failed for user "testuser"

But I am able to login to postgresql using plaintest password "test".

Answer 1

The authentication method md5 does not directly govern the encryption of passwords in the system catalog (the keyword ENCRYPTED in CREATE ROLE):

Postgres 10 or later

Note this update in Postgres 10

• Add SCRAM-SHA-256 support for password negotiation and storage (Michael Paquier, Heikki Linnakangas)

  This provides better security than the existing md5 negotiation and storage method.

The manual:

To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.

Postgres 9.6 or older

Per documentation on the authentication method:

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

Per documentation on the ENCRYPTED keyword in CREATE ROLE:

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.

Both use md5 encryption, but the first is concerned with transport and the second with storage. You are still expected to provide the unencrypted password for your login, even when using the authentication method md5 (setting in pg_hba.conf). The user name is used as salt for md5 encryption on client and server.

First matching entry in pg_hba.conf

About your remark:

Added entry for testuser in pg_hba.conf file with md5 method.

Don't just "add" an entry. The first matching line in pg_hba.conf is applied!

The manual on pg_hba.conf:

The first record with a matching connection type, client address, requested database, and user name is used to perform authentication.

Bold emphasis mine in all quotes.