Re: using schema's for data separation

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