0

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, here, 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.

2
  • 1
    Is it embarrassing to drop your pants? Not it in your bedroom, and nobody there but your trusted ... server. (And the blinds are tight, and she wouldn't leak recordings (by accident.) Commented Apr 24 at 0:01
  • @ErwinBrandstetter that seems like a (poetic) way of saying that the threat is not a big deal in this scenario. Just want to check! Commented Apr 24 at 3:20

1 Answer 1

1

A function could leak information to the user that is calling it, if it operates on data that the user is not allowed to access. That leak could be an error message: "I get a division-by-zero error, so there must be a zero somewhere." Now -> doesn't give you that error, but can you prove that a smart user cannot get it to leak information about the JSON it is operating on?

If your server and application are totally secure, you don't need any extra security. So you had better not use row level security, but use explicit WHERE conditions wherever appropriate. I admire your trust in the perfection of your application.

If you want security, you have to pay the price for security. Using a security feature like row level security and then disarming it by marking random functions as LEAKPROOF is a foolish exercise.

1
  • That makes sense, I accepted it as the answer! Commented Apr 25 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.