Re: Row Level Security Execution within the SQL Evaluation Pipeline

Greetings,

* PG Doc comments form (noreply(at)postgresql(dot)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