Re: Virtual Private Database Functionality

Hi Francoise;

I implimented something similar in an application once.  Here si the
feedback I would offer.  I don;t think that you can do exactly what you
are looking for easily, but if you are designing a database to use these
features, you can probably design them into your database schema.

See below.

François Gendron wrote:

>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).
>
>
Never quite gotten this fine-grained in my access controlls but my
models could be extended to do this easily enough.

>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 asm 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)
>
>
>
Assuming you are just looking at identifying the database user which is
logging in, you can use the current_user and session_user variables to
gather this information.  If you need to share a database-native
account, then you have some more work to do and the user *must* run an
authentication on login with a specified username and password.

>   This logon trigger must run with priviledges that are greater than
>   what the session's user has. See next step below for the reason.
>
>
Functions can be run as their definer.  See the PostgreSQL documentation.

> - 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.
>
>
>
I have used a temporary table for this and denied the user the right to
create temporary tables.  The user is also forbidden from doing anything
other than selecting from this table.  See above.  Otherwise you can use
the built-in session-handling to use database accounts in this way
without special privilage restrictions.

>   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
>
>
>
PostgreSQL has not select triggers.  To work around this issue, put your
tables in one schema and deny permission to them.  Use a view on the
tables to do the select permission checking.  This can be done using one
of the following methods:

select * from foo where check_acls('foo'); -- all or nothing, no
row-level permissions
select * from foo where check_acls('foo', foo.acl); -- Row-level
permissions.  Forbidden rows are simply blocked.

You can then add insert, update, and delete rules to define the behavior
when rows are modified in the view.  I usually add insert, update, and
delete triggers as well to actually enforce the permissions rather than
assume everything will work properly in the view.

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

You have to use views to accomplish this in PostgreSQL.  Sorry.

>   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.
>
>
>
Use rules instead of triggers then.  It is simpler.  You can also use
triggers to check individual rows (what I do).

>   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.
>
>
>
This is exactly the application I have used before as well.

>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).
>
>
>
Hope by now the answer is fairly obvious.  If you want a sample (which
is known to have some bugs) let me know.

>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
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>
>
>