PostgreSQL and/or SQL Server alternatives to Oracle's SYS_CONTEXT

Question

Having used an Oracle DB for a while, I got used to using the Oracle DB Application Context to implement access restrictions. To simply put it, upon login a procedure is called that puts the user details into the Oracle Application context like so:

DBMS_SESSION.SET_CONTEXT('context_name', 'user_id', user_id);

Then access can be enforced by creating views that would look in the context to determine which rows can be seen by a user like so:

CREATE VIEW users_vw AS 
SELECT *
  FROM users
 WHERE user_id = SYS_CONTEXT('context_name', 'user_id');

I have now moved away from Oracle and am using PostgreSQL for personal projects and SQL Server 2000 and 2008 at work. Can anyone tell me if there are corresponding capabilities that PostgreSQL or SQL Server offer?

Answer 1

In PostgreSQL, you could perhaps get by with SECURITY DEFINER-functions that reason on the current user? Documented here: http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

Edit:

plperl can be used for session variables. There are other alternatives (see link in comment), but plperl is the simplest of them.

Answer 2

If you'll use pl/perl (or pl/python if I recall correctly) you will get session variables for free.

Otherwise - when using plpgsql - you can use the GUCs to store your own settings, and use it in practically the same way.