1

Owner of table x is user dave.

Only SELECT is granted to cloudsqlsuperuser, however user anton is able to update data in table x. How it's possible?

=> \du
                                                         List of roles
         Role name         |                         Attributes                         |               Member of               
---------------------------+------------------------------------------------------------+---------------------------------------
 anton                     | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqladmin             | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 cloudsqlagent             | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqliamserviceaccount | Cannot login                                               | {}
 cloudsqliamuser           | Cannot login                                               | {}
 cloudsqlimportexport      | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqlreplica           | Replication                                                | {pg_monitor}
 cloudsqlsuperuser         | Create role, Create DB                                     | {pg_monitor,pg_signal_backend}
 dave                      | Create role, Create DB                                     | {cloudsqlsuperuser,postgres}

=> \dt
                   List of relations
 Schema |           Name           | Type  |   Owner   
--------+--------------------------+-------+-----------
 public | x                        | table | dave
=> \z x
                                      Access privileges
 Schema |     Name     | Type  |       Access privileges       | Column privileges | Policies 
--------+--------------+-------+-------------------------------+-------------------+----------
 public | x            | table | dave=arwdDxt/dave            +|                   | 
        |              |       | =arwdDxt/dave                +|                   | 
        |              |       | cloudsqlsuperuser=r/dave     +|                   | 
(1 row)

I am able to do UPDATE as anton user. Shouldn't it be forbidden?

=> update x set ...
UPDATE 1
0

1 Answer 1

2

=arwdDxt/dave means all privileges are granted to the pseudo-role PUBLIC, of which anton is obviously a member. The documentation states that

any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC. Thus, for example,[...] revoking SELECT from a user might not prevent that user from using SELECT if PUBLIC or another membership role still has SELECT rights.

Subsequently, you'll have to revoke the PUBLIC UPDATE privilege to prevent anton from updating the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.