Configure pgbouncer and postgresql in docker-compose

Question

How can I apply connection pooling with pgbouncer in my docker-compose file. I don't know if the ConnectionString of my containers are correct or not.

  postgredb:
    image: postgres
    environment:
      - POSTGRES_USER=dbuser
      - POSTGRES_PASSWORD=dbpassword
      - POSTGRES_DB=postgres
    restart: always
    volumes:
      - pg-data:/var/lib/postgresql/data/

  pgbouncer:
    image: edoburu/pgbouncer
    environment:
      - DB_HOST=postgredb
      - DB_PORT=5432
      - DB_USER=dbuser
      - DB_PASSWORD=dbpassword
      - ADMIN_USERS=postgres,admin     
    ports:
      - "5432:5432"

  commenting.api:
    environment:
      - ConnectionString=Server=pgbouncer;Port=5432;Database=commentDb;UId=dbuser;Password=dbpassword;Pooling=true;
    ports:
      - "5051:80"

  posting.api:
    environment:
      - ConnectionString=Server=pgbouncer;Port=5432;Database=postDb;UId=dbuser;Password=dbpassword;Pooling=true;
    ports:
      - "5052:80"

I get this error Npgsql.PostgresException: '08P01: server login failed: wrong password type'.

So far the closest configuration I found was in this repository edoburu/docker-pgbouncer

Answer 1

It looks as if pgbouncer expects md5 authentication by default, but modern versions of postgres default to scram-sha-256. You can force postgres to use md5 auth by setting the POSTGRES_HOST_AUTH_METHOD and POSTGRES_INITDB_ARGS environment variables:

services:
  postgres:
    image: postgres
    environment:
      - POSTGRES_USER=dbuser
      - POSTGRES_PASSWORD=dbpassword
      - POSTGRES_DB=postgres
      - POSTGRES_HOST_AUTH_METHOD=md5
      - POSTGRES_INITDB_ARGS=--auth=md5
    volumes:
      - pgdata:/var/lib/postgresql/data/
      - ./initdb.d:/docker-entrypoint-initdb.d

  pgbouncer:
    image: edoburu/pgbouncer:latest
    environment:
      - DB_HOST=postgres
      - DB_PORT=5432
      - DB_USER=dbuser
      - DB_PASSWORD=dbpassword
      - ADMIN_USERS=postgres,admin
    ports:
      - "5432:5432"

volumes:
  pgdata:

With this configuration, I am able to successfully connect to the database through pgbouncer.

Answer 2

As @Iarsks explained, new versions of postgresql (from 14) use scram-sha-256 by default. Good article about that here

It is possible to use pgbouncer with AUTH_TYPE=scram-sha-256 but by using image: rmccaffrey/pgbouncer:latest docker image, as stated in this Github Issue

Also in production with kubernetes for example, make sure to use 127.0.0.1 instead of localhost as the database host.

I created a github repository for showing how to configure things, md5 and scram with last postgresql versions.