PMM user creation instructions for MySQL 8 on Amazon RDS don't work and are harmful

[fredericgboutin-yapla]

When reading https://docs.percona.com/percona-monitoring-and-management/setting-up/client/aws.html#setting-up-the-amazon-rds-db-instance you see,

CREATE USER 'pmm'@'%' IDENTIFIED BY 'pass';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'pmm'@'%';
ALTER USER 'pmm'@'%' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'%';

The problem is, running this script on an AWS RDS MySQL 8 database "aggressively" doesn't work. It closed my client connection after complaining about an invalid GRANT. It then blocked the replication process of our replica, failing on the GRANT instructions (Context: we are migrating our Primary from 5.7 to 8.0.x so our 8.0.x replica failed quite hard).

After a chat with GPT, it explained that in MySQL 8 the default authentication method changed and that running this script instead would work,

-- Create user with caching_sha2_password authentication plugin
CREATE USER 'pmm'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'pass';

-- Grant necessary privileges using the new syntax
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'pmm'@'%';
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'%';

-- Set maximum user connections
ALTER USER 'pmm'@'%' WITH MAX_USER_CONNECTIONS 10;

I tried it and indeed it seems to work.

I'm not expert here on the why and how; we should probably use the mysql_native_password plugin instead, I don't know. But I know that the current instructions in the documentation were very harmful for us.

Thanks !

[fredericgboutin-yapla]

BTW, (God damn is anybody here or what?)

I began to get errors in my providers. I could not see any query in QAN. I dug into PMM log files only to see

time="2024-02-14T21:43:29.382+00:00" level=info msg="Action started." component=runner id=/action_id/6a4103d9-6ad2-4581-aa43-26bb68fe7f8e type=mysql-query-show
time="2024-02-14T21:43:29.386+00:00" level=warning msg="Action terminated with error: Error 1226 (42000): User 'pmm' has exceeded the 'max_connections_per_hour' resource

And there you have a solution - https://forums.percona.com/t/gaps-in-mysql-metrics-due-to-max-connections-per-hour/13209 - that should have been documented.

So the script in the documentation should also including something like,

ALTER USER 'pmm'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0;