How do I fix scram authentication errors when connecting DBeaver to a docker postgresql container?

Question

Background:

I am using a docker-compose file to run a Postgresql container. Upon upgrade, I was unable to log in using DBeaver (v23.1.2), getting the following error in DBeaver:

FATAL: password authentication failed for user "[username]"

Docker log errors indicated that I was unable to log in due to scram authentication:

postgres_1  | 2023-07-27 23:49:11.288 UTC [48] FATAL:  password authentication failed for user "[USERNAME]"
postgres_1  | 2023-07-27 23:49:11.288 UTC [48] DETAIL:  User "[USERNAME]" does not have a valid SCRAM secret.
postgres_1  |   Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

Old docker-compose file of old PostgreSQL version:

version: "3.7"

services:

  postgres:
    image: postgres:12-alpine
    environment:
      - POSTGRES_USER=[USERNAME]
      - POSTGRES_PASSWORD=[PASSWORD]
      - POSTGRES_DB=[DATABASENAME]
    volumes:
      - ./volumes/postgres-12:/var/lib/postgresql/data
    restart: always
    ports:
      - "127.0.0.1:5432:5432"

New docker-compose file for upgraded PostgreSQL (causes scram error)

I changed the docker-compose file to this when upgrading:

version: "3.7"

services:

  postgres:
    image: postgres:15-alpine
    environment:
      - POSTGRES_USER=[USERNAME]
      - POSTGRES_PASSWORD=[PASSWORD]
      - POSTGRES_DB=[DATABASENAME]    
    volumes:
      - ./volumes/postgres-15:/var/lib/postgresql/data
    restart: always
    ports:
      - "127.0.0.1:5432:5432"

Before doing so I did a database dump from the old, and when the new version was started I imported it into the new database. When I connected using DBeaver I was unable to log in receiving the above mentioned scram authentication errors.

Answer 1

The problem is that the password in the posgres database is using md5, not scram. The upgraded version of postegresql has the following setting in pg_hba.conf:

host all all all scram-sha-256

This setting requires that only scram encryption be used by clients. However, the password in the database is hashed by md5 so no match occurs.

Overview:

• change pg_hba.conf back to allow connections without scram,
• connect with DBeaver client
• re-encrypt the password this time using scram (by simply replacing with your same password),
• restore the original scram line in the pg_hba.conf
• restart the database

Specific Steps to Solution:

1. Set PGDATA environmental variable.

Edit your docker file to add the PGDATA environmental variable to make sure that the configuration directory is being found by setting your environmental variable:

    environment:
      - POSTGRES_USER=[USERNAME]
      - POSTGRES_PASSWORD=[PASSWORD]
      - POSTGRES_DB=[DATABASENAME]   
      - PGDATA=/var/lib/postgresql/data [this should match the path inside the container, not the local volume path - see the volumes statement]

1. Edit pg_hba.conf to allow unencrypted connections

In my docker-compose file above, my configuration pg_hba.conf resides on my docker host at volumes/postgres-15/pg_hba.conf. Edit as follows commenting out the scram line, and changing it to trust.

#host all all all scram-sha-256
host all all all trust

2. Edit postgresql.conf to specify scram.

Edit volumes/postgres-15/postgresql.conf to set authentication method as scram. This should already be the default on a new installation:

 password_encryption = scram-sha-256 

This will tell postgres to encrypt any new passwords in scram which will occur when you replace your password after connecting through DBeaver.

3. Restart your postgresql server.

I do this by docker-compose down; docker-compose up -d; docker-compose logs -f.

3. Connect using Dbeaver.

4. Check for Scram Unencrypted Users.

In DBeaver, open a new sql dialog by typing ctrl-] (or use the menu SQL Editor->New Script) and run the following sql select to see the passwords in the database that are not encrypted in scram:

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

The result of the sql command (not shown in screen shot) should be "False" for any password not encrypted in scram:

5. Replace passwords to encrypt in scram.

For each use that has a false result, reset the password so it gets encrypted into scram by selecting the connection, right clicking, selecting Security, then Change user password. Since we are not changing passwords just re-encrypting into scram, enter the old password twice - once in "New Password" and then in "Verify". Click OK.

6. Verify Passwords are encrypted.

Rerun the sql script above and your password should now show "True".

7. Restore pg_hba.conf to require scram.

Now go back to your server running your docker container and reverse what was done in step one - specifying that all users must use scram. I do this by editing volumes/postgres-15/pg_hba.conf, deleting or commenting out the trust line, and restoring the scram line:

host all all all scram-sha-256
#host all all all trust [or delete]

8. Restart your docker container

I do this by docker-compose down; docker-compose up -d; docker-compose logs -f.

9. Re-connect DBeaver.

You should now be able to login using DBeaver scram authentication without errors.