No support of SCRAM authentification in PostgreSQL

signedav commented 2 years ago

ili2db cannot handle the SCRAM authentication of PostgreSQL since java "42.2.18.jre6" is used (targeting JRE 6).

This leads to an error when the database uses the SCRAM authentication:

SCRAM authentication is not supported by this driver. You need JDK >= 8 and pgjdbc >= 42.2.0 (not ".jre" versions)

Steps to reproduce:

Database

Use a PostgreSQL Database using SCRAM authentication (the docker images of kartoza or postgis use since version 14 the SCRAM authentification). docker compose file content:

  db:
    image: 'kartoza/postgis:14-3.1'
    restart: always
    volumes:
      - ./postgresql:/var/lib/postgresql
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASS=postgres
      - ALLOW_IP_RANGE=0.0.0.0/0
    ports:
      - 54322:5432

run ili2db

java -jar /home/dave/.local/share/QGIS/QGIS3/profiles/DuselDuck/python/plugins/QgisModelBaker/libili2db/bin/ili2pg-4.6.1/ili2pg-4.6.1.jar --schemaimport --dbhost localhost --dbport 54322 --dbusr postgres --dbpwd ****** --dbdatabase gis --dbschema wrz_test --setupPgExt --coalesceCatalogueRef --createEnumTabs --createNumChecks --createUnique --createFk --createFkIdx --coalesceMultiSurface --coalesceMultiLine --coalesceMultiPoint --coalesceArray --beautifyEnumDispName --createGeomIdx --createMetaInfo --expandMultilingual --createTypeConstraint --createEnumTabsWithId --createTidCol --importTid --smart2Inheritance --strokeArcs --createBasketCol --defaultSrsCode 2056 --models Wildruhezonen_LV03_V2_1;Wildruhezonen_LV95_V2_1 /home/dave/qgis_project/wildruhezonen/Wildruhezonen_V2_1.ili
Info: dburl <jdbc:postgresql://localhost:54322/gis>
Info: dbusr <postgres>
SCRAM authentication is not supported by this driver. You need JDK >= 8 and pgjdbc >= 42.2.0 (not ".jre" versions)
Info: ili2pg-4.6.1-63db90def1260a503f0f2d4cb846686cd4851184
Info: ili2c-5.2.3-3ec7a10e9bacc87e149a14a7f11a6035e0481c69
Info: iox-ili-1.21.8-a6aee68136447c0616995e56888fa9c7ef362a77
Info: java.version 1.8.0_312
Info: user.name <dave>
Info: maxMemory 3564544 KB
Info: currentTime 2022-01-10 13:50:17
Info: dbschema <wrz_test>
Info: dburl <jdbc:postgresql://localhost:5432authentication method "md5"2/gis>
Info: dbusr <postgres>
org.postgresql.util.PSQLException: SCRAM authentication is not supported by this driver. You need JDK >= 8 and pgjdbc >= 42.2.0 (not ".jre" versions)
  SCRAM authentication is not supported by this driver. You need JDK >= 8 and pgjdbc >= 42.2.0 (not ".jre" versions)

it works with PASSWORD_AUTHENTICATION=md5

  db:
    image: 'kartoza/postgis:14-3.1'
    restart: always
    volumes:
      - ./postgresql:/var/lib/postgresql
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASS=postgres
      - ALLOW_IP_RANGE=0.0.0.0/0
      - PASSWORD_AUTHENTICATION=md5
    ports:
      - 54322:5432

