Thread: row-level security model
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. I want to have multiple groups A, B, C where each group could only see a subset of a table (any number of groups would be possible). If a user is a member of groups A and B then the rows they can see should be the union of what A and B can see. Ideally I could just write a SELECT rule for a table or view that would somehow intersect the result rows of the query with the result of the security function (I think Oracle has something like this). So is it possible to write independent "access" functions for each group and have them be dynamically combined based on the group membership of the user? I want to do this at the database level so the security can be enforced for any application or report generator that is allowed to connect. Thanks, John DeSoi, Ph.D.
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
On Wed, Mar 31, 2004 at 12:30:58 -0500, John DeSoi <jd@icx.net> wrote: > > I want to have multiple groups A, B, C where each group could only see > a subset of a table (any number of groups would be possible). If a user > is a member of groups A and B then the rows they can see should be the > union of what A and B can see. Ideally I could just write a SELECT rule > for a table or view that would somehow intersect the result rows of the > query with the result of the security function (I think Oracle has > something like this). So is it possible to write independent "access" > functions for each group and have them be dynamically combined based on > the group membership of the user? I want to do this at the database > level so the security can be enforced for any application or report > generator that is allowed to connect. You should be able to do this with a view. current_user will give you the user. You probably want to join this with your own group table and with the table of interest. If each row belongs to only one group this is easy.
On Wed, Mar 31, 2004 at 03:53:22PM -0500, Mike Mascari wrote: > 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 Yes, but Oracle has much more advanced support for row-level security. Look for Fine-Grain Access Controll in the docs. Also, Oracle does allow for DML in SELECT queries; look up autonomous transactions. -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Apr 1, 2004, at 10:26 AM, Bruno Wolff III wrote: > You should be able to do this with a view. current_user will give you > the user. You probably want to join this with your own group table > and with the table of interest. If each row belongs to only one group > this is easy. I'm still not clear on how this would work. Let me provide a more concrete example and perhaps you can give me a little more help on how the view model would work. Suppose I have a school database. Teachers have a many-to-many relationship with their sections (classes). Students also have a many-to-many relationship with the section table. So the teacher group should be able to view the student records for any student in any of their classes. My second group is parents. The parent and student tables also have a many-to-many relationship. So when a parent logs in they should be able to view any student for which they are a primary contact. Now what if Sam is both parent and teacher groups. He should be able to view the students in his class as well as any students for which he is the guardian. Best, John DeSoi, Ph.D.
On Apr 1, 2004, at 10:55 AM, Jim C. Nasby wrote: > Yes, but Oracle has much more advanced support for row-level security. > Look for Fine-Grain Access Controll in the docs. I found this nice article with examples on row level security with Oracle: This is the core functionality of the row level security implementation. This function is what checks the context for the current user in line with the business rules defined above and implemented in the functions to set the security context. The function then, based on the rights of the user executing the select statement or update, insert or delete returns a predicate. This predicate is a dynamic piece of SQL that is appended to the where clause of the executing SQL by the Oracle optimizer at the time the SQL is parsed and executed. http://www.securityfocus.com/infocus/1743 John DeSoi, Ph.D.
From: "John DeSoi" <jd@icx.net> > > On Apr 1, 2004, at 10:26 AM, Bruno Wolff III wrote: > > > You should be able to do this with a view. current_user will give you > > the user. You probably want to join this with your own group table > > and with the table of interest. If each row belongs to only one group > > this is easy. > > > I'm still not clear on how this would work. Let me provide a more > concrete example and perhaps you can give me a little more help on how > the view model would work. > > Suppose I have a school database. Teachers have a many-to-many > relationship with their sections (classes). Students also have a > many-to-many relationship with the section table. So the teacher group > should be able to view the student records for any student in any of > their classes. My second group is parents. The parent and student > tables also have a many-to-many relationship. So when a parent logs in > they should be able to view any student for which they are a primary > contact. Now what if Sam is both parent and teacher groups. He should > be able to view the students in his class as well as any students for > which he is the guardian. > > Best, > > John DeSoi, Ph.D. > Do you think this would work? There are a lot of joins. And I assumed it would need to look up the parent's and teacher's usernames, and that your many-to-many relationships were in tables like students_parent_link. CREATE VIEW your_students AS SELECT s.* FROM student AS s INNER JOIN class_student_link AS cs ON s.student_id = cs.student_id INNER JOIN class AS c ON cs.class_id = c.class_id INNER JOIN students_parent_link AS sp ON s.student_id = sp.student_id INNER JOIN parent AS p ON sp.parent_id = p.parent_id INNER JOIN teacher AS t ON c.teacher_id = t.teacher_id WHERE t.username = CURRENT_USER() OR p.username = CURRENT_USER() -Marc Durham-
Marc,
On Apr 1, 2004, at 4:21 PM, Marc Durham wrote:
> Do you think this would work?
> There are a lot of joins. And I assumed it would need to look up the
> parent's and teacher's usernames, and that your many-to-many
> relationships
> were in tables like students_parent_link.
>
Thanks very much -- this helped get me started.
I think I finally came up with something that lets me have modularized
access functions where I can combine access but still maintain a single
view. Here is a rough example.
Let's say I have two access functions contact_students and
staff_teaches_students. If the current user is in the contact group it
returns the primary keys (integer dbid in my example) of the related
students. Similarly, if the current user is on the teaching staff, it
returns the keys for all students in his/her classes. So I create a
function to combine all of my access functions with union:
create or replace function student_access ()
returns setof integer as '
select * from contact_students()
union select * from staff_teaches_students();
' language sql;
Then my view is
create view student_v as select student.* from student,
student_access() as id
where student.dbid = id;
Comments/criticisms about design or performance issues?
Is there a way to provide column security without creating different
views for every possible scenario?
Best,
John DeSoi, Ph.D.
On 2/4/04 4:50 am, "John DeSoi" <jd@icx.net> wrote:
> Marc,
>
> On Apr 1, 2004, at 4:21 PM, Marc Durham wrote:
>
>> Do you think this would work?
>> There are a lot of joins. And I assumed it would need to look up the
>> parent's and teacher's usernames, and that your many-to-many
>> relationships
>> were in tables like students_parent_link.
>>
>
> Thanks very much -- this helped get me started.
>
> I think I finally came up with something that lets me have modularized
> access functions where I can combine access but still maintain a single
> view. Here is a rough example.
>
> Let's say I have two access functions contact_students and
> staff_teaches_students. If the current user is in the contact group it
> returns the primary keys (integer dbid in my example) of the related
> students. Similarly, if the current user is on the teaching staff, it
> returns the keys for all students in his/her classes. So I create a
> function to combine all of my access functions with union:
>
> create or replace function student_access ()
> returns setof integer as '
> select * from contact_students()
> union select * from staff_teaches_students();
> ' language sql;
>
> Then my view is
>
> create view student_v as select student.* from student,
> student_access() as id
> where student.dbid = id;
>
>
> Comments/criticisms about design or performance issues?
>
> Is there a way to provide column security without creating different
> views for every possible scenario?
Hi John,
I don't know if this will fit your needs, but this is how I handled row
level security in an application I have. It uses arrays, so may be
PostgreSQL specific I think... But basically I have person and group tables
CREATE TABLE person (
person_id INT4 NOT NULL,
..
<other fields>
..
username TEXT NOT NULL,
lab_group_id INT4 NOT NULL,
groups_ids INT[] NULL
);
CREATE TABLE groups (
group_id INT4 NOT NULL,
name TEXT NOT NULL
);
Then each object has a base table:
CREATE TABLE experiment_base (
expt_id INT4 NOT NULL,
..
<other fields>
..
owner_id INT NOT NULL,
writer_id INT[] NOT NULL,
readers_id INT[] NOT NULL
);
I can then control who can update the row at the user level, and who can
read the row at the group level using a view like so:
CREATE OR REPLACE VIEW experiment
AS
SELECT <various fields>
FROM experiment_base a,
person b
WHERE
a.owner_id = b.person_id AND
(readers_id &&
(select groups_ids from person a where a.username = current_user)
OR
(select person_id from person a where a.username = current_user) = ANY
(writer_id)
OR
owner_id = (select person_id from person a where a.username =
current_user));
I then have a couple of functions to add or remove group_id's from the
readers_id array, and also to add or remove person_id's from the writer_id
array
I don't have large numbers of users or groups, so it performs ok... Not sure
how the array approach will scale with more though.
I don't think this is a classical approach.... But it seems to work for me.
But I would appreciate comments/criticisms from others?
Cheers
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
On Thu, Apr 01, 2004 at 22:50:48 -0500, John DeSoi <jd@icx.net> wrote: > > Comments/criticisms about design or performance issues? Except for SQL functions which can be inlined, the optimizer can't optimize what is being done in the function with what is being done in the select statement. This might be a performance issue depending on your particular circumstances. > Is there a way to provide column security without creating different > views for every possible scenario? You should be able to have one view per table and use an OR in the where clause to check for student, teacher or parent access. These tests should be simple enough, that you should be able to just do the joins in the view.
On Thu, Apr 01, 2004 at 13:21:15 -0800, Marc Durham <pgsql@d-tech.com> wrote: > From: "John DeSoi" <jd@icx.net> > > Do you think this would work? > There are a lot of joins. And I assumed it would need to look up the > parent's and teacher's usernames, and that your many-to-many relationships > were in tables like students_parent_link. > > CREATE VIEW your_students AS > SELECT s.* > FROM student AS s > INNER JOIN class_student_link AS cs ON s.student_id = cs.student_id > INNER JOIN class AS c ON cs.class_id = c.class_id > INNER JOIN students_parent_link AS sp ON s.student_id = sp.student_id > INNER JOIN parent AS p ON sp.parent_id = p.parent_id > INNER JOIN teacher AS t ON c.teacher_id = t.teacher_id > WHERE t.username = CURRENT_USER() > OR p.username = CURRENT_USER() This makes the implicit assumption that students always have at least one parent and at least one teacher. If that isn't necessarily true you will need to use a couple of left (or right) joins or records of students missing one or the other will not be accessible.
On Apr 2, 2004, at 10:39 AM, Bruno Wolff III wrote: > Except for SQL functions which can be inlined, the optimizer can't > optimize what is being done in the function with what is being done > in the select statement. This might be a performance issue depending > on your particular circumstances. How does one specify a SQL function that can be inlined? Thanks, John DeSoi, Ph.D.
On Fri, Apr 02, 2004 at 13:27:38 -0500, John DeSoi <jd@icx.net> wrote: > > On Apr 2, 2004, at 10:39 AM, Bruno Wolff III wrote: > > >Except for SQL functions which can be inlined, the optimizer can't > >optimize what is being done in the function with what is being done > >in the select statement. This might be a performance issue depending > >on your particular circumstances. > > How does one specify a SQL function that can be inlined? You use language 'SQL' in 7.4 or later.
Bruno Wolff III wrote: > On Wed, Mar 31, 2004 at 12:30:58 -0500, > John DeSoi <jd@icx.net> wrote: > >>I want to have multiple groups A, B, C where each group could only see >>a subset of a table (any number of groups would be possible). If a user >>is a member of groups A and B then the rows they can see should be the >>union of what A and B can see. Ideally I could just write a SELECT rule >>for a table or view that would somehow intersect the result rows of the >>query with the result of the security function (I think Oracle has >>something like this). So is it possible to write independent "access" >>functions for each group and have them be dynamically combined based on >>the group membership of the user? I want to do this at the database >>level so the security can be enforced for any application or report >>generator that is allowed to connect. In Oracle, what you are referring to is Row Level Security, or VPD, or whatever their marketing dept. is calling it this week.