DEV Community

Milind Satpute
Milind Satpute

Posted on

🔒 Stop Downgrading Postgres: The Secure SCRAM Fix for PgBouncer Authentication

#docker #pgbouncer #edoburu

A secure, production-ready fix for the FATAL: server login failed: wrong password type error when connecting PgBouncer to a modern PostgreSQL database (v14+) using SCRAM-SHA-256 authentication.

If you are setting up a modern application stack with Docker Compose, PostgreSQL (v14+), and PgBouncer for connection pooling, you've likely hit this frustrating error in your logs or database client (like DBeaver):

FATAL: server login failed: wrong password type
Enter fullscreen mode Exit fullscreen mode

This occurs because modern PostgreSQL defaults to the secure SCRAM-SHA-256 authentication method, but PgBouncer's default configuration often can't handle it seamlessly.

👎 The Common (and Flawed) Workaround: Downgrading to MD5

Many online guides suggest the quick fix: force your PostgreSQL container to use the deprecated MD5 method by adding environment variables like POSTGRES_HOST_AUTH_METHOD=md5.

# ⚠️ WARNING: DO NOT USE THIS FOR PRODUCTION!
postgres:
  # ...
  environment:
    # ...
    POSTGRES_HOST_AUTH_METHOD: md5 
    POSTGRES_INITDB_ARGS: --auth=md5
Enter fullscreen mode Exit fullscreen mode

This works, but it forces your production-ready PostgreSQL server to use a weaker, outdated encryption standard.

🛡️ The Secure Solution: SCRAM Hash Transfer

The correct, production-grade fix is to let PostgreSQL keep its secure SCRAM-SHA-256 authentication and manually configure PgBouncer to use the exact SCRAM hash generated by PostgreSQL.

This approach keeps your database secure and resolves the "wrong password type" error.

Prerequisites: PgBouncer Setup

We will assume you are using the popular edoburu/pgbouncer image and have mapped a local userlist.txt file into the container.

1. Create the userlist.txt file
Create a local directory (e.g., ./pgbouncer) and an empty userlist.txt file inside it.

2. Update docker-compose.dev.yml
Ensure the volume mount is present on your pgbouncer service:

# docker-compose.dev.yml snippet
pgbouncer:
  image: ${PGBOUNCER_IMAGE} # e.g., edoburu/pgbouncer:latest
  # ... other config
  volumes:
    # Map the local file to the container path
    - ./pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt
Enter fullscreen mode Exit fullscreen mode

Step 1: Get the SCRAM Hash from PostgreSQL

The first step is to retrieve the SCRAM-hashed password string for your database user from the PostgreSQL system tables.

  1. Start your stack (the PgBouncer container will fail to connect for now, but that's fine):

    docker-compose -f docker-compose.dev.yml --env-file .env.dev up -d

  2. Connect to the PostgreSQL Container using docker exec and the psql client. (Adjust container name and user as needed from your .env.dev file, e.g., tnt_postgres_dev and tnt_admin).

    docker exec -it tnt_postgres_dev psql -U tnt_admin -d tnt_dev

  3. Execute the Query to Extract the Hash:
    This query pulls the encrypted password from the pg_shadow table and prefixes it correctly for PgBouncer.

    SELECT 'SCRAM-SHA-256$' || passwd FROM pg_shadow WHERE usename = 'tnt_admin';

    Example Output:

                      ?column?
----------------------------------------------------------------------------------------------------------------------------------
SCRAM-SHA-256$2048:y+y1d/g.../z2+9eJ/1+Q==$:GzB3... # <--- COPY THIS ENTIRE STRING
(1 row)

    Copy the entire string starting from SCRAM-SHA-256$.

Step 2: Manually Update userlist.txt

Edit your local pgbouncer/userlist.txt file and paste the copied hash. The format required by PgBouncer is:

"username" "hash_string"

./pgbouncer/userlist.txt

"tnt_admin" "SCRAM-SHA-256$2048:y+y1d/g.../z2+9eJ/1+Q==$:GzB3..."
Enter fullscreen mode Exit fullscreen mode

Step 3: Final Restart and Connection

  1. Stop and Recreate Containers:
    You must stop the PgBouncer container and re-run up -d to load the newly updated userlist.txt.

    docker-compose -f docker-compose.dev.yml --env-file .env.dev down
docker-compose -f docker-compose.dev.yml --env-file .env.dev up -d

Your PgBouncer service will now start and successfully use the secure SCRAM hash to authenticate with PostgreSQL. You can connect your application or database client (like DBeaver) to the PgBouncer port (e.g., 6432) using your original plaintext password.

This method successfully resolves the authentication issue while keeping your PostgreSQL security settings at the modern standard. Happy pooling!

Top comments (0)