PostgreSQL Password Encryption

In PostgreSQL there are two main password encryption algorithms : md5 , SCRAM-SHA-256. its worth mentioning that SCRAM is more secure of course than md5 however, you need to check application driver compatibility.

In postgresql.conf  file, the password_encryption parameter is set to md5 by default.

I will simulate creating a new account called “emad”:

create user emad with password ’emad_123′;

Now, to check the password encryption used you can use either pg_shadow or pg_authid:

select * from pg_shadow where usename=’emad’;

The password hash is highlighted in yellow as shown below starting with “md5”.

How can we change the account password in the future from md5 to SCRAM-SHA-256 ?

First I will update the parameter password_ecnryption in postgresql.conf to reference scrma-sha-256 algorithm

I will perform a reload using the command:

pg_ctl reload

Then, I will change the account password using the command:

alter role emad with password ’emad_999′;

select * from pg_shadow where usename=’emad’;

As shown below, the account now has SCRAM-SHA-256 encryption algorithm

Other accounts will still use the old encryption md5 unless they change their passwords to reflect the new algorithm.

pg_hba.conf file should eventually be updated to reflect the new encryption algorithm.