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
