Postgres: Create multiple users and databases

[phlegx]

Hi!

I was wondering on how to create multiple databases with multiple users? As far as I have seen here, currently only one user that has superuser power and the corresponding database can be created trough POSTGRES_USER

Is creating multiple databases and users currently already possible with the ENV variables? If not, is there another way I can use via docker/docker compose?

[mmarzantowicz]

I think recommended way is to create new images based on postgres:xxx and add custom scripts there. Here is how I do this:

• Dockerfile

# vim:set ft=dockerfile:
FROM postgres:latest
MAINTAINER ...

# Custom initialization scripts
COPY ./create_user.sh   /docker-entrypoint-initdb.d/10-create_user.sh
COPY ./create_db.sh     /docker-entrypoint-initdb.d/20-create_db.sh

• create_user.sh

#!/bin/bash
set -e

POSTGRES="psql --username ${POSTGRES_USER}"

echo "Creating database role: ${DB_USER}"

$POSTGRES <<-EOSQL
CREATE USER ${DB_USER} WITH CREATEDB PASSWORD '${DB_PASS}';
EOSQL

• create_db.sh

#!/bin/bash
set -e

POSTGRES="psql --username ${POSTGRES_USER}"

echo "Creating database: ${DB_NAME}"

$POSTGRES <<EOSQL
CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};
EOSQL

Then I have to run my image with additional environment variables: DB_NAME, DB_USER, DB_PASS. If you want to create more than one db you can go with DB_XX_NAME and so on but it's really up to you how to name variables. Also note, that files files should be executable (+x).

For really massive deployments you can use environment file with docker-compose.

[mrts]

If you don't want to create a new image, it's also possible to mount the multiple DB creation script directory as a volume, see https://github.com/mrts/docker-postgresql-multiple-databases

[lewispham]

But still guys, why shouldn't we support this feature in this image?

[MartinKaburu]

If you would like to create multiple databases under one user or multiple users, use this script https://github.com/MartinKaburu/docker-postgresql-multiple-databases#using-multiple-databases-with-the-official-postgresql-docker-image

[yami12376]

Why this is not supported in the main image on docker hub?

[ChrisjanWust]

If you don't want to create a new image, it's also possible to mount the multiple DB creation script directory as a volume, see https://github.com/mrts/docker-postgresql-multiple-databases

If you would like to create multiple databases under one user or multiple users, use this script https://github.com/MartinKaburu/docker-postgresql-multiple-databases#using-multiple-databases-with-the-official-postgresql-docker-image

How would you use this script in conjunction with a docker volume (which maintaining data persistence)? Honestly, it feels like an ugly solution that quickly shows it's hackyness.

[tcurdt]

Is there any chance for this being reopened and improved?

[yosifkit]

Is there any chance for this being reopened and improved?

Short answer, no.
Slightly longer answer: https://github.com/docker-library/postgres/pull/240#issuecomment-314228559

[tcurdt]

@yosifkit Could you give an even longer answer, too?

Creating a new image is not great. Mounting a file to /docker-entrypoint-initdb.d/: is better - but again not really ideal. Having everything contained in a single yml is the prefered way with docker compose and friends.

So why is the resistance to improve this? I am honestly just curious if there is a good reason.