This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

Managing Cloud SQL database / schema access as code

Posted 4 weeks ago
djfinnoy
Bronze 1
Bronze 1
Reply posted on --/--/---- --:-- AM

Hello,

We have a mature developer platform where everything is defined as code, and deployed through automated pipelines. We are about to adopt Postgres, and it seems like defining granular IAM permissions as code is only possible to do at the instance level, not at the database / schema level.

We would like to minimize the amount of instances that we employ due to costs, but now we are struggling to find ways to grant service accounts POLP privileges to databases within an instance.

Does anyone have a good solution for this? At the moment the best idea we have come up with is to use a tool called flyway to implement the roles via CI. For us, the ideal solution would be for configconnector / terraform to support access management at the database level, not just at the instance level.

1 1 100
Topic Labels
1 REPLY 1

Posted on --/--/---- --:-- AM
ms4446
Staff
Reply posted on --/--/---- --:-- AM

Managing granular IAM permissions in Google Cloud SQL, specifically for PostgreSQL, can be challenging due to Cloud SQL's current IAM capabilities being limited to instance-level permissions. However, several strategies and tools can help you achieve the principle of least privilege (POLP) within your databases while maintaining a code-defined, automated deployment process.

  1. SQL Roles and Grants:

    • Manual SQL Scripts: Use SQL scripts to define roles and privileges directly within PostgreSQL. This approach ensures direct control over permissions but can become complex as configurations grow.
    • Flyway: Implement Flyway or similar database migration tools to manage and version-control your database schema changes, including roles and permissions. This method enhances scalability and maintainability.

  2. External Tools:

    • Terraform: While primarily used for provisioning Cloud SQL instances, Terraform can integrate with tools like Flyway to manage database-level permissions via provisioners after the instance is set up.
    • Config Connector: Similar to Terraform, Config Connector can manage Cloud SQL instances and can be extended to execute Kubernetes jobs that apply SQL scripts for detailed role management within Kubernetes-based applications.

  3. Database User Management Services:

    • Cloud SQL Proxy: This tool facilitates secure database connections via IAM-authenticated service accounts at the instance level, complementing SQL-based access controls.
    • Service Account Management: Carefully manage service account privileges and keys to ensure that only necessary permissions are granted, enhancing security and compliance.

  4. Advanced IAM with VPC Service Controls:

    • VPC Service Controls: Implement these controls to add an extra layer of security around your Cloud SQL instances, which helps prevent data exfiltration and limits access to authorized services within your VPC.

  5. Custom Automation Scripts:

    • Custom Scripts: Develop custom automation scripts in languages like Python or Bash to apply SQL grants and role assignments post-deployment. Integrating these scripts into your CI/CD pipelines ensures consistent application of security policies.