Mysql: 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!