Tagged with aws

AWS RDS and MySQL's SUPER privilege

While moving a RackTables database from a classic MySQL server to Amazon's RDS service, I found two things:

  • RackTables requires SUPER privileges on the MySQL DB to create triggers that will run as a different MySQL user than its own and
  • AWS RDS does not provide SUPER privileges.

When I tried to import the data from the old Racktables DB into the RDS DB, I got the error You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable).

AWS provides a setting to enable functions, procedures, and triggers for an instance of MySQL on Amazon RDS.

I followed the guide and created a database parameter group with the option log_bin_trust_function_creators = true for the Racktables DB.

After rerunning the import, the error changed slightly to You do not have the SUPER privilege and binary logging is enabled.

This is caused by the DEFINER statements in the DB dump:

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW

On the RDS instance, there was no root user, but a rootuser so I changed the .sql file with the dump to read

/*!50003 CREATE*/ /*!50017 DEFINER=`rootuser`@`localhost`*/ /*!50003 TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW

but the import still failed with the same error so I checked the user privileges in MySQL:

[root@racktables.example.com tmp]$ mysql -u rootuser -h racktablesrdb.example.us-east-1.rds.amazonaws.com -P 3306 -p -e "select User,Host from mysql.user;"
Enter password: 
+----------------+-----------+
| User           | Host      |
+----------------+-----------+
| racktablesuser | %         |
| rootuser       | %         |
| rdsadmin       | localhost |
+----------------+-----------+

Then I remembered that % does not include localhost in MySQL. Changing the .sql file to

/*!50003 CREATE*/ /*!50017 DEFINER=`rootuser`@`%`*/ /*!50003 TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW

allowed the import to complete successfully.

Obviously it's much safer to actually define the hostname rather than using the % wildcard.

Sources

Tagged , ,