There are three relevant environment variables: POSTGRES_USER,
POSTGRES_DB, POSTGRES_PASSWORD. The first two determine username and
database name of the superuser. Although postgres database is created in
any case. Using POSTGRES_PASSWORD one can specify superuser password.
Depending on presence or absence of the last one the init script decides
the authentication method for all remote connection.
With POSTGRES_PASSWORD it's md5, without trust
(host all all all $authMethod).
To add a non-superuser, one can do the following:
docker-compose.yml:
version: '3'
services:
pg:
image: postgres:12
environment:
PG_USER: u1
PG_DB: u1
volumes:
- ./init-pg.sh:/docker-entrypoint-initdb.d/init-pg.shinit-pg.sh:
#!/usr/bin/env bash
set -eu
psql -v ON_ERROR_STOP=1 \
-v PG_USER="$PG_USER" \
-v PG_DB="$PG_DB" \
<<-EOSQL
CREATE USER :PG_USER;
CREATE DATABASE :PG_DB;
GRANT ALL PRIVILEGES ON DATABASE :PG_DB TO :PG_USER;
EOSQLTo specify a password:
docker-compose.yml:
version: '3'
services:
pg:
image: postgres:12
environment:
POSTGRES_PASSWORD: ... # to enable md5 auth
PG_USER: u1
PG_DB: u1
PG_PASSWORD: ...
volumes:
- ./init-pg.sh:/docker-entrypoint-initdb.d/init-pg.shinit-pg.sh:
#!/usr/bin/env bash
set -eu
psql -v ON_ERROR_STOP=1 \
-v PG_USER="$PG_USER" \
-v PG_DB="$PG_DB" \
-v PG_PASSWORD="'$PG_PASSWORD'" \
<<-EOSQL
CREATE USER :PG_USER WITH PASSWORD :PG_PASSWORD;
CREATE DATABASE :PG_DB;
GRANT ALL PRIVILEGES ON DATABASE :PG_DB TO :PG_USER;
EOSQLdocker-compose.yml:
version: '3'
services:
pg:
image: postgres:12
bash:
image: bash
entrypoint: sleep 1000000000$ docker-compose exec bash apk add postgresql-client
$ docker-compose exec bash psql -h pg -U postgres -c '\l'
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
$ docker-compose exec bash psql -h pg -U postgres -c '\du'
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
docker-compose.yml:
version: '3'
services:
pg:
image: postgres:12
bash:
image: bash
entrypoint: sleep 1000000000
environment:
POSTGRES_PASSWORD: 123456$ docker-compose exec bash apk add postgresql-client
$ docker-compose exec bash psql -h pg -U postgres -c '\l'
Password for user postgres:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
$ docker-compose exec bash psql -h pg -U postgres -c '\du'
Password for user postgres:
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
docker-compose.yml:
version: '3'
services:
pg:
image: postgres:12
bash:
image: bash
entrypoint: sleep 1000000000
environment:
POSTGRES_USER: u1$ docker-compose exec bash apk add postgresql-client
$ docker-compose exec bash psql -h pg -U u1 -c '\l'
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+------------+------------+-------------------
u1 | u1 | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | u1 | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | u1 | UTF8 | en_US.utf8 | en_US.utf8 | =c/u1 +
| | | | | u1=CTc/u1
template1 | u1 | UTF8 | en_US.utf8 | en_US.utf8 | =c/u1 +
| | | | | u1=CTc/u1
(4 rows)
$ docker-compose exec bash psql -h pg -U u1 -c '\du'
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
u1 | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
docker-compose.yml:
version: '3'
services:
pg:
image: postgres:12
bash:
image: bash
entrypoint: sleep 1000000000
environment:
POSTGRES_DB: db1$ docker-compose exec bash apk add postgresql-client
$ docker-compose exec bash psql -h pg -U postgres -c '\l'
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
db1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
$ docker-compose exec bash psql -h pg -U postgres -c '\du'
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}