Thread: 'revoke create on database' doesn't work as expected

'revoke create on database' doesn't work as expected

From

Arnout Engelen

Date:

20 December 2002, 23:13:42

Hi,

I'm having a bit of trouble using the 'revoke on database' feature
of postgresql 7.3. I've installed postgresql for the first time 2
days ago, so excuse me if I'm doing something stupid here ;)

Suppose I want to deny the user 'arnouten' the privilege of
creating tables in the database 'leenmarkt'. Logged in as 'postgres',
I so:

leenmarkt=# revoke create on database leenmarkt from public;
REVOKE
leenmarkt=# revoke create on database leenmarkt from arnouten;
REVOKE
leenmarkt=# revoke all on database leenmarkt from arnouten;
REVOKE
leenmarkt=# revoke all on database leenmarkt from public;
REVOKE

Now the datacl-field in pg_database is set to
'{=,postgres=CT,leenmarkt=CT}' for the database 'leenmarkt'.

arnouten is not superuser:

  User name  | User ID |         Attributes
 ------------+---------+----------------------------
  arnouten   |     104 |
  leenmarkt  |     102 |
  postgres   |       1 | superuser, create database
  teamherrie |     103 |

The database is owned by postgres:

         List of databases
      Name    |  Owner   | Encoding
  ------------+----------+-----------
   leenmarkt  | postgres | UNICODE
   teamherrie | postgres | UNICODE
   template0  | postgres | SQL_ASCII
   template1  | postgres | SQL_ASCII
   test       | postgres | SQL_ASCII

but still, when I log in as 'arnouten', I can create tables
in the leenmarkt database... Anyone a clue as to what I might
be doing wrong here?


I appreciate your remarks,

Regards,

--
Arnout Engelen <pgsql@bzzt.net>

  "If it sounds good, it /is/ good."
          -- Duke Ellington

Re: 'revoke create on database' doesn't work as expected

From

Tom Lane

Date:

20 December 2002, 23:24:33

Arnout Engelen <pgsql@bzzt.net> writes:
> Suppose I want to deny the user 'arnouten' the privilege of
> creating tables in the database 'leenmarkt'.

"revoke create on database" revokes the right to create schemas, not
individual tables.  You will also need to revoke create on (at least)
the public schema.

Offhand I believe that the default privileges for databases don't grant
create to public anyway, so "revoke create on database" is a no-op
unless you previously granted that right to someone ...

            regards, tom lane