Controlling user table creation - Mailing list pgsql-admin

From Samuel Greenfeld
Subject Controlling user table creation
Date
Msg-id sa74396f.040@groupwise.rowan.edu
Whole thread Raw
Responses Re: Controlling user table creation
List pgsql-admin
( I know this is slightly OT, but since I am not a highly active SQL developer, please bear with me.)


   Watching this list, I have noticed several people mutter about the fact that any user with access to a database can
createa table in it.   I do not know if this would work, but I would like to propose a possible workaround.  Use this
informationat your own risk; I do not know if this would work, cause more damage than good, etc. 

   While I am no means an SQL wizard, one of the things I know postgres supports is the ability to create rules. At
leastunder 7.0.2, it seems that rules can be applied to a system table, such as pg_tables.   Now, I don't know if SQL
rulescan do things a user normally could not do.  But given that I could create a rule on a pg_tables, one wonders if
sucha rule could be used to keep users from making tables. 

   The rule would go something like this (more pseudo-code than SQL code):

   ON INSERT pg_tables WHERE (current_user not a superuser) AND (current_user not in allowed_tablecreator_list) DO DROP
(last_tableadded to pg_tables); 

   This might not exactly work, as pg_tables might not be the last thing to know about a table being added.  This also
mightnot work if the rule is executed before pg_tables gets modified, or if rules can not modify system tables (I only
trieda "DO NOTHING").  If a rule is the first thing that learns about a table operation, DO INSTEAD NOTHING might work,
evenin the case where a user can not modify the system tables. 

   Historically, I have not used rules with any of my SQL databases, so I do not know if this would work.   But any
insightas to what can/can not be done in this area would be useful. 

   Sincerely,
   Samuel Greenfeld



pgsql-admin by date:

Previous
From: Max Pyziur
Date:
Subject: Re: RedHat RPM install errors - advice needed
Next
From: Peter Eisentraut
Date:
Subject: Re: Controlling user table creation