Обсуждение: 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