[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] row-level security model
From:       Mike Mascari <mascarm () mascari ! com>
Date:       2004-03-31 20:53:22
Message-ID: 406B2FC2.4090309 () mascari ! com
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]