Postgres version: 9.3.16
Let's say we have two users, luser
and editor
. I'd like to make it so that luser
(or any other non-superusers) cannot create any tables under the public schema, except editor
. When I apply the following as the postgres
user, I achieve this:
postgres=> select current_user;
current_user
--------------
postgres
(1 row)
postgres=# REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# GRANT ALL ON SCHEMA public TO editor WITH GRANT OPTION;
GRANT
postgres=# SET ROLE luser;
SET
postgres=> create table public.test (uid integer);
ERROR: permission denied for schema public
postgres=> SET ROLE editor;
SET
postgres=> create table public.test (uid integer);
CREATE TABLE
postgres=> \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+--------+---------+-------------
public | test | table | editor | 0 bytes |
(1 row)
However, when if I try to run REVOKE ALL ON SCHEMA public FROM PUBLIC;
as the editor
user, this does not work (i.e. luser
can create a table under the public schema):
postgres=> SET ROLE postgres;
SET
postgres=# GRANT ALL ON SCHEMA public TO editor WITH GRANT OPTION;
GRANT
postgres=# SET ROLE editor;
SET
postgres=> REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# SET ROLE luser;
SET
postgres=> create table public.test (uid integer); # I expect this to fail with `ERROR: permission denied for schema public` but it works somehow!
CREATE TABLE
postgres=> \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+---------+-------------
public | test | table | luser | 0 bytes |
(1 row)
So, even though I can run REVOKE ALL ON SCHEMA public FROM PUBLIC;
as the editor
and it doesn't give any errors, luser
can still create a table under the public schema. How can this happen?
Prior research:
From how do you revoke create table from a user on postgresql 9.4? :
Also note the user/role executing the revoke also needs to be the
owner of the schema (so you can check that as well).
Maybe this is the reason? But I'd still expect Postgres to give some kind of error when running REVOKE ALL ON SCHEMA public FROM PUBLIC;
as the editor
...