On Fri, 2006-09-29 at 09:39 -0500, Erik Jones wrote: > What we've done is to use table inheritance. So, > we have a group of base account data tables and whenever a new account > is added they get a set of tables that inherit from these base tables. > This works well in that whenever we need a global schema change to any > of these tables we can just alter that pertinent base table and the > change will cascade down to the child tables. Many DDL commands do not propagate to the child tables -- foreign keys, for example, are not inherited. As a consequence, using inheritance for Chris' purpose has the important caveat that referential integrity will be difficult to ensure with standard FK constraints. I believe that other types of constraints and rules are also not propagated to child tables. Erik- do you have a clever solution to this aspect of using inheritance? On Thu, 2006-09-28 at 22:59 -0700, snacktime wrote: > The connections to the database are from an unprivileged user, and > everything goes through functions that run at the necessary > privileges. Someone out there must have implemented row-level security for postgresql (EnterpriseDB?). You could implement this yourself by adding a usesysid column to each table and using a combination of column defaults, views, triggers, or rules to set usesysid on insert and update and to require that the usesysid column matches the current user on select and delete. This probably improves the consistency of your security policy (over having the policy in functions). On the other hand, I could be blowing smoke -- it seems like this outta work, but I haven't actually done this myself. I don't actually do any real work myself. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
