One of the new features in PostgreSQL 10 was the introduction of stronger password authentication based on SCRAM-SHA-256. How can you migrate your existing users that currently use md5 authentication to the new method without any interruption? Actually that is quite easy, as you will see in a few moments, but there is one important point to consider: Not every client/driver does already support SCRAM-SHA-256 authentication so you need to check that before. Here is the list of the drivers and their support for SCRAM-SHA-256.

The default method that PostgreSQL uses to encrypt password is defined by the “password_encryption” parameter:

1
2
3
4
5
postgres=# show password_encryption;
 password_encryption
---------------------
 md5
(1 row)

Let’s assume we have a user that was created like this in the past:

1
2
postgres=# create user u1 login password 'u1';
CREATE ROLE

With the default method of md5 the hashed password looks like this:

1
2
3
4
5
postgres=# select passwd from pg_shadow where usename = 'u1';
               passwd               
-------------------------------------
 md58026a39c502750413402a90d9d8bae3c
(1 row)

As you can see the hash starts with md5 so we now that this hash was generated by the md5 algorithm. When we want this user to use scram-sha-256 instead, what do we need to do? The first step is to change the “password_encryption” parameter:

1
2
3
4
5
6
7
8
9
10
11
postgres=# alter system set password_encryption = 'scram-sha-256';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
postgres=# select current_setting('password_encryption');
 current_setting
-----------------
 scram-sha-256
(1 row)

From now on the server will use scram-sha-256 and not anymore md5. But what happens when our user wants to connect to the instance once we changed that? Currently this is defined in pg_hba.conf:

1
2
postgres=> ! grep u1 $PGDATA/pg_hba.conf
host    postgres        u1              192.168.22.1/24         md5

Even though the default is not md5 anymore the user can still connect to the instance because the password hash did not change for that user:

1
2
3
4
5
6
7
8
9
postgres=> ! grep u1 $PGDATA/pg_hba.conf
host    postgres        u1              192.168.22.1/24         md5
 
postgres@rhel8pg:/home/postgres/ [PGDEV] psql -h 192.168.22.100 -p 5433 -U u1 postgres
Password for user u1:
psql (13devel)
Type "help" for help.
 
postgres=>

Once the user changed the password:

1
2
3
4
5
6
7
8
9
postgres@rhel8pg:/home/postgres/ [PGDEV] psql -h 192.168.22.100 -p 5433 -U u1 postgres
Password for user u1:
psql (13devel)
Type "help" for help.
 
postgres=> password
Enter new password:
Enter it again:
postgres=>

… the hash of the new password is not md5 but SCRAM-SHA-256:

1
2
3
4
5
postgres=# select passwd from pg_shadow where usename = 'u1';
                                                                passwd                               >
----------------------------------------------------------------------------------------------------->
 SCRAM-SHA-256$4096:CypPmOW5/uIu4NvGJa+FNA==$PNGhlmRinbEKaFoPzi7T0hWk0emk18Ip9tv6mYIguAQ=:J9vr5CQDuKE>
(1 row)

One could expect that from now on the user is not able to connect anymore as we did not change pg_hba.conf until now:

1
2
3
4
5
6
postgres@rhel8pg:/home/postgres/ [PGDEV] psql -h 192.168.22.100 -p 5433 -U u1 postgres
Password for user u1:
psql (13devel)
Type "help" for help.
 
postgres=>

But in reality that still works as the server now uses the SCRAM-SHA-256 algorithm. So once all the users changed their passwords you can safely switch the rule in pg_hba.conf and you’re done:

1
2
3
4
5
6
7
8
postgres=> ! grep u1 $PGDATA/pg_hba.conf
host    postgres        u1              192.168.22.1/24         scram-sha-256
 
postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

You just need to make sure that all the users do not have a hash starting with md5 but the new one starting with SCRAM-SHA-256.

Post Views: 2,455
Thumbnail [60x60]
by
Daniel Westermann