User rights missing for MariaDB user (in the INSTALL documentation)
Describe the bug
I followed the documentation when installing the latest castopod release (alpha 69) on a fresh Debian VM with Apache / MariaDB.
The INSTALL documentation for mariadb states: "User must have at least these privileges on the database for Castopod Host to work: ALTER
, DELETE
, EXECUTE
, INDEX
, INSERT
, SELECT
, UPDATE
"
I created a database user for castopod with these permissions on the castopod database (and completed all other steps of the installation). Here are my bash commands for creating the database / database user:
$ sudo mysql -u root -p
MariaDB [(none)]> CREATE DATABASE castopod_db;
MariaDB [(none)]> CREATE USER 'castopod_user'@localhost IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALTER, DELETE, EXECUTE, INDEX, INSERT, SELECT, UPDATE ON castopod_db.* TO 'castopod_user'@localhost;
MariaDB [(none)]> FLUSH PRIVILEGES;
Expected behavior
The install process completes without error.
Actual behavior
When completing the installation from https://domain/cp-install I end up with an app error in my web browser: "We seem to have hit a snag. Please try again later."
When investigating Castopod logs, I see the following error:
ERROR - 2021-08-31 08:39:12 --> CREATE command denied to user 'castopod_user'@'localhost' for table 'cp_migrations'
ERROR - 2021-08-31 08:39:12 --> Table 'castopod_db.cp_migrations' doesn't exist
ERROR - 2021-08-31 08:39:12 --> Table 'castopod_db.cp_migrations' doesn't exist
Context
- Castopod: v1.0.0-alpha.69
- OS: Debian 11
- Browser: Firefox
- Web server: Apache 2.4.48
- Database : MariaDB 10.5.11
Possible fixes
Adding the CREATE permission apparently fixes the issue (could be added in the documentation).
$ sudo mysql -u root -p
MariaDB [(none)]> CREATE DATABASE castopod_db;
MariaDB [(none)]> CREATE USER 'castopod_user'@localhost IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT CREATE, ALTER, DELETE, EXECUTE, INDEX, INSERT, SELECT, UPDATE ON castopod_db.* TO 'castopod_user'@localhost;
MariaDB [(none)]> FLUSH PRIVILEGES;