Re: [RFC] Interface of Row Level Security

On Fri, May 25, 2012 at 5:08 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>>> 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.
>>
>> Seems like a somewhat random wart, if it's just an exception for
>> superusers.  I think we need to do better than that.  For example, at
>> my last company, sales reps A and B were permitted to see all
>> customers of the company, but sales reps C, D, E, F, G, H, I, and J
>> were permitted to see only their own accounts.  Those sorts of
>> policies need to be easy to implement.
>>
> Probably, if "sales_rep" column records its responsible repo, its
> security policy is able to be described as:
>  (my_sales_rep() in ('A', 'B') OR sales_rep = my_sales_rep())

Yes, but that's a pain to optimize.  When A or B tries to select from
the table, the query optimizer has to realize that my_sales_rep() is
stable, inline it, do constant simplification and throw away the
entire OR clause.  Note that this won't work today, because we only
constant-fold immutable functions, not stable ones.  Then, since there
are no remaining security quals, we have to realize that we actually
don't need the security_barrier subquery RTE at all, and optimize that
away as well.  Maybe we can make all of that work, and maybe we should
make all of it work, but it's fairly complex.  The advantage of having
the function return the qual rather than contain the qual is that all
of that goes away.  The function can choose to return nothing (no RLS
for this user) or it can choose to return something (which will likely
be simpler than what it would have needed to return out of the chute).One disadvantage is that we have to parse the
returnedqual instead 
of just sucking in a node-tree.

Anyway, I don't feel super-strongly about this particular idea, so if
I'm the only one who likes it, fine, but that having been said, I
think users are going to want a *declarative* way to control which
policies are applied to which users.  Suppose Bob is a sales rep who
is only allowed to see his own customers, but then one day, we decide
we trust Bob after all, so we want to let him see everything.  We
could go back and update the IN (...) list in the security policy
function, but that's an ugly and unscalable nuisance, especially if
we've got 10,000 users.  It's much nicer to be able to just grant bob
a permission using some kind of, well, GRANT command.  That's what
we're doing, after all.  Alastair's proposal of making the security
policy a property of the GRANT is one way of tackling that, and the
RLSBYPASS permission I proposed elsewhere is another.  Something along
these lines seems likely to improve performance (by replacing a query
optimization problem with a syscache lookup) as well as ease-of-use.

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