I’m not quite sure with the issue here, I did spin up a MySQL RDS instance as a test and using the
ALTER USER 'strapi'@% IDENTIFIED WITH mysql_native_password BY '<password>';
worked perfectly both locally and in a test EC2 instance with a proper VPC setup.