GCP

Ways to implement RBAC in GCP Cloud SQL.

• GCP Cloud SQL RBAC is not as straightforward as in services like IAM for other GCP resources.
• However we can implement RBAC using a combination of IAM roles + DB level permission.

🔹 RBAC using GCP IAM

1. GCP IAM Roles (Instance-level access). a) controls -> instance access & and what actions they can perform.

| Roles | Permissions |
| ---------------- | -------------------------------------------------- |
| Cloud SQL Admin | Full control over instances |
| Cloud SQL Editor | Modify instances |
| Cloud SQL Viewer | View instances only |
| Cloud SQL Client | Connect to instances (used for applications/users) |

1. Allow developers (listed users) or services to connect to Cloud SQL but not administer it. a) we can use predefined permissions to the developer for specific roles for EX: -

cloudbuild.builds.create
cloudfunctions.functions.list
cloudnotifications.activities.list
cloudsql.backupRuns.list
cloudsql.databases.list
cloudsql.instances.addServerCa

🔹 DB level RBAC (postgresQL-native RBAC)

1. Inside PostgreSQL we can create roles, users, and assign permissions directly.

| Users | Role | Permissions |
| ----- | -------------- | ------------------------------ |
| admin | ALL | ALL PRIVILEGES |
| user1 | READ ONLY | SELECT |
| user2 | READ + WRITE | SELECT, INSERT |
| user3 | FULL ACCESS | SELECT, INSERT, UPDATE, DELETE |
| user4 | READ + UPDATE | SELECT, UPDATE |
| user5 | WRITE + DELETE | INSERT, DELETE |

a) step 1 : Create users

   CREATE USER user1 WITH PASSWORD 'password1';
   CREATE USER user2 WITH PASSWORD 'password2';
   CREATE USER user3 WITH PASSWORD 'password3';
   CREATE USER user4 WITH PASSWORD 'password4';
   CREATE USER user5 WITH PASSWORD 'password5';

b) step 2 : Grant access

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
    GRANT SELECT ON products, orders TO user1;
    GRANT SELECT, INSERT ON products, orders TO user2;
    GRANT SELECT, INSERT, UPDATE, DELETE ON products, orders TO user3;
    GRANT SELECT, UPDATE ON products, orders TO user4;
    GRANT INSERT, DELETE ON products, orders TO user5;

c) check permissions

       SELECT
       grantee AS user,
       table_schema,
       table_name,
       privilege_type AS permission
     FROM
       information_schema.role_table_grants
     WHERE
       grantee IN ('admin', 'user1', 'user2', 'user3', 'user4', 'user5')
     ORDER BY
       grantee, table_name, privilege_type;

d) revoke permissions

        REVOKE SELECT ON products FROM user1;
        REVOKE ALL PRIVILEGES ON orders FROM user3;
        REVOKE INSERT, DELETE ON orders FROM user5;

🔹 SUMMARY

• GCP IAM roles for Cloud SQL instance access management.
• Database-native roles and permissions for fine-grained data control.
• Security best practices like least privilege, encryption, logging, and automation.