Search code examples
postgresqldocker

How to set the password for the Posgres 16.X with Docker 25.0.X?

Sounds like the basic skill, however it looks like the Docker's and/or PosgreSQL's API has pretty changed, so many command I have googled does not work anymore. Thus let us begin from versions list.

Version list

Client: Docker Engine - Community

 Version:           25.0.4
 API version:       1.44
 Go version:        go1.21.8
 Git commit:        1a576c5
 Built:             Wed Mar  6 16:32:12 2024
 OS/Arch:           linux/amd64
 Context:           default

About Posgres, for the postgres image, it should be latest one for the April 2024, namely 16.2.

docker-compose.yaml

Most likely, the frontserver service does not matter for this topics, but I have left it just in case.

version: "3.5"

services:

  Database:

    image: postgres
    container_name: Example-Production-Database
    ports: [ "${DATABASE_PORT}:${DATABASE_PORT}" ]

    environment:

      - POSTGRES_PASSWORD=${DATABASE_PASSWORD}

    volumes:
      - DatabaseData:/data/example.jp

volumes:
  DatabaseData: {}

The Postgres required POSTGRES_PASSWORD environment variable, but Docker detects the .env file, so I have referred to DATABASE_PASSWORD environment variable.

.env

Assume that the correct password is ACTUAL_PASSWORD.

DATABASE_ENVIRONMENT_NAME="STAGING"
DATABASE_HOST="Database"
DATABASE_PORT="5432"
DATABASE_USER_NAME="postgres"
DATABASE_PASSWORD="ACTUAL_PASSWORD"

Docker command

sudo docker compose up --build

Connection attempt

Because my application must be run on VPS, the host is not the "localhost" - it must the IP address of the VPS which I can not share. Trying to connect with ACTUAL_PASSWORD:

enter image description here

Output:

Example-Production-Database     | 2024-04-07 01:08:24.202 UTC [93] FATAL:  password authentication failed for user "postgres"
Example-Production-Database     | 2024-04-07 01:08:24.202 UTC [93] DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"

Maybe the password has not been set correctly? Unfortunately, if to try default pass1234, nothing will change.

What have I missed?

Solution

  • The POSTGRES_PASSWORD environment variable is used to set the password.

    I don't have access to any information for your front_server service, but I'll substitute a test service that will connect to the database.

    version: "3.5"

services:
  Database:
    image: postgres
    container_name: Example-Production-Database
    ports:
      - "${DATABASE_PORT}:${DATABASE_PORT}"
    environment:
      - POSTGRES_USER=${DATABASE_USER_NAME}
      - POSTGRES_PASSWORD=${DATABASE_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -d postgres -U ${DATABASE_USER_NAME}"]
      interval: 5s
      timeout: 5s
      retries: 5

  test:
    image: postgres
    environment:
      - PGPASSWORD=${DATABASE_PASSWORD}
    command: sh -c "psql -h Database -U ${DATABASE_USER_NAME} -c 'SELECT * from pg_user;'"
    depends_on:
      Database:
        condition: service_healthy

    The test service will connect to the database and then run the SELECT * from pg_user; query.

    An important aspect of this setup is that the test service waits for the database to be "healthy". And the database in turn will only be "healthy" when it is ready to accept connections. This is important, otherwise the test service will try to connect before the database is ready. My hypothesis: this is the problem with your existing stack.

    In the screenshot below you can see:

    • (top panel) connection to database from the test service and
    • (bottom panel) connection to database from the host (using same password as specified in the .env file).

    enter image description here