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
outof them all."                                                    Psalm 34:19