Setting up multiple mysql users without storing passwords

[tomnewport]

I would like to set up MySQL with two users; writeuser and readuser. My Dockerfile and build context are committed to a public git repository. Currently I create the users like so:

Dockerfile

FROM mysql:5.4
COPY setup.sql /docker-entrypoint-initdb.d/

setup.sql

CREATE DATABASE mydb;

CREATE USER 'readuser'@'%' IDENTIFIED BY 'passwordforreaduser';
GRANT SELECT ON mydb.* TO 'readuser'@'%';

CREATE USER 'writeuser'@'%' IDENTIFIED BY 'passwordforwriteuser';
GRANT INSERT, UPDATE, SELECT, DELETE ON mydb.* TO 'writeuser'@'%';

But this exposes passwords to anyone who can access the git repo. I would like to be able to pass passwords in as environment variables instead, but this isn't supported by the .sql script format.

What would be the best way of going about this?

[yosifkit]

You could put it into a shell script and have that interpret any environment variables for other passwords. Something like the following:

#!/bin/bash
set -e

mysql --protocol=socket -uroot -p$MYSQL_ROOT_PASSWORD <<EOSQL
CREATE DATABASE mydb;

CREATE USER 'readuser'@'%' IDENTIFIED BY '$CUSTOM_PASS_1';
GRANT SELECT ON mydb.* TO 'readuser'@'%';

CREATE USER 'writeuser'@'%' IDENTIFIED BY '$CUSTOM_PASS_2';
GRANT INSERT, UPDATE, SELECT, DELETE ON mydb.* TO 'writeuser'@'%';
EOSQL
# I don't think anything needs to be escaped in this heredoc.

Then just run it:

docker run -d -e MYSQL_ROOT_PASSWORD=foo -e CUSTOM_PASS_1=bar -e CUSTOM_PASS_2=baz mysql

This will still expose the passwords to the environment of the container, but they won't be in your git repo.

[tianon]

You could even use :- syntax to give them default values if they aren't supplied at runtime:

mysql --protocol=socket -uroot -p$MYSQL_ROOT_PASSWORD <<EOSQL
CREATE DATABASE mydb;

CREATE USER 'readuser'@'%' IDENTIFIED BY '${CUSTOM_PASS_1:-default-custom-pass-1}';
GRANT SELECT ON mydb.* TO 'readuser'@'%';
...

[cwhsu1984]

read the thread and help me solve my own problem, thanks guys!

[LaurentNoyon]

I'm having difficulties applying the advices above, here is my Dockerfile :

FROM mysql:5.7

ADD docker/mysql/script.sql /docker-entrypoint-initdb.d/script.sql
ADD docker/mysql/init_script_database.sh /docker-entrypoint-initdb.d/init_script_database.sh
RUN chmod -R 775 /docker-entrypoint-initdb.d

Here is my init_script_database.sh :

mysql --protocol=socket -uroot -p$MYSQL_ROOT_PASSWORD <<EOSQL
CREATE USER 'myuser_bis'@'%' IDENTIFIED WITH mysql_native_password AS '$MYSQL_SECOND_PASSWORD';GRANT USAGE ON *.* TO 'myuser_bis'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
CREATE DATABASE IF NOT EXISTS `myuser_bis`;
GRANT ALL PRIVILEGES ON `myuser\_bis`.* TO 'myuser_bis'@'%';
EOSQL

Here is my docker-compose :

[...]
# MySQL container
mysql:
    build:
        context: ../
        dockerfile: ./docker/mysql/Dockerfile
    volumes:
        # Prevent mysql data to be erased on restart
        - mysql_volume:/var/lib/mysql
    # Connect to "mysql_network" network, as defined below
    networks:
        - mysql_network
    # Pass a list of environment variables to the container
    environment:
        TZ: "Europe/Rome"
        MYSQL_ALLOW_EMPTY_PASSWORD: "no"
        MYSQL_ROOT_PASSWORD: "${MYSQL_ROOT_PASSWORD}"
        MYSQL_USER: "myuser"
        MYSQL_PASSWORD: "${MYSQL_PASSWORD}"
        MYSQL_DATABASE: "mybase"
        MYSQL_SECOND_PASSWORD: "${MYSQL_SECOND_PASSWORD}"
    command: [
        'mysqld',
        '--character-set-server=utf8mb4',
        '--collation-server=utf8mb4_general_ci',
        '--innodb-use-native-aio=0' # this has been added to prevent bugs mysqli::real_connect(): php_network_getaddresses: getaddrinfo failed
    ]
[...]

Finally I have env file with all the variables (properly read).

While running docker-compose up, I get no errors. I get "mybase" database, "myuser" user, but the user "myuser_bis" is missing and its database too. Also, "mybase" database is empty (as if script.sql was not read).
If I comment the line "ADD docker/mysql/init_script_database.sh /docker-entrypoint-initdb.d/init_script_database.sh" from my Dockerfile, I'll will get "mybase" database filled with the sql script I provided, and "myuser" user.

How can I make my shell script work in order to add my second user ?

Thanks in advance for your help.

(also I'm using compose-down and prune volumes each time to be sure I get a fresh start)