How to efficiently manage SQL permissions in AWS RDS for a CI/CD workflow?
I'm performance testing and Currently developing a CI/CD pipeline for an application hosted on AWS, I need to establish a robust SQL permission management system in RDS. While reviewing our deployment scripts, I noticed that the database user roles were not aligning with our best practices for least privilege access. My initial implementation granted broad permissions like `CREATE`, `DROP`, and `ALTER`, which doesn't seem ideal for security compliance. To tighten up security, I attempted to create a dedicated user for each microservice with more granular permissions. Here's the SQL I used to create one of the roles: ```sql CREATE ROLE service_user; GRANT SELECT, INSERT, UPDATE ON DATABASE mydb TO service_user; ``` However, I'm not entirely sure if this approach will scale appropriately as more services come online. Additionally, I realized that managing these permissions manually might slow down our deployment process. Does anyone have experience leveraging AWS IAM roles to automate the permission management for RDS? Furthermore, I've explored using AWS Secrets Manager to store database credentials securely, but integrating that with our existing deployment scripts has proven a bit challenging. Here’s a snippet of what I tried: ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": "secretsmanager:GetSecretValue", "Resource": "arn:aws:secretsmanager:us-east-1:123456789012:secret:mysecret" } ] } ``` I would appreciate any guidance on best practices for managing SQL permissions in a dynamic environment like this, especially as we want to ensure compliance and security while keeping our CI/CD pipeline efficient. Is this even possible?