java -jar /home/dave/.local/share/QGIS/QGIS3/profiles/DuselDuck/python/plugins/QgisModelBaker/libili2db/bin/ili2pg-4.6.1/ili2pg-4.6.1.jar --schemaimport --dbhost localhost --dbport 54322 --dbusr postgres --dbpwd ****** --dbdatabase gis --dbschema wrz_test --setupPgExt --coalesceCatalogueRef --createEnumTabs --createNumChecks --createUnique --createFk --createFkIdx --coalesceMultiSurface --coalesceMultiLine --coalesceMultiPoint --coalesceArray --beautifyEnumDispName --createGeomIdx --createMetaInfo --expandMultilingual --createTypeConstraint --createEnumTabsWithId --createTidCol --importTid --smart2Inheritance --strokeArcs --createBasketCol --defaultSrsCode 2056 --models Wildruhezonen_LV03_V2_1;Wildruhezonen_LV95_V2_1 /home/dave/qgis_project/wildruhezonen/Wildruhezonen_V2_1.ili
Info: dburl <jdbc:postgresql://localhost:54322/gis>
Info: dbusr <postgres>
Info: ili2pg-4.6.1-63db90def1260a503f0f2d4cb846686cd4851184
Info: ili2c-5.2.3-3ec7a10e9bacc87e149a14a7f11a6035e0481c69
Info: iox-ili-1.21.8-a6aee68136447c0616995e56888fa9c7ef362a77
Info: java.version 1.8.0_312
Info: user.name <dave>
Info: maxMemory 3564544 KB
Info: currentTime 2022-01-10 14:03:30
Info: dbschema <wrz_test>
Info: dburl <jdbc:postgresql://localhost:54322/gis>
Info: dbusr <postgres>
Info: databaseProduct <PostgreSQL>
Info: databaseVersion <14.0 (Debian 14.0-1.pgdg110+1)>
Info: driverName <PostgreSQL JDBC Driver>
Info: driverVersion <42.2.18.jre6>
Info: postGISVersion <POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="140" GEOS="3.9.0-CAPI-1.16.2" PROJ="7.2.1" GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER>
Info: compile models...
Info: lookup model <LocalisationCH_V1> 2.3 in repository <jdbc:postgresql://localhost:54322/gis/wrz_test/>
Info: lookup model <CatalogueObjects_V1> 2.3 in repository <jdbc:postgresql://localhost:54322/gis/wrz_test/>
Info: lookup model <CHAdminCodes_V1> 2.3 in repository <jdbc:postgresql://localhost:54322/gis/wrz_test/>
Info: lookup model <GeometryCHLV03_V1> 2.3 in repository <jdbc:postgresql://localhost:54322/gis/wrz_test/>
Info: lookup model <CoordSys> 2.3 in repository <jdbc:postgresql://localhost:54322/gis/wrz_test/>
Info: lookup model <Units> 2.3 in repository <jdbc:postgresql://localhost:54322/gis/wrz_test/>
Info: ilifile </home/dave/.ilicache/jdbc&003apostgresql&003a/localhost&003a54322/gis/wrz_test/Units-20120220.ili>
Info: ilifile </home/dave/.ilicache/jdbc&003apostgresql&003a/localhost&003a54322/gis/wrz_test/CHBase_Part2_LOCALISATION_V1.ili>
Info: ilifile </home/dave/.ilicache/jdbc&003apostgresql&003a/localhost&003a54322/gis/wrz_test/CHBase_Part3_CATALOGUEOBJECTS_V1.ili>
Info: ilifile </home/dave/.ilicache/jdbc&003apostgresql&003a/localhost&003a54322/gis/wrz_test/CoordSys-20151124.ili>
Info: ilifile </home/dave/.ilicache/jdbc&003apostgresql&003a/localhost&003a54322/gis/wrz_test/CHBase_Part4_ADMINISTRATIVEUNITS_V1.ili>
Info: ilifile </home/dave/.ilicache/jdbc&003apostgresql&003a/localhost&003a54322/gis/wrz_test/CHBase_Part1_GEOMETRY_V1.ili>
Info: ilifile </home/dave/qgis_project/wildruhezonen/Wildruhezonen_V2_1.ili>
Info: create table structure, if not existing...
Info: ...done

Update 6.6.2023: Eine Erkenntnis: Die ENV ist in anderen images (e.g. von postgis oder sogis) anderst, dort heisst sie POSTGRES_HOST_AUTH_METHOD, setzt aber nur den Teil in pg_hba.conf (nicht in postgres.conf) und löst so das Problem nicht.

sjib commented 2 years ago

@signedav Is PASSWORD_AUTHENTICATION=md5 in ModelBaker implemented? I cannot find a dialog/field/flag for that currently in the data import dialog (v7.0.3). Any other way to set md5?

signedav commented 2 years ago

No. md5 is the authentification mode you need to set on the database server. Not in the client. It's not the competence of QGIS Model Baker to change database settings.

Maybe this has not been clear. In my example I use a docker container and when I start it with the mentioned setting the Postgres Server uses md5 instead of scram.

edigonzales commented 2 years ago

@sjib It's an option when starting the database docker image. I think it will end in the pg_hba.conf file of the database. So at the moment it has to be solved on the database side.

edigonzales commented 2 years ago

How do you run postgres? On Windows? With Docker?

signedav commented 2 years ago

As @edigonzales mentioned it's in the pg_hba.conf. See the link above https://www.postgresql.org/docs/current/auth-password.html There it describes how to change to SCRAM - I suppose it's just the other way around.

