0

I'm using Google Cloud SQL Postgres and having difficulty revoking privileges to create tables from a user.

I first removed all roles and attributes from the user (truncated for brevity):

mydatabase=> \du+
                                                             List of roles
         Role name         |                         Attributes                         |           Member of            | Description
---------------------------+------------------------------------------------------------+--------------------------------+-------------
 cloudsqlsuperuser         | Create role, Create DB                                     | {pg_monitor,pg_signal_backend} |
 myuser                    |                                                            | {}                             |
 postgres                  | Create role, Create DB                                     | {cloudsqlsuperuser}            |

This worked for preventing the user from accessing data in existing tables, but the user was still able to create new tables. I figured this might be because of the default CREATE privilege PUBLIC has to the public schema so I tried revoking both that and any specific privileges for the user...

postgres=> REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=> REVOKE ALL ON DATABASE "mydatabase" FROM "myuser";
REVOKE
postgres=> REVOKE ALL ON SCHEMA public FROM myuser;
REVOKE

But the user still has access to CREATE tables within mydatabase:

mydatabase=> SELECT pg_catalog.has_schema_privilege('myuser', 'public', 'CREATE');
 has_schema_privilege
----------------------
 t
(1 row)

I thought maybe the issue was that the permissions were at the database level and not the schema level so I tried revoking them there as well, but no luck:

postgres=> GRANT ALL ON DATABASE "mydatabase" TO cloudsqlsuperuser;
GRANT
postgres=> GRANT ALL ON DATABASE "mydatabase" TO postgres;
GRANT
postgres=> REVOKE ALL ON DATABASE "mydatabase" FROM myuser;
REVOKE
postgres=> REVOKE ALL ON DATABASE "mydatabase" FROM public;
REVOKE
postgres=> GRANT CONNECT ON DATABASE "mydatabase" TO myuser;
GRANT

The output of \dn+ doesn't show any other permissions that should affect this user AFAIK:

postgres=> \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | cloudsqlsuperuser | cloudsqlsuperuser=UC/cloudsqlsuperuser+| standard public schema
        |                   | =U/cloudsqlsuperuser                  +|
        |                   | postgres=C/cloudsqlsuperuser           |
(1 row)

Additionally, the search_path is default:

postgres=> SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

How do I correctly remove the CREATE privilege for this user?

5
  • 1
    Please show the result of \dn+ and the setting of search_path. Commented Aug 17, 2021 at 7:22
  • @LaurenzAlbe I edited the question and added those details, thanks Commented Aug 17, 2021 at 15:31
  • Thanks. That should be possible if all connections are to the same database. But perhaps Google has modified PostgreSQL permissions. Commented Aug 17, 2021 at 18:06
  • @ashgromnies this is way too late but did you ever figure out a solution for this? Commented Mar 12, 2023 at 23:29
  • @AkashAgarwal yes, IIRC I had to revoke the default public group permissions: REVOKE CREATE ON SCHEMA public FROM PUBLIC; as well Commented May 19, 2023 at 3:23

0

Your Answer

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

Browse other questions tagged or ask your own question.