Unable to authenticate to PostgreSQL using PGPASSFILE environment variable

[dot-mike]

Hi,

First off, this might be an upstream bug, unsure.

I am unable to authenticate to my postgresql database using environment variable PGPASSFILE to point to a file containing the password for my database. Storing the same password in the environment variable PGPASSWORD surprisingly works.

These two environment variables are defined in the postgres docs for env vars

PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using a password file (see Section 34.16).

PGPASSFILE behaves the same as the passfile connection parameter.

password file documentation: https://www.postgresql.org/docs/16/libpq-pgpass.html

There is a foot note that the file it self needs to have access mode 0600 which docker can do with mode parameter in secrets-block

---

Example environment (using Docker compose!)

docker-compose.yml

version: '3.8'

secrets:
  coredns_user_postgres_password:
    file: ./coredns.pgpass

services:
  coredns:
    build:
      context: .
      dockerfile: ./Coredns.dockerfile
      args:
        VERSION: 1.11.1
        TARGETPLATFORM: linux/amd64
    volumes:
      - ./coredns-config:/etc/coredns
    ports:
      - 53:53/udp
    init: true
    restart: always
    command: -conf /etc/coredns-config/Corefile
    depends_on:
      - postgresql
    environment:
      - PGPASSFILE=/run/secrets/coredns_user_postgres_password
    secrets:
      - source: coredns_user_postgres_password
        mode: 0600

  postgresql:
    image: bitnami/postgresql:16.2.0
    init: true
    volumes:
      - db:/bitnami/postgresql
    environment:
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=postgres
    ports:
      - 5432:5432

volumes:
  db
    driver: local

coredns.pgpass

*:*:*:postgres:postgres

Logs:

# docker compose logs -tf coredns
coredns-1  | 2024-02-16T11:03:26.017368987Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:26.437155600Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:26.880961062Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:27.518046141Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:28.545084505Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:30.389228798Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:33.838719388Z pq: password authentication failed for user "coredns"
coredns-1  | 2024-02-16T11:03:40.477094519Z pq: password authentication failed for user "coredns"