0

So far I have a user called test_user as shown in the image below, this user owns test_db:

enter image description here

I want to revoke the user's privileges from another database I have, let's call it main_db.

Revoke all on database main_db from test_user; why connected as a main_user, who is a member of {cloudsqlsuperuser}.

Essentially I just want main_user to only have access to main_db and test_user to only have access to test_db.

So far I ran revoke privilege all on database gitlabhq_production from test_user.

But it looks like I am still able to connect directly to the database main_db with test_user when I run psql -U test_user -d main_db -h 127.0.0.1 -W. I expect this to through an error saying user cannot connect, I have also revoked all privileges of main_user to test_db but looks like I am still able to insert a row into test_table which is in test_db.

P.S I am using GCP Cloud SQL instance with Postgres 13.

How do I achieve this ?

UPDATE

Info related to the main_db, test_db and main_user and _test_user

                                                               List of roles
         Role name         |                   Attributes                   |                          Member of                           
---------------------------+------------------------------------------------+--------------------------------------------------------------
 main_user                 | Create role, Create DB                         | {cloudsqlsuperuser}
 test_user                 |                                                | {}
 

                                                   List of databases
        Name         |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges            
---------------------+-------------------+----------+------------+------------+-----------------------------------------
 main_db             | main_user         | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/main_user                          +
                     |                   |          |            |            | main_user=CTc/main_user
 test_db             | test_user         | UTF8     | en_US.UTF8 | en_US.UTF8 | test_user=CTc/test_user
Vicky
  • 134
  • 9
floormind
  • 1,868
  • 5
  • 31
  • 85
  • Are you able to run any actual queries as the user? i.e. list the tables? – Ruslan Nov 30 '21 at 13:39
  • i am able to run insert into a table in test_db as main_user ... i dont want this to happen... i should only be able to run that insert as a test_user. isnt that part of an actual query ? correct me if i'm wrong – floormind Nov 30 '21 at 13:42

1 Answers1

3

All you have to to is to revoke the privileges from PUBLIC:

REVOKE ALL ON DATABASE main_db FROM PUBLIC;
REVOKE ALL ON DATABASE test_db FROM PUBLIC;

Then only the owners of the databases can connect to them.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi Thanks for your reply, i ran REVOKE ALL ON DATABASE test_db FROM PUBLIC; only... however main_user who owns main_db can still insert/select on a table in test_db who is owned by test_user – floormind Nov 30 '21 at 16:11
  • Not if the permissions on the database were as you showed in your question. – Laurenz Albe Nov 30 '21 at 16:20
  • yes the persmission for test_db is as i've shown in the example, could it be that it's because main_user is a member of {cloudsqlsuperuser} ? – floormind Nov 30 '21 at 16:27
  • Possible. Hard to say, since your question does not show the roles and memberships. – Laurenz Albe Nov 30 '21 at 16:31
  • Hey i have pasted an update for the databases and roles for both users and dbs, – floormind Nov 30 '21 at 16:40
  • Thanks. Then there must really be some special magic about `cloudsqlsuperuser`. Since this is a closed source fork of PostgreSQL, I cannot say anything about that. Perhaps remove `main_user` from `cloudsqlsuperuser`. – Laurenz Albe Nov 30 '21 at 16:50
  • yes so removing that membership seem to work, i cant even connect to test_db with main_user anymore. – floormind Nov 30 '21 at 17:35