4 ms·
We gave up on RLS when we realized that the query engine behaved as if the RLS-filtered table was being fully materialized prior to any join or where clause fil
by saltcured 7y ago
We gave up on RLS when we realized that the query engine behaved as if the RLS-filtered table was being fully materialized prior to any join or where clause filtering. So nearly every query turns into full table scan and sort spasms.
- LunaSea 7y agoWas this slow even after the policy conditions were indexed? Did you have only equality type RLS conditions or also more heavy ones too?
- saltcured 7y agoOur typical policies would have to compare a column value to the local session variable where we stored client authentication context attributes. Sometimes this was an embedded column and sometimes we had to use a scalar subquery to cross-walk over to the stored data that acted as a row or row-class ACL. Our service now renders its own joins and subqueries, merging the client-based constraints with other application filtering criteria. We are never using prepared statements but instead dynamically render SQL where the client context is baked in as constants in the policy-enforcing filters. The query planner seems to do a much better job deciding on join and filtering orders, particularly when the application filtering criteria sparsify the result so that policies only need to be enforced on a small subset of the tables. For RLS to match our current system, I think it would need a way to partially evaluate the policy, so it could reuse common subexpressions and treat them like constants during plan optimization.
- taffer 7y agoWhich version of Postgres did you use? There have been significant improvements in RLS enforcement in Postgres 10: https://www.postgresql.org/docs/10/release-10.html#id-1.11.6.13.5.3.5 https://www.postgresql.org/docs/10/release-10.html#id-1.11.6...