Row Level Security Execution within the SQL Evaluation Pipeline

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: mtaube(at)caci(dot)com
Subject: Row Level Security Execution within the SQL Evaluation Pipeline
Date: 2023-02-21 21:25:41
Message-ID: 167701474147.23475.959567841182626784@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/sql-createpolicy.html
Description:

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.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Stephen Frost 2023-02-21 21:43:40 Re: Row Level Security Execution within the SQL Evaluation Pipeline
Previous Message Peter Geoghegan 2023-02-21 17:49:31 Re: Transaction wraparound and read committed isolation level