User and DB were not created from environment variable arguments

[Bryksin]

Hi

Having problem to set database name with database user, details are below:

Dockerfile:

FROM postgres:10.4-alpine

LABEL maintainer="Vadim Bryksin <removed_my_email_from_internet@gmail.com>"

HEALTHCHECK --interval=30s --timeout=15s CMD pg_isready -U postgres  || exit 1

Building docker:

docker build -t postgres_db . -f deployment\db\Dockerfile

Docker images:

REPOSITORY                 TAG                 IMAGE ID            CREATED             SIZE
postgres_db                latest              541471b4064f        5 seconds ago       39.5MB
postgres                   10.4-alpine         f298e9fa532e        6 days ago          39.5MB
docker4w/nsenter-dockerd   latest              cae870735e91        7 months ago        187kB

Running container with:

docker run -it --rm --name postgres_db -v db-data:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_DB=snippet_db -e POSTGRES_USER=snippet_user -e POSTGRES_PASSWORD=P@88w0rd postgres_db

Connecting trough Navicat UI tool:

Postgres output log:

2018-05-31 21:57:03.245 UTC [147] FATAL:  password authentication failed for user "snippet_user"
2018-05-31 21:57:03.245 UTC [147] DETAIL:  Role "snippet_user" does not exist.
        Connection matched pg_hba.conf line 95: "host all all all md5"

Changing user to default: postgres

Postgres output log:

2018-05-31 22:00:07.921 UTC [195] FATAL:  database "snippet_db" does not exist

Changing to default DB:

Conclusion:
From all environment variables passed during the container execution was accepted only: POSTGRES_PASSWORD

Other variables: POSTGRES_USER and POSTGRES_DB were ignored!
Possibly relates to an already closed issue: #41

[wglambert]

pgtest is the build of your Dockerfile

$ docker run -dit --rm --name postgres_db -v db-data:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_DB=snippet_db -e POSTGRES_USER=snippet_user -e POSTGRES_PASSWORD=P@88w0rd pgtest
f8b720b412fcce9866bae16c1403cf80bef578a9d2e26d9996c0a95749232e11
$ docker exec -it postgres_db bash
# psql -d snippet_db -U snippet_user -W
Password for user snippet_user: 
psql (10.4)
Type "help" for help.

snippet_db=# 

db-data is an empty directory in the build context
I'm not familiar with navicat, but the problem might reside there

[wglambert]

Issue might be that the password is entered incorrectly, database snippet_db isn't specified to psql

I've checked cross-container connectivity for psql as well

$ docker run -dit --rm --name postgres_db -v db-data:/var/lib/postgresql/data -p 5432:5432 --network=network -e POSTGRES_DB=snippet_db -e POSTGRES_USER=snippet_user -e POSTGRES_PASSWORD=P@88w0rd pgtest
b05db68967be0c9eec8117d86a2449e4d9b054d90c543a189e8e71d73c960a23

$ docker run -dit --rm --name postgres_db1 -v db-data:/var/lib/postgresql/data --network=network -e POSTGRES_DB=snippet_db -e POSTGRES_USER=snippet_user -e POSTGRES_PASSWORD=P@88w0rd pgtest 337a4f8617f188c32e13f3e182c6a117df1b2a958c850bdb38e9b5fbd3f1b8d9

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                   PORTS                    NAMES
337a4f8617f1        pgtest              "docker-entrypoint.s…"   2 minutes ago       Up 2 minutes (healthy)   5432/tcp                 postgres_db1
b05db68967be        pgtest              "docker-entrypoint.s…"   3 minutes ago       Up 3 minutes (healthy)   0.0.0.0:5432->5432/tcp   postgres_db

$ docker exec -it postgres_db bash
# psql -h postgres_db1 -d snippet_db -U snippet_user -W
Password for user snippet_user: 
psql (10.4)
Type "help" for help.

snippet_db=# 

[Bryksin]

Uhhh it is definitely not a navicat problem as well as not a wrong password, as during those 3 screenshots with connection info in navicat - password was entered only once, it wasn't re-entered for each screenshot, and at the 3d attempt with default user and db it did work out

Also, DB logs from container clearly tells the problem:

2018-05-31 21:57:03.245 UTC [147] DETAIL:  Role "snippet_user" does not exist.

and

2018-05-31 22:00:07.921 UTC [195] FATAL:  database "snippet_db" does not exist

Regarding db-data - it is docker volume which was created with this command:

docker volume create --name db-data -d local

It was done so because I'm on Win platform and trying to use NTFS for postgress data which is running in Linux environment, as the solution I found the recommendation to use docker volume - link to the solution

[yosifkit]

Did you ever start the postgres container without specifiying the user and database? If there was a database already initialized once in your Docker volume, then later runs with different env vars will not change the users, that is only done on first initialization.

See also #203 (comment)

[Bryksin]

docker volume prune

That was exactly the case :)
Docker volume had already stored data from previous runs, where I didn't set the user and DB yet.
Closing ticket, my bad! sorry guys and thank you for detecting this problem

[vlaksi]

docker volume prune

That was exactly the case :)
Docker volume had already stored data from previous runs, where I didn't set the user and DB yet.
Closing ticket, my bad! sorry guys and thank you for detecting this problem

You saved plenty of time for my friend, tnx !!

[0x7An]

It's important to notice the order of the env vars declared it has to be in the exact order as follows

POSTGRES_DB: mydb
POSTGRES_USER: myuser
POSTGRES_PASSWORD: mypassword

[HachemOuanes]

You can always initialize scripts for your database before running the container that if even in case you want to add multiple users or create tables initially inside of your container. a basic script would be like :

set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE database_name;
    CREATE USER new_user_name WITH ENCRYPTED PASSWORD 'new_password';
    GRANT ALL PRIVILEGES ON DATABASE new_user_name TO database_nam;
EOSQL

and can be saved as a shell file (init_db.sh for example).
Make sure at last to copy the file from your relative directory to the container by adding it to the docker-compose.yml file :

services:
  db:
    image: postgres:latest
    ports:
      - '4020:5432'
    environment:
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=postgres
    volumes: 
      - ./init_db.sh:/docker-entrypoint-initdb.d/init_db.sh

[Mishatopkek]

In my case, I've already had PostgreSQL working in the background on windows. And when I tried to connect to the docker container, I connected to my windows PostgreSQL. You need to open services.msc and turn off PostgreSQL