[ic] Hierarchical catalogs

Chris Keane chris.keane at zzgi.com
Tue Aug 17 00:38:07 UTC 2010

Hey all,

We're looking at solving an interesting problem and I wanted to share it 
with you to see if anyone has a brainwave about a better way than the 
one we're currently pursuing.

We're building a system for an organization that has many subsidiaries. 
The organization wants visibility into the operation of all subsidiaries 
(or generally, the entire organization) but wants to compartmentalize 
the subsidiaries so that they can't see each  other.

Userdb is shared across all subsidiaries but only users who have 
actually logged in at a subsidiary are visible to that subsidiary. 
Similar with orders, etc.

We use Postgres.

We're currently planning to solve this using schemas, views and triggers.

Each subsidiary will be set up with their own catalog, all using a 
common database. Each catalog will be set to use a catalog-specific 
schema using DatabaseAuto, which contains a view of each table in the 
master database that basically adds condition to the select,
e.g. instead of

select blah from thing

selecting directly from a table we'll end up with a view called thing 
that implements something like (simplified)

select blah from masterdb.thing a, masterdb.permissions b
where a.key = b.key and
           b.subsidiary = 'this subsidiary' and
           b.perm = 'read';

We'll have insert, delete and update triggers on the view that similar 
check permission then apply the update to the masterdb.

Of course, all the views and triggers adds a whole new level of 
complexity that can add problems down the line, so we're looking at it 
every which way to make sure this is how we really want to do it.

Any thoughts about an easier way to accomplish compartmentalization 
while still allowing overall access?

Of course, then we need to figure out multi-master async replication! We 
love bucardo which is what we use now for 1 master/1 remote, but now 
we're talking multiple remotes for the single master... ugh.


More information about the interchange-users mailing list