gcp CloudSql - postgres user per DB limitation

[ciolake]

Hi,

I have GCP CloudSQL postgres instance with databases DB1, DB2  and users user1 , user2.

I want to setup limitation for user1 to access only DB1 and user2 to access only DB2. Is it possible to do that?

[yrhsk]

A few recommendations on optimization (basically, start from low params and increase gradually to determine the best specs for your app)

1) Set Cloud run concurrency at low point - let's say, 10.
2) The same as the previous one for CPU and RAM - start with 1 CPU and 0.5 GiB of RAM.
3) For the Gunicorn settings: "2 * CPU + 1" formula to set Gunicorn workers - 3 in case of starting with 1 core. Start 2 Gunicorn threads for 1 CPU core and set timeout to 30 seconds.

[yrhsk · Accepted solution]

You shall connect to Cloud SQL (use `gcloud sql connect <params>'), then run:

REVOKE ALL PRIVILEGES ON DATABASE DB1 FROM user2;
REVOKE ALL PRIVILEGES ON DATABASE DB2 FROM user1;

GRANT ALL PRIVILEGES ON DATABASE DB1 TO user1;
GRANT ALL PRIVILEGES ON DATABASE DB2 TO user2;

(considering that you have only two users and two DBs)

[ciolake]

My postgres is having only private IP, so is there anyway to connect to SQL and change the privileges?  

[yrhsk]

You can use either Compute Instance (VM) in the same VPC as the Cloud SQL instance, or install the GCP Cloud SQL Auth Proxy: https://cloud.google.com/sql/docs/postgres/sql-proxy, and then connect using psql CLI