Google Cloud Shell or Cloud SQL issue with postgres error permission denied for table Asked yesterday

Question

I'm connecting to my Cloud SQL instance/postgres db using Cloud Shell and using my CLOUD SQL User account but getting "error permission denied for table" when trying to perform SELECT.

I'm not sure if my issue is my IAM GCP account access or my Cloud SQL User account. My Cloud SQL User account has cloudsqlsuperuser root access.

When connecting to a postgres database using Cloud Shell, is the IAM account roles used as well or just the Cloud SQL User account permissions?

If I need to update roles/permissions to either account so I can do a query (SElECT) which account do I need to update and what roles/permissions; IAM or SQL User?

Answer 1

I'm assuming that when you navigate to Cloud SQL > Users (url: https://console.cloud.google.com/sql/instances/<instance-name>/users) you have multiple users listed there, at least one named postgres and the current username you're trying to use.

What happens is that when you create a new user, even though it's granted the cloudsqlsuperuser role, that does not mean it has SELECT access to already existing databases. You can read more about it here.

In order to give the current user access to that specific database, first log in with your postgres user in Cloud Shell (replace <instance-name> with the project instance name and <database-name> with the name of the database from Cloud SQL that you're using):

gcloud sql connect <instance-name> --user=postgres -d=<database-name>

Now in the new psql session open as postgres, run the GRANT command (replace <table-name> with the name of the table you intend to grant access to and replace <username> with the name of the original user you wish to grant access to):

GRANT SELECT ON "<table-name>" to "<username>";

When both steps are successful you should be able to close and open Cloud Shell again, login with your intended user and run the SELECT query.