Creating Dynamic user inside the Dockerfile - MySQL

Question

I am trying to solve a problem with Docker and MySQL.

I have Docker file:

FROM mysql:5.6

ADD setup.sql /docker-entrypoint-initdb.d

RUN apt-get -qq update && apt-get install -y expect

RUN echo "test" | unbuffer -p mysql_config_editor set --login-path=mydb_db_test --host=mydb-mysql --user=test --password

and also the appropriate SQL file:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE SCHEMA IF NOT EXISTS `mydb_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE USER 'dev'@'%' IDENTIFIED BY 'dev';
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';

FLUSH PRIVILEGES;

GRANT ALL ON mydb.* TO 'dev'@'%';
GRANT ALL ON mydb_test.* TO 'test'@'%';

FLUSH PRIVILEGES;

I am using Node.js and db-migrate module. problem is when I run the tests, I cant connect to the MySQL instance. Always the same issue. It seems that it uses various logins, and fails as they do not exist: Sometimes it uses the:

ERROR 1045 (28000): Access denied for user '_mysql'@'172.20.0.1' (using password: NO)

Sometimes:

ERROR 1045 (28000): Access denied for user '_spotligth'@'172.20.0.1' (using password: NO)

And mostly my Mac user name (main login):

ERROR 1045 (28000): Access denied for user 'XXXXX'@'172.20.0.1' (using password: NO)

Those users that I have set up in setup.sql, are never used at all. Also, mysql_config_editor statement seems to have no effect at all.

It will work if I add (Manually) one for example my loginname as a user. For the time being I do not have problem with that, as I am building dev environment.

So I planned to make dynamic user creation, something along these lines (Please read the comments in the Dockerfile):

FROM mysql:5.6

RUN currentLocalUserCredentials=$(whoami)

RUN echo currentLocalUserCredentials
# So I have my user name in the variable

ADD setup.sql /docker-entrypoint-initdb.d
#How do I pass it to setup.sql???

RUN apt-get -qq update && apt-get install -y expect

RUN echo "test" | unbuffer -p mysql_config_editor set --login-path=mydb_db_test --host=mydb-mysql --user=test --password

So I could do this:

 CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE SCHEMA IF NOT EXISTS `mydb_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
userLoginName = received from the Dockerfile    

CREATE USER 'dev'@'%' IDENTIFIED BY 'dev';
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
CREATE USER userLoginName@'%' IDENTIFIED BY 'test';
CREATE USER userLoginName@'localhost' IDENTIFIED BY 'test';

FLUSH PRIVILEGES;

GRANT ALL ON mydb.* TO 'dev'@'%';
GRANT ALL ON mydb_test.* TO userLoginName@'%';

FLUSH PRIVILEGES;

You should grant each user its own sql file to import, this way things stay transparent. Reducing code redundancy is not always good - in this case, it's actually bad. Don't try to add users dynamically with this kind of import. — Daniel W., Jun 25 '18 at 16:58
Usually you have numbered SQL files with each version of things. like: `001-task-1234-basic-structure.sql`, `002-task-9999-altered-users-column.sql`, `003-task-1111-added-user-dev.sql`,... — Daniel W., Jun 25 '18 at 17:00

score 1 · Accepted Answer · answered Jun 26 '18 at 08:17

Docker is used in a DevOps manner where, the objective there regarding infrastructure is, that, infrastructure as code should be deterministic and repeatable.

That means, when you introduce an SQL change, you append it as a new step. You do not change existing SQL files. So the whole point of creating your users dynamically is far from best practice.

As your user from whoami is deterministic (=you know the username before you setup the container), there is no need to add it somehow dynamically.

When you introduce a new user in your container, you create a new SQL file to import. The best is to number the files and import one after the other.

Example:

001-2017-10-21-basic-schema-added.sql
002-2017-10-29-added-dev-user.sql
003-2018-01-01-altered-import-table.sql
004-2018-01-09-added-temp-table.sql
...

So every step you take to get to your resulting schema is reproducible and repeatable.

Creating Dynamic user inside the Dockerfile - MySQL

1 Answers1