No support of SCRAM authentification in PostgreSQL

[signedav]

See this issue https://github.com/claeis/ili2db/issues/448

At least in the Model Baker Documentation should be an entry of this and a solution.

The documentation says (of course scram-sha-256 could be replaced with md5 to do the other way around):

[..] set password_encryption = 'scram-sha-256' in postgresql.conf, make all users set new passwords, and change the authentication method specifications in pg_hba.conf to scram-sha-256.

What only a change in one of those files does?

I think - but not sure - postgresql.conf defines how the password should be encrypted (hashed) when creating / alter a user and pg_hba.conf defines what kind of encryption (hash) is expected by the password sent by the client.

But as mentioned in the first quote "[...] postgresql.conf, make all users set new passwords [...]" - so this step with ALTER USER is requested automatically. Not sure if this could be a little risky that currently running clients do not work anymore, when the encryption on server side changed.

Btw. I see in the documentation as well:

If a password was encrypted using the scram-sha-256 setting, then it can be used for the authentication methods scram-sha-256 and password (but password transmission will be in plain text in the latter case). The authentication method specification md5 will automatically switch to using the scram-sha-256 method in this case, as explained above, so it will also work.

So it's possible that one changed it on pg_hba.conf but still the "previous" encryption is used...

And here some additional info how to check the password encryption (and change it):

To see how a password is encrypted we can check pg_authid:

> SELECT rolpassword from pg_authid where rolname = '{your-db-user-name}';

When we want to change it (e.g. to md5) we need to set the password_encryption and then alter the user:

> SHOW password_encryption;
scram-sha-256
-- not sure if this is set by the setting in `postgresql.conf`
> SET password_encryption  = 'md5';
> ALTER USER "{your-db-user-name}" with password '{your-db-password}';

And where are the files?

> SHOW hba_file;
              hba_file               
-------------------------------------
 /etc/postgresql/14/main/pg_hba.conf
(1 row)

> SHOW config_file;
               config_file               
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

[signedav]

Fix Steps

1. Change your password encryption

In SQL Console (e.g. PgAdmin):

> SHOW password_encryption;
scram-sha-256
-- not sure if this is set by the setting in `postgresql.conf`
> SET password_encryption  = 'md5';
> ALTER USER "{your-db-user-name}" with password '{your-db-password}';

2. Change enryption in postgresql.conf

In SQL Console (e.g. PgAdmin):

> SHOW config_file;
               config_file               
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf

Und im postgresql.conf ändern zu 'md5':

password_encryption = 'scram-sha-256'

3. Change enryption in pg_hba.conf

In SQL Console (e.g. PgAdmin):

> SHOW hba_file;
              hba_file               
-------------------------------------
 /etc/postgresql/14/main/pg_hba.conf

Und im pg_hba.conf ändere 'scram-sha-256' zu 'md5':

[lacardonap]

@signedav thank you very much for the contribution, I followed the steps and everything worked fine.

The error: No support of SCRAM authentification in PostgreSQL

Valid postgres version we are using:

select version()

> PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit

Change enryption in postgresql.conf

SHOW config_file;
/etc/postgresql/15/main/postgresql.conf

Change enryption in pg_hba.conf

SHOW hba_file;
/etc/postgresql/15/main/pg_hba.conf

Restart postgres service to apply changes

sudo systemctl restart postgresql.service

Update password to md5

SHOW password_encryption;
ALTER USER "postgres" with password 'secret';
SELECT rolpassword from pg_authid where rolname = 'postgres';

[andreasneumann]

@signedav - we ran into this issue in our PostgreSQL/PostGIS course today.

Do you see any chance that the pgjpdbc will support scram-sha-256 in the future?

I believe that future DB admin will enforce the better encryption in the future - and then there will be no chance to work with ili2pg/ModelBaker in the future with newer PostgreSQL versions ...

DBeaver is also a Java based application and works fine with scram-sha-256 - are they not using pgjpdbc ?

Anyway: thank you for the good summary and step-by-step explanation for the workaround!

[signedav]

I think (not sure) pgjpdbc is already supporting it but ili2db is not yet using it (see https://github.com/claeis/ili2db/issues/448) since java "42.2.18.jre6" is used (targeting JRE 6). I don't know about the effort / risk to bump ili2db to a newer JRE (see it's marked as 5.0.0 milestone).

[andreasneumann]

@signedav - thank you for the clarification!

[signedav]

This is fixed with ili2db 5.0.0 :tada: