Role "postgres" does not exist

[bistace]

Hello,

I am trying to connect a Django server to a PSQL database but it seems like the database is not created with the correct user in my docker-compose.yml file. I get the following error:

gestion_materiel_tps-db-1   | 2023-03-06 08:19:53.351 UTC [70] FATAL:  password authentication failed for user "postgres"
gestion_materiel_tps-db-1   | 2023-03-06 08:19:53.351 UTC [70] DETAIL:  Role "postgres" does not exist.
gestion_materiel_tps-db-1   |   Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

Here is my docker-compose.yml:

version: "3"

services:
  app:
    build:
      context: .
    ports:
       - 80:80
       - 443:443
    command: 
      /opt/app/start-server.sh
    environment:
      - DB_HOST=db
      - DB_NAME=database_gestion_tps
      - DB_USER=postgres_user
      - DB_PASS=*
    depends_on:
      - db
      
  db:
    image: postgres:13-alpine
    environment:
      - POSTGRES_DB=database_gestion_tps
      - POSTGRES_USER=postgres_user
      - POSTGRES_PASSWORD=*
    ports:
      - "5432:5432"
    volumes:
      - db_gestion_tps:/var/lib/postgresql/data

As I used the POSTGRES_USER variable in the environment section, I expected the database to be created with the postgres_user user but if we look at the log from docker compose up, we can see that it is owned by the postgres user:

gestion_materiel_tps-db-1   | The files belonging to this database system will be owned by user "postgres".
gestion_materiel_tps-db-1   | This user must also own the server process.
gestion_materiel_tps-db-1   | 
gestion_materiel_tps-db-1   | The database cluster will be initialized with locale "en_US.utf8".
gestion_materiel_tps-db-1   | The default database encoding has accordingly been set to "UTF8".
gestion_materiel_tps-db-1   | The default text search configuration will be set to "english".
gestion_materiel_tps-db-1   | 
gestion_materiel_tps-db-1   | Data page checksums are disabled.

Could you please help me with this?

[yosifkit]

gestion_materiel_tps-db-1 | The files belonging to this database system will be owned by user "postgres".
gestion_materiel_tps-db-1 | This user must also own the server process.

This is talking about the system user; i.e. user ID 70 in the Alpine based images:

postgres/13/alpine/Dockerfile

Lines 9 to 13 in 156d065

	# 70 is the standard uid/gid for "postgres" in Alpine
	# https://git.alpinelinux.org/aports/tree/main/postgresql/postgresql.pre-install?h=3.12-stable
	RUN set -eux; \
	addgroup -g 70 -S postgres; \
	adduser -u 70 -S -D -G postgres -H -h /var/lib/postgresql -s /bin/sh postgres; \

---

The PostgreSQL database user is created in the entrypoint and is the one specified by POSTGRES_USER. If POSTGRES_USER is specified, then there is no database user called postgres.

postgres/13/alpine/docker-entrypoint.sh

Line 92 in 156d065

eval 'initdb --username="$POSTGRES_USER" --pwfile=<(printf "%s\n" "$POSTGRES_PASSWORD") '"$POSTGRES_INITDB_ARGS"' "$@"'

See also, the Docker Hub readme: https://github.com/docker-library/docs/tree/e93496cc5983da36a1d304237bfc5fd76185a5c2/postgres#postgres_user

---

A quick test:

$ docker network create postynet
$ docker run -it --rm -e POSTGRES_USER=postgres_user -e POSTGRES_PASSWORD=mysecretpassword --name posty --network=postynet postgres:alpine
...
2023-03-06 18:28:25.490 UTC [1] LOG:  database system is ready to accept connections
(after attempting to connect with -U postgres)
2023-03-06 18:29:48.648 UTC [65] FATAL:  password authentication failed for user "postgres"
2023-03-06 18:29:48.648 UTC [65] DETAIL:  Role "postgres" does not exist.
	Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
...
$ docker run -it --rm --network=postynet -e PGPASSWORD=mysecretpassword postgres:alpine psql -U postgres -h posty
psql: error: connection to server at "posty" (172.20.0.2), port 5432 failed: FATAL:  password authentication failed for user "postgres"
$ docker run -it --rm --network=postynet -e PGPASSWORD=mysecretpassword postgres:alpine psql -U postgres_user -h posty
psql (15.2)
Type "help" for help.

postgres_user=# ^D\q

[bistace]

Thank you for the explanations! However, I do not understand what I have to change in my docker-compose file, it looks correct to me. Do you see something that needs to be changed?

[tianon]

From your error message, it seems like your application might be hard-coded to postgres instead of reading from your DB_USER environment variable.

Unfortunately, we do not have the bandwidth to provide in-depth integration/deployment/environment debugging or support here; these sorts of questions/requests would be more appropriately posted to a dedicated support forum, such as the Docker Community Slack, Server Fault, Unix & Linux, or Stack Overflow.

[jhoughjr]

So the answer is compose can't do it?

[yosifkit]

@jhoughjr, we have an example compose.yml on the Docker Hub page. The postgres image can be deployed in many ways. If you have a specific question that isn't covered in the Docker Hub readme, please open a new issue.