O Marc Munro έγραψε στις Apr 18, 2006 : > You are talking about row-level security. Different users must be able > to see different subsets of data. > > This sounds like a job for Veil: http://veil.projects.postgresql.org/ > > Veil allows you to implement row-level access controls based upon any > criteria you can define. In your case, this would probably be based > upon being a representative of a specific company or vessel. > > Taking the Veil approach you will need to modify your apps, only to the > extent that they must identify the user on connection. All connections have a user in postgresql actually. What do u mean? I see your approach is based on creating views, permissions (IIUC unix like??), schemata, etc... So it provides isolation (and subsequently security). Good work. What i am thinking of doing is exactly the same but taylored for my case without the use of generic tool. The ammount of CREATE RULES, altho scared me a little bit, both thinking performance/administration. For every table i should define the whole set of Veil views/rules etc... I am thinking of letting inserts,updates be physical without the interception of rules, since i have some heavy dbmirror triggers hanging around already, so persformance is an issue here. > > If you are interested in using Veil, I would be pleased to help. > > __ > Marc > > On Tue, 2006-04-18 at 07:24 -0300, pgsql-general-owner@xxxxxxxxxxxxxx > wrote: > > Date: Tue, 18 Apr 2006 13:44:34 +0300 (EEST) > > From: Achilleus Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> > > To: pgsql-sql@xxxxxxxxxxxxxx, <pgsql-admin@xxxxxxxxxxxxxx>, > > <pgsql-general@xxxxxxxxxxxxxx> > > Cc: pgsql-jdbc@xxxxxxxxxxxxxx > > Subject: Thoughts on a Isolation/Security problem. > > Message-ID: > > <Pine.LNX.4.44.0604181230470.24609-100000@xxxxxxxxxxxxxxxxxxxxx> > > > > > > Hi, i have run into the following problem. > > Let me describe the context first. > > > > When i joined the company(ies) i work for (a group of Shipping Mgmt/ > > Owenship/Agent companies), the only thing i was told when i started > > designing the DB/Apps was just one company. > > > > So i built everything into one single DB, and i wrote the apps > > having one company in mind. > > > > Our architecture is based on jboss3/postgresql (curenctly 7.4.12). > > There is one .ear file, which authenticates users against a lotus > > notes ldap server. > > > > At the time, the corporate organisational model was a little bit > > wierd: > > - Many Indepentent ownership companies > > - Many Independent Mgmg companies > > (but all busines was with one company in mind). > > > > Each App user is a member of one or more ldap groups, each group > > mapping to a mgmt company. > > > > So i ended up with > > - one DB with 173 tables > > - one DB user (postgres) > > - one .EAR application with 148,827 lines of code. > > > > Now the requirements start to change. > > The boss now bought some other types of vessels too. > > So virtually there must be N separate distinct apps, where N is the > > number > > of Mgmt companies (roughly one for every type of vessel), where each > > app > > sees and manages only its data. > > > > Moreover there are some apps that should see global data for some > > specific > > tables. (like the crew data, people in the crew move from one type of > > vessel to the other so they are not tied to a Mgmt company). > > > > These new requirements are of legal nature, as well as of > > operational. (People managing a type of vessels dont want to mess > > with > > another type, > > and auditors must see each company completely separated from the > > rest). > > > > Doing it with extra code would be a real pain, since i would have to > > refine > > all security/authentication based on the groups > > (groups@mgmt_companies) > > that a person belongs to. Also this way no inherent isolation/security > > would hold. > > > > Now i am thinking of restructuring the whole architecture as: > > - Create one EAR app for every mgmt company > > - Create one DB USER for every mgmg company > > - Create one SCHEMA (same as the USER) for every mgmt company > > (mgmtcompany1,mgmtcompany2,etc...) > > - Find a way (links/xdoclet/eclipse?) to have *one* common code base > > for > > the N EAR apps. > > - Tweak with jboss*.xml to map java:comp/env/jdbc/<mgmt company>DB to > > java:/<mgmt company>pgsql, where <mgmt company>pgsql authenticates > > with the corresponding DB USER. > > - Classify the tables into > > - The ones that apply to ALL mgmt companies (and leave them in the > > public schema) > > - The ones that apply *only* to a mgmt company and so create one > > under > > each SCHEMA > > - Load the data in *each* SCHEMA, except the tables that apply to all. > > - Define a process of "mgmt company"fying the tables in each schema > > (e.g. > > delete from mgmtcompany1.vessels the vessels that dont belong to > > mgmtcompany1, and so forth) > > - Resolve FK constraint issues > > - The default search_path in psql (whats the the equivalent in jdbc?) > > is > > $user,public, so effectively *each* EAR will hit automagically the > > correct > > mgmtcompanyN.* tables, or the public.* tables if these tables apply to > > all > > mgmt companies. > > > > With this way, the hard work is DB oriented, and not APP oriented. > > However i wonder whether someone else has gone thru a similar process, > > or if someone finds some assumption conceptually flawed. > > > > Thanx for reading, and for any possible thoughts. > > > > -- > > -Achilleus > > > > > -- -Achilleus
