CODEWITHSUNDEEP
javapostgresqljdbc
gooin
gooin

Reputation: 113

how use SCRAM-SHA-256 to connect database by jdbc in PostgresSQL

there I'm trying to test SCRAM-SHA-256 in jdbc.

  public static void main(String[] args) throws SQLException {
        String url = "jdbc:postgresql://192.168.56.101:1521/gisdb";
        Properties props = new Properties();
        props.setProperty("user", "pguser");
        String scram = "SCRAM-SHA-256$4096:QNTBYMpbvZjbOx5RLM7rvA==$tuBD7Ek7niK8jyiuwjCGLH2EurqMNoIaclZhOXohyik=:2b0Ow951/1OPKsqzaGLWPyT+wVIXOs9dvY8TF2eTCVM=";
        props.setProperty("password", scram);
//        props.setProperty("password", "*******");

        Connection conn = DriverManager.getConnection(url, props);
        String databaseProductVersion = conn.getMetaData().getDatabaseProductVersion();
        System.out.println(databaseProductVersion);
    }

my pg_hba.conf:

hostssl all             all             192.168.0.0/16          scram-sha-256
#hostssl all             all             192.168.0.0/16          trust

select rolname,rolpassword from pg_authid; it shows:

          rolname          |                                                              rolpassword
---------------------------+---------------------------------------------------------------------------------------------------------------------------------------
 pg_monitor                |
 pg_read_all_settings      |
 pg_read_all_stats         |
 pg_stat_scan_tables       |
 pg_read_server_files      |
 pg_write_server_files     |
 pg_execute_server_program |
 pg_signal_backend         |
 pguser                    | SCRAM-SHA-256$4096:PZNbiF6I5G1SVcoN9sTjJw==$xe8jrBS9iUn0ldoIV8moaAod06sYRbxsyyQaUbiuSQE=:2tpLS+eL1brme0Il0wcnsllkDBfDkaQ/II7iVJ3ecxM=
 test                      |
 postgres                  | SCRAM-SHA-256$4096:o1lyjT/acTglIuLsp6TF3Q==$pdWLZ8DuceZDwr9jla0WPzXXa3N3kWrjh9cPnrloP3w=:6hd8Ib7Od+ZZenItVoH4L+26oSiBxqp63WxO82PeonM=

when I use scram-sha-256 encrypted password string or prue password in java code to connect, in log file, it shows FATAL: password authentication failed for user "pguser" . when I change method to trust in pg_hba.conf, it works.

How to use scram-sha-256 method to password connect?
My JDBC driver version is postgresql-42.2.12.jar

Upvotes: 7

Views: 11973

Answers (2)

utkarsh
utkarsh

Reputation: 447

From what I understand as a solution. This is a multi step process, All steps defined below:

  • Firstly, we need to have SCRAM as the encryption type on db. For this you need to edit the file - postgresql.conf and change the value of password_encryption as : password_encryption = scram-sha-256

Restart your postgres server after this change

  • Second, update the passwords in db and verify if they are in scram encryption. By default the db passwords are encrypted with MD5, after updating the db configuration, you need to do update these encrypted strings that are stored in the db. Run sql query : alter user with encrypted password ; Verify the change by running the query : select rolname,rolpassword from pg_authid; You should see the password column having string in the format SCRAM-*
  • Third, we need to enable scram config for incoming client connections. For that edit the file : pg_hba.conf . Refer to the screenshot below :

**Change the method value as in the image** Rest, your jdbc program should work (with just a minor edit : String scram = "plaintext-password"; ) keeping in mind that the right versions of jdbc drivers are used.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246653

You must supply the clear text password, not the SCRAM hash.

Upvotes: 8

Related Questions