'revoke create on database' doesn't work as expected
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
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