Virtual Private Database

I want to implement something like Oracle's Virtual Private Databases
using PostgreSQL.

This uses rewrite rules or views to provide each user with a different,
much restricted, view of the database.  For instance, in a retail outlet
database, a customer would only be able to see details of their own
purchases; a salesperson would be able to see full details for purchases
for their own sales and maybe summary details for all purchases in their
department; and the store owner would be able to see everything.

The way Oracle does it is something like this:

On successful login, a trigger fires and determines what class of user
this is (customer, salesperson, etc), along with any other pertinent
information.  If memory serves this is all stored in a session object
called an application context.

Rewrite rules will exist on the tables to which access is restricted.
These add something like the following to the where clause:

and (   (context.userclass = 'Customer' and
         account_no = context.account_no)
     or (context.userclass = 'Salesperson and
         dept_no = context.dept_no)
     or (context.userclass = 'Merchant'))

I want to use this to create a more secure application server where the
server itself has almost no database privilege.  The user would
authenticate themselves to the database through this server, and then
their database session would gain the appropriate privileges.  This
seems much more secure than the usual method of giving the server full
privilege and trusting it to limit what the users can see and do.

I am unsure of the best way to tackle this but it would seem that I will
need to hack the backend server to add the necessary functionality.

My thinking at the moment is that I should add the means to change the
logon id for the current session.  I was thinking about adding maybe two
new functions, callable from SQL:

  - becomeuser(user, authentication_info)
    This would alter the session to gain the rights of a different user.
  - revertuser()
    This would revert the session back to its original state.

A possible alternative would be to dynamically assign the session to a
particular group but I feel a little more unsure of this.

All feedback, questions, humerous flames, etc will be welcomed.

--
Marc        marc@bloodnok.com