Login to PostgreSQL using md5 encrypted password and not plaintext password Login to PostgreSQL using md5 encrypted password and not plaintext password postgresql postgresql

Login to PostgreSQL using md5 encrypted password and not plaintext password


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 andstorage method.

The manual:

To ease transition from the md5 method to the newer SCRAM method, ifmd5 is specified as a method in pg_hba.conf but the user's password onthe server is encrypted for SCRAM (see below), then SCRAM-basedauthentication 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. Thesemethods operate similarly except for the way that the password is sentacross 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 behavioris determined by the configuration parameter password_encryption.) Ifthe presented password string is already in MD5-encrypted format, thenit is stored encrypted as-is, regardless of whether ENCRYPTED orUNENCRYPTED is specified (since the system cannot decrypt thespecified encrypted password string). This allows reloading ofencrypted 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.