I have used the rules system to implement a simple sort of row based security.
Basically each row has an integer field that is nullable (we'll call it
row_mask), if the value is NULL then there is no security for the row, and if
the value is NOT NULL it must be greater than or equal to the value in another
related table that basically looks like this:
create table rolemask (
role_name name,
role_mask int4,
primary key rolemask_pkey (role_name)
);
Not the most elegant solution, but it does a very limited job reasonably well
because the rule allows the planner to cache the query plan that includes the
security check once for a connection/table combination. It's a simple _RETURN
rule that (if memory serves) looks like this:
create rule "_RETURN" as on select to sometable do instead
select * from sometable where row_mask is null or
row_mask >= (select role_mask from rolemask where role_name =
SESSION_USER);
Similar rules on insert/update can be used a little more creatively, and can
throw an error. The select rule simply excludes rows entirely. Also, the
insert/update rules don't apply to COPY operations.
I've done a few different variations of the above, including using multiple
masks (adding a mask_type to the tables involved), functions, or functions and
rules together, but this seems to give the best performance, even compared to a
caching function (pl/pythonu) that stores role and permissions data at the
connection level.
Just a thought :-)
Richard
-----Original Message-----
>From: Mike Orr <[email protected]>
>Sent: Feb 2, 2010 4:56 PM
>To: [email protected]
>Subject: Re: Do your models handle all the data logic?
>
>On Tue, Feb 2, 2010 at 12:19 PM, Frank <[email protected]> wrote:
>>> The other thing is it doesn't have row-based permissions, only
>>> table-based. Unless it was added in PostgreSQL 8.4. Although I suppose
>>> you can mimic row-based permissions in a stored procedure.
>>
>> I know I'm a bit off-topic but does SQLAlchemy allows for row-based
>> permissions?
>
>Permissions are handled by the database engine, not SQLAlchemy.
>SQLAlchemy is just a front end for passing SQL statements to the
>database.
>
>You pretty much have to go to commercial database engines for
>row-based permissions at tjios. MySQL might have it in one of its
>backends somewhere. SQLite does not have any permission restrictions,
>although it's subject to the filesystem permissions of the database
>file.
>
>--
>Mike Orr <[email protected]>
>
>--
>You received this message because you are subscribed to the Google Groups
>"pylons-discuss" group.
>To post to this group, send email to [email protected].
>To unsubscribe from this group, send email to
>[email protected].
>For more options, visit this group at
>http://groups.google.com/group/pylons-discuss?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
