RDBMS user managment through Gitlab CI/CD

Question

I'm setting up Gitlab CI/CD, to test and deploy a project that uses micro services. The service are build in Laravel and each runs in a Dockers container of their own. Each micro service has a dedicated database (MySQL) user. The users are only given permission to their respective tables and fields in the application database.

To handle the database migrations a separate Docker container/Laravel instance is used. This instance has a separate admin user with rights on the application database, but not on the mysql database(so it can't create users).

Now that deployment starts getting nearer I was looking in to Gitlab CI/CD, so far I am only building test images. Getting the right users in MySQL proofed challenging (but I managed). But I am stuck on how to manage the users in production...

Should I give the container that handles the migrations permission on the relevant mysql database tables and write migrations that use raw SQL using DB::statement() to insert (or remove) users and set the appropriate permissions? Is there a Laravel way here, One that lets you manage RDBMS users? Or should I add the users manually (feels like giving up)>

For the local develop/test environment there is a separate repro that stored all the files needed to start it. To create the database users for all the micro services a directory with .sql files is mounted in the /docker-entrypoint-initdb.d directory of the MySQL docker container. This is automatically loaded by MySQL and used to create all those users and grant them their privileges.

Answer 1

It is not advised to handle users and permissions for production databases using migrations or simple SQL queries.

Instead, you can manage them outside of your application code using a configuration management tool like Ansible, Chef, or Puppet, or a database administration tool like MySQL Workbench or phpMyAdmin.

Users can also be added manually, however, it's necessary to think about security and limit permissions. To create database users and grant them rights in the local develop/test environment, a directory containing .sql files can be mounted in the /docker-entrypoint-initdb.d directory of the MySQL docker container.