Обсуждение: 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