From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: vost_800@gmx.de
Subject: BUG #18271: Re: Postgres policy exists bug
Date: Fri, 05 Jan 2024 14:05:59 +0000
Message-ID: <18271-70059a372db705e3@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, vost_800@gmx.de
Subject: Re: BUG #18271: Re: Postgres policy exists bug
In-Reply-To: <18271-70059a372db705e3@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox