Search Postgresql Archives

Re: Thoughts on a Isolation/Security problem

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

 



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



[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