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.Elphick@lfix.co.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