Re: row-level security model

[Mike Mascari <mascarm@mascari.com>]

John DeSoi wrote:

> I have a security model I have implemented in another (non-SQL) database 
> environment that I would like to use in Postgresql. I have read the 
> rules and set returning functions documentation but I still don't see 
> how it would work in Postgresql. Any ideas or direction would be greatly 
> appreciated.

The mechanism that has been most often described is to use 
PostgreSQL user and groups and use CURRENT_USER in the view 
definition. For example:

CREATE TABLE salaries (
 employee text unique not null primary key,
 salary numeric(16,2) not null,
);

CREATE VIEW v_salaries AS
SELECT *
FROM salaries
WHERE employee = CURRENT_USER;

with the appropriate GRANTs and REVOKEs applied to the view and 
table. You could leverage PostgreSQL groups or join against an 
application group-membership table:

CREATE VIEW v_salaries AS
SELECT *
FROM salaries
WHERE CURRENT_USER IN
 (SELECT userid
  FROM appgroups
  WHERE groupid = 'Accounting');

etc.

There are normally two issues that crop up:

1) Often people would prefer to not use PostgreSQL's authentication 
mechanism, in which case CURRENT_USER is not available for view 
definitions. The only way I know around this is to provide a little 
'C' function to get/set a session attribute, invoke the set() upon 
connecting and build the views over the get(). The set() could, for 
example, take a userid and password and only actually set the global 
variable accessed by get() if the password matched the application 
user-table.

2) PostgreSQL allows the use of functions in WHERE clauses that can 
modify the database. Oracle does not. A side effect is that if a 
user has the ability to write a function, regardless of whether or 
not the language is trusted, they can by-pass the use of views as 
security:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3D02B372.B6A4EFB6%40mascari.com&rnum=2&prev=/groups%3Fq%3DMike%2BMascari%2Bsecurity%2Bhole%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den

HTH,

Mike Mascari








---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org