Re: Row-Level Security - Mailing list pgsql-hackers

On Sat, Dec 12, 2009 at 7:41 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Stephen,
>
>> Please comment, update the wiki, let us know you're interested in this..
>
> I blogged about this some time ago.  One issue I can see is that I
> believe that the RLS which many users want is different from the RLS
> which SEPostgres implements.
>
> Links:
>
> http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-1-30732
> http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-2-30757
>
> --Josh Berkus

I read these blog entries a while ago but had forgotten about them.
They're very good, and summarize a lot of my thinking on this topic as
well.  I think that we can design a framework for row-level security
which can encompass both constraint-based security and label-based
security.  Both seem to me to be be based around doing essentially the
following things:

1. Adding columns to the table to store access control information.
2. Populating those columns with additional information (owner, ACL,
security label, etc.) which can be used to make access control
decisions.
3. Injecting logic into incoming queries which uses the information
inserted by (1) to filter out rows to which the access control policy
does not wish to allow access.

Waving my hands in the air, #1 and #2 seem pretty straightforward.
For constraint-based security, one can imagine just adding a column to
the table and then adding a BEFORE INSERT OR UPDATE FOR EACH ROW
trigger that populates that column.  For label-based MAC, that's not
going to be quite sufficient, because the system needs to ensure that
the trigger that populates the security column must run last; if it
doesn't, some other trigger can come along afterwards and slip in a
value that isn't supposed to be there; plus, it might be inconvenient
to need to define this trigger for every table that needs RLS.

However, those problems don't seem insurmountable.  Suppose we provide
a hook function that essentially acts like a global BEFORE INSERT OR
UPDATE trigger but which fires after all of the regular triggers.
SE-PostgreSQL can gain control at that point and search through the
columns of the target relation for a column called, say,
sepg_security_label.  If it finds such a column and that column is of
the appropriate type, then (1) if an explicit security label is
provided, it checks whether the specified label is permissible, (2)
otherwise, if the operation is insert, it determines the appropriate
default label for the current security context and inserts it, (3)
otherwise, it just leaves the current label alone.  This might not be
quite the right behavior but the point is whatever behavior you want
to have in terms of assigning/disallowing values for that column
should be possible to implement here.  The upshot is that if the
system administrator creates an sepg_security_label column of the
correct type, row-level security will be enabled for that table.
Otherwise, it will not.

#3 seems a little bit trickier.  I don't think the GRANT ... WHERE
syntax is going to be very easy to use.  For constraint-based
row-security, I think we should have something more like:

ALTER TABLE table ADD ROW FILTER filtername USING othertable [, ...]
WHERE where-clause

(This suffers from the same problem as DELETE ... USING, namely that
sometimes you want an outer join between table and othertable.)

This gives the user a convenient way to insert a join against one or
more side tables if they are so inclined.

For security frameworks like SE-PostgreSQL, we might just provide a
hook allowing the incoming query tree to be modified, and let the hook
function check whether each table in the query has row-level security
enabled, and if so perform a modification equivalent to the above.

None of this addresses the issue of doing RLS on system catalogs,
which seems like a much harder problem, possibly one that we should
just ignore for the first phase of this project.

Thoughts?

...Robert