How to grant a SELECT permission for a new GCP Cloud SQL PostgreSQL user?

Question

I need to read from an existing GCP Cloud SQL Postgres database. I created a new user using a GCP console. When I log in as that user I get "access denied" whenever I attempt to SELECT from any table. I tried all kinds of ALLOW and GRANT with no luck.

The most bizarre behavior is "database not found" response when I run GRANT ALL PRIVILEGES ON DATABASE. It says "database doesn't exist" despite it most definitely existing.

What commands need to be executed on a user created via GCP console so that the user can SELECT from the tables?

Thanks

Answer 1

There are two aspects in this question, before exploring these, it is important to understand the differences between IAM and SQL users.

IAM users, when created, do not have any inherent right to read data inside the database. Permission to the database and tables must be explicitly granted to the IAM user from within Postgres using the 'grant' command

Creating a user with Postgres internal commands, the level of access is determined by the role it is associated with as mentioned in link1, link2. In this specific case, you should use an account that does have permission to grant the IAM role the appropriate permissions. As you do not have the postgresql password, you will have to reset it like in [5]

While it was already stated this is in production, there is no other way. Only an existing user with appropriate permissions can update the database to change permissions as this is done within the Database itself.

[5] gcloud sql users set-password postgres --instance=<DBNAME> --prompt-for-password

Answer 2

This is because you should specify the database with the flag --database.

For example:

 gcloud sql connect myinstance --user=myuser --database=mydb

Answer 3

when you create your Postgresql instance in your Gcp through cloud console and the you add an user to your instance and when you try connecting to your instance from the user you created you are getting "database doesn't exists" error .

This happens because

When we try connecting to the instance using gcloud sql connect --user , the issue might be this command only works with default user i.e Postgres. you can check the documentation also,database Error

The workaround is to connect to your default postgres user and from there use "\c" psql command to recoonect as different user.

PostgreSQL has always to connect to a database, mysql can just "connect to the server"

you always have to connect to a database , you can't connect "to the cluster / to the server"

steps:

1. gcloud sql connect [instancename] --user=postgres --quiet

2. provide password

3. postgres=> terminal opens

4. type \c i.e

   postgres=>\c [databasename][username]
   
   example-
   
       postgres=> \c testdata user1
   

5. user changes to the user you have created

6. create your table in database and query it like -

   select * from table1;