How about this:
* Have one master schema that holds all physical tables. This schema
is accessible only by the superuser.
* Create a schema which contains views that mirror the master schema.
This is the schema that the customers connect to, each using a
different db role, and since it's a mirror of the master schema, it
means no change in app structure (except dropping rights management,
see below).
* Let these views pull their data from the respective master schema
table (i.e. SELECT * FROM ...) with the addition of a WHERE-clause on
client_id, that uses a function: ...WHERE client_id IN (get_client_ids
()).
* The 'get_client_ids()'-function should query a table in the master
schema that keeps the client_id's that are assigned to each db role
(e.g. SELECT client_id FROM foo WHERE role=CURRENT_USER), and return
those client_id's. For a regular customer, it would return one
client_id, for a supervisor kind of user, it would return two or
more, perhaps even all, client_id's.
* Have UPDATE and INSERT rules on the views that store the data in
the actual master schema tables. (The rules would of course have to
add client_id, this time through a function that can only return one
client_id.)
To conclude: one master schema, one mirrored customer schema that
adapts to the db role, one additional table in the master schema to
handle the rights.
Sincerely,
Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90
