Search Postgresql Archives

Re: Separation of clients' data within a database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux