Why can't I extend docker postgres image to create extra database and user

Question

I'm using docker postgres 9.4.5 images and extendeding it using an init.sh copied to /docker-entrypoint-initdb.d in the docker container. I am trying to create another database and a non admin user that as access to both $POSTGRES_DB as well as the 2nd database I am creating. I tried the following where $POSTGRES_USER is the user myadmin and $POSTGRES_DB is the database mydb1 passed through docker-compose environment:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
   CREATE USER userx WITH password '$POSTGRES_PASSWORD';
   CREATE DATABASE diagnostics;
   GRANT ALL PRIVILEGES ON DATABASE userx TO $POSTGRES_DB;
   GRANT ALL PRIVILEGES ON DATABASE userx TO mydb2;
EOSQL

This gave me an error:

postgres_1   | CREATE DATABASE
postgres_1   | 
postgres_1   | CREATE ROLE
postgres_1   | 
postgres_1   | 
postgres_1   | /docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init-user-db.sh
postgres_1   | FATAL:  database "myadmin" does not exist
postgres_1   | psql: FATAL:  database "myadmin" does not exist
docker_postgres_1 exited with code 2

I then tried it with --username postgres and now get the error

ERROR:  database "userx" does not exist

I then tried to create the database userx: #!/bin/bash set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
   CREATE DATABASE userx;       
   CREATE USER userx WITH password '$POSTGRES_PASSWORD';
   CREATE DATABASE diagnostics;
   GRANT ALL PRIVILEGES ON DATABASE userx TO $POSTGRES_DB;
   GRANT ALL PRIVILEGES ON DATABASE userx TO mydb2;
EOSQL

but got:

postgres_1   | CREATE DATABASE
postgres_1   | ERROR:  role "mydb1" does not exist
postgres_1   | STATEMENT:  GRANT ALL PRIVILEGES ON DATABASE rwx TO analytics;
postgres_1   | ERROR:  role "mydb2" does not exist
docker_postgres_1 exited with code 3

Can someone please help, this is a real blocker for me and I have no idea how to proceed as I feel that I should not need to be creating the database userx or the roles , or am I wrong?

i think your GRANT is not correct... it's GRANT ALL PRIVILEGES ON yourdbname not user TO your user — user3012759
– user3012759, Commented Nov 17, 2016 at 15:46
@user3012759 Thank you soo much, sill school boy error on my part. If you want to add this as an answer I will happily accept it — jonnie
– jonnie, Commented Nov 17, 2016 at 15:53

user3012759 · Accepted Answer · 2016-11-17 15:57:31Z

2

Your GRANT syntax was not quite right - https://www.postgresql.org/docs/9.0/static/sql-grant.html

In short should be like that:

GRANT ALL PRIVILEGES ON yourdbname not user TO your user;

answered Nov 17, 2016 at 15:57

user3012759

2,11520 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why can't I extend docker postgres image to create extra database and user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related