Backups cannot be restored on AWS RDS - View creation problem. Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this

Question

The environment is MariaDB 10.6.8, via Amazon RDS. We have all 'admin' rights but not SUPER on AWS databases.

I have a view in an application that has it's data stored in a single schema. We use non-root/admin accounts to access that data. I get an error restoring the backup from AWS:

ERROR 1227 (42000) at line 53347: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

A logged on user (programatically), not the 'root' / admin account, created a view:

DROP TABLE IF EXISTS `v_approvals`;
DROP VIEW IF EXISTS `v_approvals`;
CREATE VIEW `v_approvals` AS WITH url_bases(`reference_type`,`base_url`) AS ...

However, when that SQL file is backed up, and the CI/CD routines try to restore it, we get the above error.

This is what is actually in the backup file:

/*!50001 DROP TABLE IF EXISTS `v_approvals`*/;
/*!50001 DROP VIEW IF EXISTS `v_approvals`*/;
...
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`our_app`@`172.15.3.81` SQL SECURITY DEFINER */
/*!50001 VIEW `v_approvals` AS with url_bases(`reference_type`,`base_url`)

The IP that goes with the user is the production IP. We use the same user, but obviously a different IP on the other instances.

So the RDS error makes sense, you'd need SUPER to create that view definition, but it's not needed! We just need a view in the same schema as all of the tables it references.

Why does the mysqldump backup insert the DEFINER row? How do I get the SQL backup to NOT do that?

The backup command is:

mysqldump  -h rdshost.casdfas.us-west-1.rds.amazonaws.com \
           -u replicator --password=${{ secrets.PROD_DB_REPLICATOR_PW }} \
           --databases our_app > PROD_2_SANDBOX.sql

Where "replicator" is an account that has read, but no create authority on the production database.

All tables and data are exported/created fine. This app doesn't use views, so this is the first time we've stumbled on the issue.

I could use sed/awk to remove that line, but it seems weird to have to pre-process a backup file.

Answer 1

With MariaDB, one bone headed way to do it indeed is by processing the dump file.

First, export it as normal:

mysqldump  -h rdshost.casdfas.us-west-1.rds.amazonaws.com \
           -u replicator --password=${{ secrets.PROD_DB_REPLICATOR_PW }} \
           --databases our_app > PROD_2_SANDBOX_in.sql

Then feed that through sed:

sed '/DEFINER=/d' PROD_2_SANDBOX_in.sql > PROD_2_SANDBOX.sql

Then use the SQL file to restore the database as normal.

Note you can optimize this by piping to sed then dumping the SQL. (I initially created it the other way to make sure/debug that the right statement was stripped out).

This is not a great solution, as it may double the disk space, and requires the time to do the processing. It does, however, work.

I find it hard to believe that MariaDB views can't be restored from backups on AWS. There must be an easier way.