Re: Thoughts on a Isolation/Security problem - Mailing list pgsql-general

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.

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
>
>