BUG #18271: Re: Postgres policy exists bug
The following bug has been logged on the website:
Bug reference: 18271
Logged by: Wladimir Trubizin
Email address: vost_800@gmx.de
PostgreSQL version: 16.1
Operating system: debian:bookworm-slim docker postgres:latest
Description:
Hi,
After submitting my initial report, I attempted to find a workaround for the
issue. However, during this process, I discovered the same behavior as with
the EXISTS operation, specifically when dealing with subqueries.
The common factor among all cases was that they were based on subqueries,
and the inconsistencies surfaced when either selecting a column with the
boolean type or using a WHERE clause against boolean. To illustrate,
consider the following example:
FALSE IN (
SELECT is_private
FROM public.profiles AS p
WHERE p.user_id = user_id
)
I also investigated a subquery within a function called in a policy:
CREATE OR REPLACE FUNCTION public.is_user_private(_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
RETURN (
SELECT is_private
FROM public.profiles
WHERE user_id = _user_id
);
END;
$$ LANGUAGE plpgsql;
The same behavior was observed in this scenario as well.
The workaround I found was to store the value in a variable and then return
the variable instead of the value from the SELECT statement directly:
CREATE OR REPLACE FUNCTION public.is_user_private(_user_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
is_user_private BOOLEAN;
BEGIN
SELECT is_private
INTO is_user_private
FROM public.profiles
WHERE user_id = _user_id;
RETURN is_user_private;
END;
$$ LANGUAGE plpgsql;
I hope this clarifies the issue. Let me know if you have any questions or if
further clarification is needed.
Best regards,
Wladimir
PG Bug reporting form <noreply@postgresql.org> writes:
After submitting my initial report, I attempted to find a workaround for the
issue. However, during this process, I discovered the same behavior as with
the EXISTS operation, specifically when dealing with subqueries.
Neither here nor in the other thread have you provided a
*self-contained* example of what you think is wrong. I experimented
with a function like your example here and couldn't see anything that
looked wrong to me.
As far as this bit goes:
FALSE IN (
SELECT is_private
FROM public.profiles AS p
WHERE p.user_id = user_id
)
you do realize that "user_id" here will be resolved as p.user_id
because that's the most closely nested source of such a column?
So that WHERE clause will not provide any useful filter.
Your function example doesn't have that bug, but your original
policy definition looks like it might.
regards, tom lane
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>I haven't thoughed about the resolving of "user_id" to the closed nested source</div>
<div>because I haven't discovered this side effect when I was not working with boolish filters.</div>
<div> </div>
<div>After considering your advice I tried to use the first example with EXISTS by replacing "user_id" to</div>
<div>"public.inventories.user_id" and the side effect wasn't there anymore. Could have know that if had made more researcher beforehand.</div>
<div> </div>
<div>Next time, if I find something, I will create a self-contained test case so that everyone can reproduces the error.</div>
<div> </div>
<div>With best regards,</div>
<div> </div>
<div>Wladimir</div></div></body></html>