Обсуждение: parallel query and row-level security?

Hi, I am struggling to find information on how the new parallel query
features intersect with row-level security.  I am wondering whether
this is expected to work at this time?

My cursory experiments indicate that we can get a parallel plan when
querying as the table owner or superuser but not when querying as a
role that is subject to the row-level security policies on the same
table.

Our policies on the table are something like:

   CREATE POLICY "p1" ON schema1.table1
      FOR SELECT
      USING (ARRAY['x', 'y'] && schema1.our_procedure());

   CREATE POLICY "p2" ON schema1.table1
      FOR SELECT
      USING ((SELECT True FROM schema2.foo_authz p
              WHERE p.sec_class = table1.sec_class
              LIMIT 1));

We've defined our_procedure as STABLE PARALLEL SAFE using plpgsql and
it makes a call to current_setting().  Should we ever expect to see
parallel query with policies such as these?  Are there any documented
limits where we might adjust our policy and gain parallel plans?

Thanks,


Karl

p.s. In case you are wondering about these odd policies, we are using
them to inject web client authorization decisions into our data access
layer...

We are running under a single application postgresql role and our app
passes web client authentication attributes as a session parameter
which we look up with current_setting() and convert back into an array
to intersect with the ACL content ['x', 'y'] in the first policy
above.

The second policy permits access to rows in a certain security class
managed by dynamic content in another mapping table (which of course
has limits as to how the web application will modify it).  This allows
some subset of rows to be exposed to less trusted clients, while the
first policy reveals all rows to a more trusted group of clients.

We also have some tables where the ACL is stored in a column and
intersected for each row, but that doesn't exist in this first case
where I tried to get a parallel plan.

On Oct 27, Karl Czajkowski modulated:
> Hi, I am struggling to find information on how the new parallel query
> features intersect with row-level security.  I am wondering whether
> this is expected to work at this time?
>

Further experimentation shows that I can get parallel plans with
row-level security, if I remove our ugly policy with a scalar subquery
and instead denormalize the decision into another column of the same
table (i.e. one managed by a trigger).

However, our policies are still adding huge overhead.  If I lie and
say our stored procedure is IMMUTABLE rather than STABLE, the
optimizer lifts our first (row-independent) policy check out of the
per-row filter and the query is 10x faster. But it seems to execute it
per-row when it is marked as STABLE, even though the function takes no
arguments and so does not vary by row.

But, our procedure calls current_setting which has STABLE volatility.
Does the session/connection cache plans?  Would declaring our function
as IMMUTABLE accidentally allow reuse of (stale) plans across
boundaries where we have modified the session parameter? My
conservative reading of the docs suggests it might, which would make
this cheat unsafe for us...


Thanks,

Karl