Postgresql – Disallowing table creation in the public schema

postgresql

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 (it does, when I try to run this as the luser user)…

Best Answer

This will probably do what you want:

revoke create on schema public¹ from public²;

Note that the public² does not mean the schema public¹, but a group representation of all users of the database. So when you try to revoke your user from creating tables nothing happens because it probably does not have this permissions at start(except if you give it), but the group public² that your user belongs, is with this access by default - That's the default behavior for schema public.

Now you can give the create privilege for specific users:

grant create on schema public to specificuser;
Related Question