CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformgoogle-iamgoogle-cloud-iam
Fuyang Liu
Fuyang Liu

Reputation: 1486

Cloud SQL (postgres) cloudsqlsuperuser cannot view tables created by a cloudsqliamserviceaccount user

I am testing the CloudSQL IAM automatic authenticationby using IAM service account users. The goal is to deploy a backend service running in cloud with an service account (SA), which can connect onto a CloudSQL database without using password auth.

So this is what I did:

And this is a problem I have now:

When I use cloud-sql-proxy to login the demo-db locally via psql, with user user-db and the password, I realise that I cannot view or select the table created by the backend service (via user-sa).

Then how can I view the data in the database as a developer?

PS: Fow now I don't have access to user-sa or sa's secret/key files. As it is managed by our infra. I only have ownership of demo-db and I could give access right of my db to user-sa ...

Upvotes: 2

Views: 2949

Answers (1)

ps2goat
ps2goat

Reputation: 8485

I had the same issue on AWS's Postgres RDS.

You (as the backend creation service) basically have to create a role, place your deployment role (user-sa) into that role and also your app user (user-db).

Then your deployment script will have to use ALTER <OBJECT> OWNER TO <ROLE>. Now every role or user in that role will have access. You'll have to do this for functions, tables, etc

Another alternative is to set default permissions via ALTER DEFAULT PRIVILEGES, but note that those only take effect on new objects. If you add a new db role afterward and want to grant it permissions to a table that already existed, you'd still have to add explicit permissions for the new role.

* Note that in postgres, a role and user are interchangeable in commands. In my view, a role does not log in, but it can be used to hold a set of permissions. Instead of assigning permissions to individual users, assign them to a group/role, then grant the user membership to the group/role. Ensure that your inheriting permissions is allowed on the users and roles for this to work.

Upvotes: 0

Related Questions