Failure to generate userlist.txt

[ejoebstl]

Steps to reproduce:

docker run --rm \
    -e DB_USER=****** \
    -e DB_PASSWORD=******** \
    -e DB_HOST=******** \
    -e DB_NAME=postgres \
    -p 5432:5432 \
    edoburu/pgbouncer

Gives the following error:

2021-01-23 16:08:45.798 UTC [1] ERROR could not open auth_file /etc/pgbouncer/userlist.txt: No such file or directory

Subsequently, connecting to the database fails:

2021-01-23 16:09:06.757 UTC [1] LOG C-0x7f04932a30f0: (nodb)/(nouser)@127.0.0.1:46564 no such user: ******

Shouldn't that be done automatically?

[ejoebstl]

I've worked around it by building my own image with a custom userlist, like the doc suggests:

FROM edoburu/pgbouncer
COPY userlist.txt /etc/pgbouncer/

[tiago156]

Same here:

2021-01-23 22:28:25.477 UTC [1] ERROR could not open auth_file /etc/pgbouncer/userlist.txt: No such file or directory

Sorry to bother you, but all my containers just crashed, if you have time to fix it I appreciate it.

Thank you

[cheungpat]

It appears recent changes caused userlist.txt to be absent, and the entrypoint.sh do not create the file if it is missing

d33ec7c

Any workaround that places userlist.txt under /etc/pgbouncer would work, I changed my run command to touch /etc/pgbouncer/userlist.txt before running the entrypoint script.

[yashmehrotra]

@cheungpat The new code was pushed on the same tags which created existing containers to fail as well.

I made a Dockerfile from the sha of the working one and pushed it to my own docker repository.

# Dockerfile
FROM edoburu/pgbouncer@sha256:e173802088fdbd6626a2e9ca66d01c4be029ca04710cca73ae818efa1f4d7dd7

[The-Fonz]

edoburu/pgbouncer@sha256:e173802088fdbd6626a2e9ca66d01c4be029ca04710cca73ae818efa1f4d7dd7

Our systems went down as well, pinning the image to this exact SHA fixes it for us.

[urbanadventurer]

Thanks for pointing out the cause of this problem. I mounted an empty file in a volume as a workaround.

  pgbouncer:
    image: edoburu/pgbouncer
    volumes:
      - ./pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt
    environment:
      - DB_USER=dbuser
      - DB_PASSWORD=
      - DB_HOST=postgres
      - DB_NAME=db
      - POOL_MODE=transaction
      - MAX_CLIENT_CONN=100
      - ADMIN_USERS=postgres,dbuser
    expose:
      - "5432" # for postgres
    depends_on:
      - postgres
    networks:
      - backend

[brandonhilkert]

Same happened to us. Can confirm #33 (comment) fixes it for now.

[alexandersm]

The same happened with our deployment, pgbouncer does not start anymore after such an update !

[abandali]

Agreed, this was a painful commit: it updated the existing tags which borked working deployments, and it was half-baked (there are other references to userlist.txt further down in entrypoint.sh which were not updated to use _AUTH_FILE)
Also, the empty userlist.txt file work-arounds do not work if you've got pgbouncer in a container inside a pod, so the only feasible workaround for us is to either pin to the sha or replace with a customized image - not ideal for a heavy-traffic prod environment

[esalter]

I appreciate all the work maintainers do but in the future please tag releases using semver. It would have helped us as we were targeting 1.14.0, not latest, so I would not have expected that to update.

[Clean-Cole]

Dodged a bullet with this one. Anyone need help with tests? Seems like this image might be at a point that its warranted.

[loozhengyuan]

@esalter I think the images are indeed properly tagged using semver, and you should still be getting 1.14.0 if you pulled edoburu/pgbouncer:1.14.0.

EDIT: See #35 for proposal for immutable tags

[GrizimJeeng]

@esalter I think the images are indeed properly tagged using semver, and you should still be getting 1.14.0 if you pulled edoburu/pgbouncer:1.14.0.

EDIT: See #35 for proposal for immutable tags

Hi,
maybe I'm missing something,
but is there no way just to avoid pushing images that already pushed to Dockerhub?
why old versions got pushed 3 days ago?
https://hub.docker.com/r/edoburu/pgbouncer/tags?page=1&ordering=last_updated

[loozhengyuan]

@grizim-power Ordinarily, yes, but not feasible for a project like edoburu/docker-pgbouncer that provides container images to a corresponding pgbouncer release. Take the current release for example; if it is buggy or contains security vulnerabilities, then we will need to push to the same image tag anyway. The fix could also end up creating a new bug for another set of users as well. So, IMO, the correct approach is to change the versioning scheme/practice, as proposed in #35, so that we can maintain immutability of the image tags while providing (1) versioned releases and (2) releases mapped to upstream pgbouncer versions.

[GrizimJeeng]

@grizim-power Ordinarily, yes, but not feasible for a project like edoburu/docker-pgbouncer that provides container images to a corresponding pgbouncer release. Take the current release for example; if it is buggy or contains security vulnerabilities, then we will need to push to the same image tag anyway. The fix could also end up creating a new bug for another set of users as well. So, IMO, the correct approach is to change the versioning scheme/practice, as proposed in #35, so that we can maintain immutability of the image tags while providing (1) versioned releases and (2) releases mapped to upstream pgbouncer versions.

