Re: [RFC] Interface of Row Level Security

2012/5/23 Robert Haas <robertmhaas(at)gmail(dot)com>:
> 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)".
>
Please ignore the approach to track cost value of qualifiers.
I believe it does not work well without something fundamental updates.

> 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.
>
I become to think it is a better approach than tracking origin of each
qualifiers. One problem is case handling on update or delete statement.

It may be possible to rewrite the update / delete query as follows:

From:
UPDATE tbl SET X = X + 1 WHERE f_leak(Y)
To:
UPDATE tbl SET X = X + 1 WHERE ctid = (
SELECT * FROM (
SELECT ctid FROM tbl WHERE uname = getpgusername() <== (*)
should have security-barrier
) AS tbl_subqry WHERE f_leak(Y)
);

Expanded sub-queries will have security-barrier flag, so it enforces
the "uname = getpgusername()" being checked earlier than f_leak(Y).
We may need to measure the performance impact due to the reform.

> 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.
>
I think, this situation is similar to a case when we reference a view
without privileges to underlying tables. If Bob set up a view with
something "tricky" function, it allows Bob to reference credentials
of users who reference the view.
More or less, it might be a problem when a user try to invoke
a user defined function declared by others.
(Thus, sepgsql policy does not allow users to invoke a function
declared by another one in different domain; without DBA's checks.)

I think it is a good idea not to apply RLS when current user has
superuser privilege from perspective of security model consistency,
but it is inconsistent to check privileges underlying tables.

> 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.
>
Sorry, I don't favor this idea. Even if table owner set up a function to
generate additional qualifiers, it also has no guarantee the qualifiers
are invoked prior to user-given one.
It seems to me this approach will have same problem...

>>> 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?
>
Yes, we don't need any new invent to check the value of new tuples.
But it should be done after all the user-defined triggers. Existing
trigger does not have a mechanism to enforce order to be invoked.
So, what I really implement is a mechanism to inject some pseudo
triggers "at tail of the Trigger array".

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>