MySQL Docker User Permissions

TODO: collapasable, copyable, download, etc

I have mysql setup in a Dockerfile like ```FROM mysql:8.0.34

Copy the table schema/ddl to the mysql initdb

COPY --chown=mysql:mysql ./init.sql /docker-entrypoint-initdb.d/

TODO(syscl): change it to use configMap

ENV MYSQL_ROOT_PASSWORD="rootpassword" \ MYSQL_USER="troubleshooter"
MYSQL_PASSWORD="fl4uVU#3"

EXPOSE 3306

CMD ["mysqld"]```

The contents of init.sql are ```-- Set utf8 settings globally SET collation_server = utf8_unicode_ci; SET character_set_server = utf8;

CREATE DATABASE IF NOT EXISTS appdata DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Needed for dev, staging, and production GRANT ALL PRIVILEGES ON appdata.* TO 'deductive'@'%';

-- The following is needed only in dev (docker and the developer workflow) GRANT ALL PRIVILEGES ON appdata_shadow.* TO 'deductive'@'%';

FLUSH PRIVILEGES;

When it runs, the docker logs show ```2023-12-22 23:45:14+00:00 [Note] [Entrypoint]: Creating user troubleshooter
2023-12-22 23:45:14+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
ERROR 1410 (42000) at line 8: You are not allowed to create a user with GRANT```

What is wrong with my setup?The error message you're encountering, `ERROR 1410 (42000) at line 8: You are not allowed to create a user with GRANT`, indicates a problem with the way user privileges are being granted in your `init.sql` script. This issue is often related to permission restrictions in MySQL, particularly in newer versions where the security model has been tightened.

In your `init.sql` script, you have the following lines:

```sql
GRANT ALL PRIVILEGES ON appdata.* TO 'deductive'@'%';
GRANT ALL PRIVILEGES ON appdata_shadow.* TO 'deductive'@'%';

These lines attempt to grant privileges to a user named 'deductive'. However, the script doesn't actually create this user; it only grants privileges. In MySQL, particularly in versions 8 and above, the GRANT statement can no longer be used to implicitly create a user. You must explicitly create the user before granting privileges.

Here are the steps to fix the issue:

1. Explicit User Creation: Before the GRANT statements, explicitly create the user. Add these lines to your init.sql:

   CREATE USER IF NOT EXISTS 'deductive'@'%' IDENTIFIED BY 'your_password';
   

   Replace 'your_password' with a secure password of your choice.

2. Grant Privileges: After creating the user, you can then grant privileges as you have in your script.

   GRANT ALL PRIVILEGES ON appdata.* TO 'deductive'@'%';
   GRANT ALL PRIVILEGES ON appdata_shadow.* TO 'deductive'@'%';
   

3. Docker Environment Variables: You've set environment variables for a MySQL user troubleshooter in your Dockerfile. Ensure this user is meant for your application's connection and not for the GRANT statements in init.sql.

4. Update Dockerfile and Script: After updating your init.sql, rebuild your Docker image to ensure the changes are applied.

5. Security Considerations: Using % as the host in user creation and grant statements allows connections from any host. This can be a security risk. In a production environment, you should restrict this to known hosts or networks.

6. Time Zone Warnings: The warnings about the time zone files are usually not critical, but if you need accurate time zone support in MySQL, you might want to look into configuring the time zone data in your Docker setup.

After making these changes, your setup should work as expected, and the error related to user creation with GRANT should be resolved.

MySQL Docker User Permissions created at Tue Jan 20 1970 17:08:08 GMT+0000 (Coordinated Universal Time)