Sample Header Ad - 728x90

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