IMO, even if we have a bug or security vulnerabilities - we should not push a new image with the same tag.
Override existing image losing the point of versioning.

[loozhengyuan]

@grizim-power Yeah, completely agree, image tags should be immutable.

[vdboor]

Yikes! I've reverted the grep change. I didn't expect this would affect you all.

I personally don't think immutable docker tags are feasible, as security updates are generally published under the same tag (e.g. rebuilding on top of a newer base image).

However, I think these things are bigger then me. If some of you (or a group) would like to implement this, you're more then welcome to take up maintainer-ship, and take this project to a next level. I don't want to be a limiting factor here.

[cheungpat]

I personally don't think immutable docker tags are feasible, as security updates are generally published under the same tag (e.g. rebuilding on top of a newer base image).

That is my understanding too. Official docker hub images are updated with the same tag from time to time.

However, I think these things are bigger then me. If some of you (or a group) would like to implement this, you're more then welcome to take up maintainer-ship, and take this project to a next level. I don't want to be a limiting factor here.

Thank you for providing this docker image and I think it makes configuring pgbouncer much easier.

[Natrinicle]

I created a PR with a fix that should work for creating userlist.txt if it doesn't exist while not touching one that does.

#37

[Natrinicle]

And here's another PR with the fix that I previously suggested as well as a bit of new code to allow for ENV variables to create admin and stats users by providing an equal number of passwords as users to ADMIN_PASSWORDS and STATS_PASSWORDS ENV variables using the same comma delimiter. This one's a bit more out there but should work well for many use cases. Only one of the two needs to be used to fix this issue though.

#38

[bemanuel]

Reading the script "'entrypoint.sh" the question is, you need to inform the variable DATABASE_URL then, "entrypoint.sh" will work and generate userlist.txt

[MathiasDrapier]

When I try to mount a volume with an empty userlist.txt file I get the following error:
/entrypoint.sh: line 49: can't create /etc/pgbouncer/userlist.txt: Permission denied

[DustinJSilk]

@MathiasDrapier Did you find a solution to the Permission denied error, not being allowed to write to a secret volume?

[MathiasDrapier]

@DustinJSilk Yep take a look on my "tuto"

• Create a directory /home/debian/postgres which will be mount as a volume for stored database data

• Create a Docker network direct_web with this command :

docker network create -d overlay --scope swarm direct_web

• Create a file userlist.txt like this :

"postgres" "your_password"

• Create a Dockerfile like this :

FROM edoburu/pgbouncer COPY userlist.txt /etc/pgbouncer/

• Build the docker image according to this DockerFile :

docker build -t test_pgbouncer .

• Create stack_psql_pgbouncer.yml file and add this content :

stack_psql_pgbouncer.yml
version: "3.7"
services:
  psql:
    image: postgres
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD= #your_password#
      - PGDATA=/data/postgres
    volumes:
       - /home/debian/postgres:/data/postgres
    networks:
      - direct_web
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
      placement:
        constraints:
         - node.hostname == node1
  pgbouncer:
    image: test_pgbouncer:latest
    environment:
      - DB_USER=postgres
      - DB_PASSWORD=     #your_password#
      - DB_HOST=psql
      - POOL_MODE=transaction
      - ADMIN_USERS=postgres
    ports:
      - "5432:5432"
    networks:
      - direct_web
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
      placement:
        constraints:
         - node.hostname == node1
    depends_on:
      - psql
networks:
  direct_web:
    name: direct_web
    external: true

• Deploy this service with this command :

sudo docker stack deploy --compose-file stack_psql_pgbouncer.yml psql

[DustinJSilk]

Very detailed, thank you @MathiasDrapier!

[alesima]

When I mount the file using the kubernetes example I get the following error:

/entrypoint.sh: line 50: can't create /etc/pgbouncer/userlist.txt: Permission denied

[nadilas]

When I mount the file using the kubernetes example I get the following error:

/entrypoint.sh: line 50: can't create /etc/pgbouncer/userlist.txt: Permission denied

Yes this still exists, but not unsolvable.

You can get around it by mounting a modified entrypoint into the container using a mounted folder which is not created in advance. The issue comes from the folder having no group write permissions:
drwxr-xr-x 2 postgres root 63 Sep 4 2021 pgbouncer

I haven't found another way to override the PG_CONFIG_DIR.

Ref:s

• Support Arbitrary User IDs #23

        volumes:
        - name: config
          emptyDir: {}
        - name: entrypoint-cfg
          configMap:
            name: psql-db-bouncer
            defaultMode: 365
            
          command:
            - ./entrypoint.sh
            - /usr/bin/pgbouncer
            - /etc/pgbouncer/custom/pgbouncer.ini
          volumeMounts:
            - name: config
              mountPath: /etc/pgbouncer/custom
            - name: entrypoint-cfg
              mountPath: /entrypoint.sh
              subPath: entrypoint.sh
  

• pgbouncer extension points PostHog/charts-clickhouse#104
  set user = in the custom entrypoint