News for hackers. Stuff that matters.
Re:Towards Multiverse Databases by striking on Monday June 17, 2019 @10:24AM (#20203248)
The lack of this feature is why, for myself, PostgreSQL Row Level Security has been a non-starter.

After all, without a feature like this, you can't query on behalf on multiple users at a time without also encoding your RLS rules in some app queries, and caching becomes more difficult to implement as it now cannot be implemented independently of the app.

Is there any way to retrofit this behavior onto Postgres?

Re:Towards Multiverse Databases by ZitchDog on Monday June 17, 2019 @12:55PM (#20204915)
I have done it, you can store data in a transaction using SET LOCAL and refer to it in RLS policies.

I wouldn't recommend it though, it ends up being a performance nightmare.

Re:Towards Multiverse Databases by eloff on Monday June 17, 2019 @02:08PM (#20205769)
I'm curious why this ends up being a bad idea. Can you elaborate please?
Re:Towards Multiverse Databases by ZitchDog on Tuesday June 18, 2019 @03:58PM (#20217722)
Many operators are not leakproof and as such will not push down into the RLS query. We had to manually mark the operators as leakproof even though they were not.

Operators cannot be made leakproof in RDS, so we had to choose between RDS and RLS.

Explain plans for the simplest queries get crazy. So when your perf is bad, it's hard to know why (so RLS gets blamed for more than its share, but it also does cause problems)

Re:Towards Multiverse Databases by LunaSea on Monday June 17, 2019 @01:08PM (#20205061)
Is this due to policy conditions not being indexed or due to another reason?

I'd be interested if you had more information.

Re:Towards Multiverse Databases by saltcured on Monday June 17, 2019 @05:00PM (#20207339)
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.
Re:Towards Multiverse Databases by taffer on Monday June 17, 2019 @07:58PM (#20208522)
Which 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...
Re:Towards Multiverse Databases by LunaSea on Monday June 17, 2019 @06:01PM (#20207807)
Was this slow even after the policy conditions were indexed?

Did you have only equality type RLS conditions or also more heavy ones too?

Re:Towards Multiverse Databases by saltcured on Monday June 17, 2019 @09:11PM (#20208961)
Our 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.

Re:Towards Multiverse Databases by ZitchDog on Tuesday June 18, 2019 @03:59PM (#20217731)
(see my comment above)
Re:Towards Multiverse Databases by atwebb on Monday June 17, 2019 @10:44AM (#20203470)
I am not sure of how RLS works within Postgres, within SQL server, the function is applied at query time if applicable. You can bounce it up against a permissioning table to limit specific users/groups. Would groups/roles not be the way to manage sets of users?