Hi Tom,
Thanks once again for your time looking at this. I have a resolution but didn't exactly get to the bottom of what was going on.
Forcing the function used in the index to be leakproof did not work. I guessed, but am not certain, that this is because either to_jsonb() or jsonb_eq operator are not leakproof as well?
During my testing of a solution (which basically was not to use jsonb for this) I saw this message while using RLS in an unrelated query.
DEBUG: not using statistics because function "enum_eq" is not leak-proof
I did not see a message like this using my jsonb indexes, even though it seems like a related issue.
Is there another effect potentially going on here or incomplete debugging messages?
Best regards,
Alastair
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: 04 March 2020 04:22
To: Alastair McKinley <a.mckinley@analyticsengines.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Poor plan choice with partial unique indexes on jsonb column and simple RLS policy (with test script)
Alastair McKinley <a.mckinley@analyticsengines.com> writes:
> Thank you for having a look at this. In the interim I discovered that I could trigger the issue by creating a security barrier view, whereas a regular view worked fine, so I think that also points to your conclusion about leakyness?
> I attempted to workaround the issue with a leakproof function, so far with no success.
> ...
> Is this an approach that could fundamentally work?
Forcing the expression to be considered leakproof should work.
I'm not sure that your partial index is OK for the purpose of
collecting stats, though -- does it help if you make a non-partial
index on that function expression? Otherwise, it's possible that
I guessed wrong about which part of the WHERE clause is problematic.
You could try doing EXPLAINs with different portions of the WHERE
to see how the rowcount estimate changes.
BTW, just marking something "leakproof" when it isn't really so
is possibly a security problem. You should think twice about
what threat model you're hoping RLS will protect against.
regards, tom lane