CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-sql
user11020868
user11020868

Reputation: 427

How to grant connectivity read only access to gcp cloud sql

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 ?

Upvotes: 3

Views: 4628

Answers (2)

Göky Tenek
Göky Tenek

Reputation: 1

first let's see what the user has: SHOW GRANTS FOR db_user

Let's revoke all: REVOKE ALL PRIVILEGES, GRANT OPTION FROM db_user FLUSH PRIVILEGES

Now, let's give only the needed to the user: GRANT SELECT ON db_name.* TO db_user

or you can also give: GRANT SELECT, UPDATE, INSERT, DELETE ON db_name.* TO db_user

Flush again: FLUSH PRIVILEGES

and check again: SHOW GRANTS FOR db_user

Upvotes: 0

Gabe Weiss
Gabe Weiss

Reputation: 3342

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 ]

Upvotes: 4

Related Questions