Re: [ADMIN] Controlling user table creation

Peter Eisentraut wrote [re using rules to guard against unprivileged
table creation]:
>It couldn't, because the CREATE TABLE code does not go through the rule
>system.

Could it not be done by enforcing access control on system tables? At
present this is partially supported. Perversely, I can deny select
privilege to pg_class but cannot deny insert privilege:

junk=# revoke all on pg_class from public;
CHANGE
junk=# \d
List of relations
Name | Type | Owner
------------------+----------+-------
a | table | olly
...
(14 rows)
junk=# \c - ruth
You are now connected as new user ruth.
junk=> \d
ERROR: pg_class: Permission denied.
junk=> create table xx (id int);
CREATE
junk=> \c - olly
You are now connected as new user olly.
junk=# \d
List of relations
Name | Type | Owner
------------------+----------+-------
a | table | olly
...
xx | table | ruth
(15 rows)

If the denial of write privilege were enforced, it would not be possible
for an unprivileged user to create tables. When a database is created,
all the system tables should be made read only for PUBLIC. As a corollary,
when a write privilege is granted on a table, it may be necessary to
give concomitant privilege on tables needed to update sequences and other
such items (I can't think of any others, at the moment), or else by-pass
privilege checking on these.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Many are the afflictions of the righteous; but the
LORD delivereth him out of them all."
Psalm 34:19