How to grant connectivity read only access to gcp cloud sql

Question

I want to grant a user read only db access to a postgres cloud sql db on GCP (google cloud). For example, I want user to able to view db tables from postico/pg admin but not be able to delete/update/insert records.

I granted the user the following roles Cloud SQL Client (Connectivity Access to Cloud Sql Instances) Cloud SQL Viewer (Read only access to Cloud SQL Resources)

I first tried only Coud SQL Viewer but the user could not access the db from their local machine.

Cloud SQL Client (Connectivity Access to Cloud Sql Instances) - Does this give user ability to edit the db records ? If so, is there a way to have connectivity access without write permissions ?

Gabe Weiss Gabe Weiss · Accepted Answer · 2020-11-13T05:58:49

So, there's two concepts of permission on Cloud SQL. There's instance (Cloud) level access, and DB level access.

The IAM roles like you're looking at (Cloud SQL Client) deal with instance level. Can you connect to Cloud SQL, can you view all the databases in the project, etc. The ability to write/update/read records is at the DB level. For that you need to use GRANT SQL commands while connected to the instance.

Check out the GRANT documentation here: https://www.postgresql.org/docs/9.0/sql-grant.html

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

How to grant connectivity read only access to gcp cloud sql

1 Answers