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?
\dn+
and the setting ofsearch_path
.public
group permissions:REVOKE CREATE ON SCHEMA public FROM PUBLIC;
as well