What is harm in marking a function as LEAKPROOF in PostgreSQL when row level security is in effect in a single tenant setup?
1
vote
1
answer
245
views
I am trying to understand the implications in marking a PostgreSQL function as LEAKPROOF. Let us say we have a (single tenant) web application setup, which is Browser -> Server (Django, Rails, etc.) -> PostgreSQL. The server is the only one that has access to PostgreSQL, the only one that can add run queries, execute functions, etc.
The server tries to query a table in the public schema called
foo
. The table is just a primary key, id
, and a jsonb
field, bar
, with 100,000 rows. The jsonb
field has a GIN index on it. When row level security is not applied, any ->
queries for bar
use the GIN index just fine. However, when row level security is applied, row level security forces a sequence scan. This is a known "problem", as covered [here](https://postgrespro.com/list/thread-id/2480344) , [here](https://www.postgresql.org/message-id/CAGrP7a2kQ5vVUE%3D46xPU1LLJJ2GuYSREWsTY49WbfPV6R0sM3g%40mail.gmail.com) , etc.
After reading the PostgreSQL documentation and a bunch of posts, I struggle to see the risk in overwriting the jsonb
filter operation with a LEAKPROOF equivalent. I know the documentation says it could "leak" information, but in a single tenant setup, who would it be leaking it to? The server could filter out any error messages to the client and the server itself is a trusted entity.
Asked by rationaltiger24
(143 rep)
Apr 23, 2024, 10:25 PM
Last activity: Apr 24, 2024, 05:55 AM
Last activity: Apr 24, 2024, 05:55 AM