Re: Thoughts on a Isolation/Security problem

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@postgresql.org
> wrote:
> > Date: Tue, 18 Apr 2006 13:44:34 +0300 (EEST)
> > From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
> > To: pgsql-sql@postgresql.org, <pgsql-admin@postgresql.org>,
> >    <pgsql-general@postgresql.org>
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Thoughts on a Isolation/Security problem.
> > Message-ID:
> > <Pine.LNX.4.44.0604181230470.24609-100000@matrix.gatewaynet.com>
> >
> >
> > 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