MySQL MultiUser lambda cannot rotate users with host different than default '%'

ivan-georgiev commented 1 year ago

MySQL MultiUser lambda assumes that user host is '%'. It is true if user is created as follows:

If user is created with custom host, rotation lambda will fail on "SHOW GRANTS FOR %s" query. Example how to create such user:

Possible solutions:

Select all hosts for user provided by secret's username property and update password for each of them. Query must be something like "SELECT DISTINCT host FROM mysql.user WHERE User = %s". Typically scenario where someone creates single USER with multiple HOST value and different passwords is not expected , because this might cause issues on overlaps like '10.%.%.%' and '10.10.%.%'. This implementation is probably too complicated, unless in case different Host values are used as additional security layer and password is the same for database users with same User value.
Secret's metadata is extended with optional "hostname" property with default value of '%'. This seems fine, but the code is making check if current credentials are valid, so this value can be taken dynamically without complicating the structure of the secret. In both cases there is a limitation that rotation lambda must be in compatible subnet - if user is created as '10.10.%.%' and lambda runs from '10.20.x.x' function will fail.
Hostname is taken dynamically on step where current application credentials are validated, using the existing connection object. Query "SELECT CURRENT_USER()" will return application user in format username>@<hostname like 'appuser'@'10.%.%.%'.

ivan-georgiev commented 1 year ago

rstevens011 commented 1 year ago

Thank you for opening this issue - we are looking into it.

ivan-georgiev commented 1 year ago

Example implementation to cover single mysql.user.User value, multiple mysql.user.Host values and same password for all:

aws-samples / aws-secrets-manager-rotation-lambdas