Virtual Private Database Functionality - Mailing list pgsql-general

Hi,

I am new to the PostgreSQL world, coming from many years in the Oracle
world (Since Oracle 3).

One feature that I am trying to find in the PostgreSQL database is an
equivalent to Oracle's Virtual Private Database functionnality (also
historically known as Fine-Grained Access Control, and sometimes
referred to as row-level security).

I have read some previous posts on this topic in the PostgresSQL archives
and some have provided workarounds to replicate what others were
requesting.

Unfortunately, I think (I may be wrong) that the core of the issue was
missed. Here is my description of Virtual Private Database (VPD) and
what I am looking for.

VPD requires four basic elements:

 - Trigger upon logon (Session initiation)

   The purpose of the trigger is to be able to capture on who or what
   the session is for and _collect_ data that describes the context
   of the session. (no security rules are involved at this point, only
   the base data that will be input into the rules)

   This logon trigger must run with priviledges that are greater than
   what the session's user has. See next step below for the reason.

 - Secured context data stored for the complete session (session object)

   Once the logon trigger has fired and collected the context data,
   the data needs to be stored in memory. We dont what to potentially
   require reading database tables again for every SQL transaction
   that is issued by the application, to obtain this context data.

   The context data must be secured from the session's user, since if
   he can change it, he can change his priviledges upon the database
   tables.

 - Table level SELECT, INSERT, UPDATE, DELETE triggers

   When any SQL transaction is issued upon specified tables,
   optionnally INCLUDING SELECTs, then a trigger is fired and a
   stored procedure is run.

   The stored procedure is known to define the security policy. The
   same stored procedure can be associated with many tables in the
   database. Ideally, the name of the triggered table is passed to
   the security policy procedure.

   In Oracle these are not _called_ TRIGGERS, but rather POLICIES
   and may be enabled and disabled by priviledged users without
   affecting any other part of the database or application.

 - Security policy defining stored procedure

   The purpose of the stored procedure is to _dynamically_ build a
   WHERE clause predicate that will be used to rewrite the query
   (ADD the predicate using the AND operator) that was issued upon
   the table by the user or the application.

   The predicate is built by accessing :
      - the session specific context data
      - the paramater indicating which table is accessed
      - any other information in database tables

   The 'dynamic' part allows all security policies to be changed
   at will without affecting any database structures or application
   code. Security policies can be totally data-driven, not hard-coded.

The reason VPD is usefull is it allows an application to be written
to perform business functionnality WITHOUT having any security
related code imbedded in it. The security is coded seperately from
both the application and the database structure, and is active
regardless of which application or tool is used to access the database.

In a sense, it is like a different implementation of aspect oriented
programming. The businness function is seperate from the security
function which is also seperate from the database structure definition.

So, the issue around VPD is not simply about being able to
specify row-level security, but rather to have the security be
defined seperately from both application and database structure and
also have this security be extremely dynamic and potentially be
data-driven and defined by non-technical users within and application
(but still active regardless of other application or tool access).

If anyone has an idea how this could be implemented in PostgreSQL, I
would appreciate feedback.

Thanks,

--
François Gendron
La Société d'Informatique Gendron Inc.
Tél: (514) 212-3994   Fax: (418) 682-5391
mailto:Francois.Gendron@gendron.ca