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 and storage method.
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
orUNENCRYPTED
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.
Related videos on Youtube
user3610957
Updated on June 04, 2022Comments
-
user3610957 almost 2 years
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".
-
user3610957 over 9 yearsYes I have added entry and ensured that it is considering this entry itself in pg_hba.conf file. My requirement is that I am passing encrypted password through jdbc so Postgres should recognize this password as an encrypted and allow user to login or is there any mechanism so that we can decrypt the password at postgres side before login?