[ic] Hierarchical catalogs
chris.keane at zzgi.com
Tue Aug 17 00:38:07 UTC 2010
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