Skip to main content
Bumped by Community user
Bumped by Community user
Bumped by Community user
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link
Tweeted twitter.com/StackDBAs/status/851434818982354944
luser can't run REVOKE
Source Link
kolistivra
  • 231
  • 3
  • 6

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)...

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...

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)...

Source Link
kolistivra
  • 231
  • 3
  • 6

Disallowing table creation in the public schema

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...