Document database user permissions for "Production Deployment: Storing your Metabase application data in MySQL"

[andygrunwald]

Is your feature request related to a problem? Please describe.

In the documentation section "Configuring the Metabase application database" > "MySQL or MariaDB" it is described on how to configure Metabase for a production deployment when you aim to store the Metabase data in a database.

Special requirements for the database (like collation) are also described.

For security purposes, I am to

1. Create an own database user for Metabase
2. Limit this user to the metabase database only
3. Trim down the required permission scheme to the minimum for this database user

Describe the solution you'd like

A list of MySQL permissions the database user needs to operate in an expected way.

Lets say we create a new database metabase_storage:

CREATE DATABASE metabase_storage CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Now we create a new user metabase_storage_user.

We limit the permission of the MySQL user metabase_storage_user to

1. the metabase_storage database
2. the ALL privilege

Especially part 2, the ALL privilege can be dangerous, because (I assume) Metabase don't need that much permission.

My goal: Limit the user metabase_storage_user to the permission it needs.

A summary of available MySQL privileges is available here: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#privileges-provided-summary

Describe alternatives you've considered

None so far

How important is this feature to you?

Pretty high in a context of a production deployment.

Additional context

One Side Effect: If a future version of Metabase requires more/less permission, it would be beneficial to mention this in the Changelog. Otherwise, the users can run into errors.

[flamber]

You can remove any context related "Server administration" and "Stored routines", but specifically these are needed: ALTER, CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, UPDATE (unsure, could be needed in some cases, so I would add those too CREATE TEMPORARY TABLES, LOCK TABLES) You could potentially run with less privileges (insert, select, update, delete), but during some version upgrades you might encounter errors because lack of privileges if there's schema changes. The errors should make it fairly obvious what the problem is.

Related to #9292 and #8560 and #7681

[andygrunwald]

Thanks @flamber. I will go with the listed ones.

Looking forward to get this properly documented. PS: Thanks for the good work.

[tommimarkus]

Same issue with PostgreSQL documentation. Permissions required from the user used to connect to application database are not documented. Some migrations also seem to require superuser (installing extensions) All of this sould be documented properly.

[paoliniluis]

@tommimarkus can you give us more details about when you needed to install extensions? Metabase does not need any extension in any database