Re: [RFC] Interface of Row Level Security - Mailing list pgsql-hackers

On Wed, May 30, 2012 at 3:26 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
> My preference is RLSBYPASS permission rather than the approach
> with functions that return policy clause at run-time, because it needs
> to invalidate prepared statement at random timing.
> In case of this function approach, the RLS policy shall be generated
> on planner stage, and we cannot have any assumption to the criteria
> of RLS policy. A function might generate RLS policy regarding to the
> current user id. Yes, it is straightforward. The prepared statement
> should be invalidate whenever current user-id got switched.
> However, someone may define a function that generate RLS policy
> depending on the value of "client_min_messages" for example.
> Do we need to invalidate prepared statement whenever GUC get
> updated? I think it is overkill. We cannot predicate all the criteria
> user want to control the RLS policy using the functions.
> So, RLSBYPASS permission is more simple way to limit number of
> situations to invalidate prepared statements.

That's a good point.

> If we would have an "ideal optimizer", I'd still like the optimizer to
> wipe out redundant clauses transparently, rather than RLSBYPASS
> permissions, because it just controls all-or-nothing stuff.
> For example, if tuples are categorized to unclassified, classified or
> secret, and RLS policy is configured as:
>  ((current_user IN ('alice', 'bob') AND X IN ('unclassified',
> 'classified')) OR (X IN 'unclassified)),
> superuser can see all the tuples, and alice and bob can see
> up to classified tuples.
> Is it really hard to wipe out redundant condition at planner stage?
> If current_user is obviously 'kaigai', it seems to me the left-side of
> this clause can be wiped out at the planner stage.
> Do I consider the issue too simple?

Yes.  :-)

There are two problems.  First, if using the extended query protocol
(e.g. pgbench -M prepared) you can prepare a statement just once and
then execute it multiple times.  In this case, stable-functions cannot
be constant-folded at plan time, because they are only guaranteed to
remain constant for a *single* execution of the query, not for all
executions of the query.  So any optimization in this area would have
to be limited to cases where the simple query protocol is used.  I
think that might still be worth doing, but it's a significant
limitation, to be sure.  Second, at present, there is no guarantee
that the snapshot used for planning the query is the same as the
snapshot used for executing the query, though commit
d573e239f03506920938bf0be56c868d9c3416da made that happen in some
common cases.  If we were to do constant-folding of stable functions
using the planner snapshot, it would represent a behavior change from
previous releases.  I am not clear whether that has any real-world
consequences that we should be worried about.  It seems to me that the
path of least resistance might be to refactor the portal stuff so that
we can provide a uniform guarantee that, when using the simple query
protocol, the planner and executor snapshots will be the same ... but
I might be wrong.

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