Howto pass POSTGRES_USER env variable when using docker-compose .yml for docker swarm

Question

I'm starting a docker swarm with a PostgreSQL image. I want to create a user named 'numbers' on that database.

This is my docker-compose file. The .env file contains POSTGRES_USER and POSTGRES_PASSORD. If I ssh into the container hosting the postgres image, I can see the variables when executing env. But psql --user numbers tells me that role "numbers" does not exists.

How should I pass the POSTGRES_* vars so that the correct user is created?

version: '3'

services:

  postgres:
    image: 'postgres:9.5'
    env_file:
       - ./.env
    ports:
      - '5432:5432'
    volumes:
      - 'postgres:/var/lib/postgresql/data'
    deploy:
      replicas: 1
    networks:
      - default
    restart: always

This creates the postgresql user as expected.

 $ docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=numbers -d postgres

Answer 1

When Postgres find its data directory already initialized, he does not run the initialization script. This is the check:

if [ ! -s "$PGDATA/PG_VERSION" ]; then
....

So I recommend you to manually create that user or start from scratch (removing your volume if you can afford it, loosing the data). From command line:

docker volume ls
docker volume rm <id>