Re: [RFC] Interface of Row Level Security

On Wed, May 23, 2012 at 3:45 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
> I wanted to have discussion to handle this problem.
>
> Unlike leaky-view problem, we don't need to worry about unexpected
> qualifier distribution on either side of join, because a scan on table
> never contains any join. Thus, all we need to care about is order of
> qualifiers chained on a particular scan node; being reordered by
> the cost to invoke functions.
>
> How about an idea to track FuncExpr come from the security policy
> and enforce "0" on its cost? Regular functions never reach zero
> cost, so the security policy must be re-ordered to the head.

Hmm. That would disregard the relative costs of multiple qualifiers
all of which were injected by the security policy, which I suspect is
not a good idea. Furthermore, I think that we should not assume that
there is no join involved. I would expect a fairly popular RLS qual
to be something of the form "WHERE NOT EXISTS (SELECT 1 FROM hide_me
WHERE hide_me.pk = thistab.pk)". Perhaps when we see that RLS
applies, we should replace the reference to the original table with a
subquery RTE that has the security_barrier flag set - essentially
treating a table with RLS as if it were a security view.

Also, suppose that Bob applies an RLS policy to a table, and, later,
Alice selects from the table. How do we keep Bob from usurping
Alice's privileges? If we insist that Bob's RLS policy function runs
as Bob, then it defeats inlining; but if it runs as Alice, then Bob
can steal Alice's credentials. One idea is to apply the security
policy only if Alice's access to the table is granted by Bob. That
way, if Alice is (for example) the superuser, she's immune to RLS.
But that doesn't seem to completely solve the problem, because Alice
might merely be some other relatively unprivileged user and we still
don't want Bob to be able to walk off with her access.

Another idea is to set things up so that the RLS policy function isn't
applied to each row directly; instead, it's invoked once per query and
*returns* a WHERE clause. This would be a lot more powerful than the
proposed design, because now the table owner can write a function that
imposes quals on some people but not others, which seems very useful.

>> Also, if the point here is to provide security for tables not views,
>> it seems like you really need to have (at least a design for) RLS
>> security on insert/update/delete operations.  Just adding the same
>> filter condition might be adequate for deletes, but I don't think it
>> works at all for inserts.  And for updates, what prevents the user from
>> updating columns he shouldn't, or updating them to key values he
>> shouldn't be able to use?
>>
> If we also apply the security policy to newer version of tuples on
> update and insert, one idea is to inject a before-row-(update|insert)
> trigger to check whether it satisfies the security policy.
> For same reason, the trigger should be executed at the end of
> trigger chain.

It's not clear to me that there is any need for built-in server
functionality here. If the table owner wants to enforce some sort of
policy regarding INSERT or UPDATE or DELETE, they can already do that
today just by attaching a trigger to the table. And they can enforce
whatever policy they like that way. Before designing any new
mechanism, what's wrong with the existing one?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company