Greetings, * PG Doc comments form (nore...@postgresql.org) wrote: > RLS documentation seems to say the user's predicate is evaluated AFTER the > policy is evaluated. This presents to me an issue that I can't wrap my head > around, and seems to confuse others as well. > > Setup: > 1. We have a policy-enabled table that has millions of rows, with ID as a > primary key. > 2. There exists a row in this table whose ID column is 10. > 3. User submits a query: SELECT * FROM MYTABLE WHERE id = 10. > > According to the documentation, the WHERE clause is not evaluated until > AFTER the policy is evaluated, thus there is initially a full table scan of > MYTABLE that returns only the set of rows that the user has access to (via > the policy), THEN the WHERE clause is activated that reduces the row count > to 1 or 0. > > This sounds non-performant, and if this is or is not the case, I think it > should be more clearly explained. In addition, a link to a "best practices > using the policy effectively" would be useful, as from reviewing stack > overflow, there is lots of concern over performance of RLS.
Functions which are leakproof can be pushed down below the policy because those functions won't ever leak information about the values that they might see in the data that the user shouldn't be allowed to see. Logically, the WHERE clause still comes after the policy, but with the leakproof function that backs the '=' operator, we're able to optimize the query and use the 'id' index that exists. Of course, RLS isn't going to be free and you can certainly have cases where you're using a function or operator that isn't leakproof and then you'll have the issues you describe, or just in general adding on the conditions of the policy could have performance impacts, but this specific case isn't going to be an issue. Note that we do explicitly perform query optimization *after* adding in the RLS policies into the query. Thanks, Stephen
signature.asc
Description: PGP signature