To upgrade an existing installation from md5 to scram-sha-256, after having ensured that all client libraries in use are new enough to support SCRAM, 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.

sjib commented 2 years ago

So there seem to be 3 options for now:

Use JDK instead of JRE
Change the database password setttings
wait till JRE also supports SCRAM (as this feature seems to have been introduced with Postgres 11 -> see https://issues.openbravo.com/view.php?id=44832)

Does anyone found the documentation of the JRE releases?

edigonzales commented 2 years ago

I think only No 2 is an option.

On the Java side it's the jdbc driver version that ili2pg uses (https://github.com/claeis/ili2db/blob/master/build.gradle#L334), that does not support SCRAM.

sjib commented 2 years ago

On the Java side it's the jdbc driver version that ili2pg uses (https://github.com/claeis/ili2db/blob/master/build.gradle#L334), that does not support SCRAM.

This is JRE not JDK: ili2pgImplementation group: 'org.postgresql', name: 'postgresql', version: '42.2.18.jre6'

On windows: If I install jdk instead of jre for using java I do not have problems. The challenge is more that non-developers / ordinary users should not have to install the full jdk

edigonzales commented 2 years ago

Strange since I'm using JDK only and it does not work. I thought the jdbc version must be >= 42.2.0 and NOT the jre6 compatible version: https://www.postgresql.org/about/news/jdbc-4220-released-1825/

To run a java program you only need a runtime environment (JRE) and no development kit (JDK). At least if the application does not need a compiler on runtime. As far as I understand... But perhaps the JDK has some "scram-needed" libs that will end up in the classpath when running ili2pg.

I still think the way to go is to update the jdbc driver but this will only happen if ili2pg will bump the minimal java version too: https://github.com/claeis/ili2db/blob/master/build.gradle#L9

sjib commented 2 years ago

Just for reference: The combination Postgres14 and Java 8.3 does not work even if I set password_encryption = md5 # md5 or scram-sha-256 (or is there any other variable I have to turn on? I restarted the postgres service and even the computer)

The combination of Postgres11 and Java 11.0.12 works without any settings of password_encryption (variable commented out #)

Thanks for all feedbacks.

edigonzales commented 2 years ago

In which file did you set password_encryption=md5? In postgresql.conf? The workaround from @signedav will change pg_hba.conf.

sjib commented 2 years ago

@edigonzales in postgres.conf

sjib commented 2 years ago

What is the difference between pg_hba.conf and postgressql.conf

I am running qgis on my laptop (local host)

edigonzales commented 2 years ago

pg_hba.conf is all about authentification: hba=host-based authentication. postgresql.conf contains all the settings.

signedav commented 2 years ago

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. Do you agree on that @sjib @edigonzales @lacardonap ?

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.confbut 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}';

voisardf commented 1 year ago

@claeis Hi Claude, as there is no due date yet for ili2db 5.x what is the state of this issue? Is there a resolution to be expected soon or an immediate workaround replacing the library manually? In Neuchâtel we use the latest QGIS ModelBaker 7.4.2 & ili2pg 4.11.0 on Windows with PostgreSQL 14 and face this issue.

Anything that can be done to accelarate fixing the issue? Thanks!

edigonzales commented 1 year ago

@voisardf Are the mentionend workarounds not working for you?

(though I'm not against a proper solution)

voisardf commented 1 year ago

@edigonzales unless I missed some of the possible workarounds, unfortunately no.

md5-encryption is not an option anymore in our environment
I'd have to check if the use of JDK helps, but from your post on January 28 I understood that does not work either? I guess an option would be to install PostgreSQL locally and use this instance to export the data - but that is not a something I'd like to generalize as it adds an extra layer of time, work and software installation

voisardf commented 1 year ago

I just checked an ModelBaker export using jdk-17.0.4.1 and scram settings - it does not work, I get the know library error

edigonzales commented 1 year ago

@voisardf Can you please try https://github.com/edigonzales/ili2pg-scram/releases/tag/v4.11.0 It's a fat jar including all the libs that are placed in the original "libs" dir.

voisardf commented 1 year ago

@edigonzales Thanks, I can confirm that your fat .jar works in my environment; I just replaced the path in the ModelBaker export command (ili2pg-4.10.0.jar) to the new ili2pg-4.11.0.jar file above and the process functions perfectly well. 💯 🙂

So it's clearly all about configuring the compatible library versions 👍

claeis / ili2db