[ic] Hierarchical catalogs

[Rick Bragg]

On Mon, 2010-08-16 at 17:38 -0700, Chris Keane wrote:
> 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.
> 
> Chris.
> 
> 
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
> 


Hi Chris,

I have done something somewhat similar, just a note, I chose to use the
"affiliate" db for these logins, saving userdb for other things...
Another difference is that I am doing it all under 1 catalog instead of
many.  This way, the userdb can stay as is for checkouts ecommerce,
whatever, and each affiliate can log in and basically have their own
world of content editing, calendars, products, etc...

I am interested in hearing other's comments as well.

Rick