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

score 1 · Answer 1 · answered Apr 18 '23 at 18:29

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.

answered Apr 18 '23 at 18:29

larsks

277,717
41
399
399

And should I change `connectionstrings` in the containers(in the question)? – Kaveh Naseri Apr 19 '23 at 14:44
That shouldn't be necessary; nothing in the connection string is impacted by this configuration. – larsks Apr 19 '23 at 19:57
But when I use this `ConnectionString=Server=pgbouncer;Port=5432;Database=postDb;UId=dbuser;Password=dbpassword;Pooling=true;` I get error `InvalidOperationException` – Kaveh Naseri Apr 19 '23 at 20:04
I'm not sure what's causing that error. I don't know what software you're using that interprets that connection string. Your question was specifically about getting pgbouncer to talk to postgres, and using the configuration in this answer you can verify (e.g. using the `psql` command) that it works as expected. If you're able to successfully test the connection using `psql` but you're getting errors with your software, that might be worth opening a new question. – larsks Apr 19 '23 at 23:12

score 0 · Answer 2 · answered Apr 27 '23 at 12:53

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.

Configure pgbouncer and postgresql in docker-compose

2 Answers2