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

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;

Answer 2

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

For example:

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