Postgres policy exists bug

Hello,

currently I am working on a platform and I just recently found a weird bug
in policy if I use exists against a boolean. First of all I use postgres in
docker with the latest flag. Now back to the issue: I have the table
inventories where I store users item now only signed in users can see
others users inventory if the profiles table is_private column is set to
FALSE. This is the permissive select policy for the inventories table:
(
auth.grant() = 'password' AND
(
auth.uid() = user_id OR
EXISTS (
SELECT 1
FROM public.friends AS fr
WHERE fr.user_id = auth.uid()
AND fr.friend_id = user_id
) OR
(
EXISTS (
SELECT 1
FROM public.profiles AS p
WHERE p.user_id = user_id AND p.is_private IS FALSE
)
)
)
)
and technically I should see the inventory from the public user but not the
private. But somehow it shows the opposite way around: private users
inventory is visible but not from the public one. Now if it works the
opposite way (even if it doesn't make sense) I toughed: lets turn the check
around to this:
(
(
auth.grant() = 'password' AND
(
auth.uid() = user_id OR
EXISTS (
SELECT 1
FROM public.friends AS fr
WHERE fr.user_id = auth.uid()
AND fr.friend_id = user_id
) OR
(
EXISTS (
SELECT 1
FROM public.profiles AS p
WHERE p.user_id = user_id AND p.is_private IS TRUE
)
)
)
)
)

but by doing that I can't see any inventory. Negating the last policy also
doesn't return all inventories. Something feels really of. And to make it
more reliable I also will show the example profiles and the inventories

Profiles table:
CREATE TABLE public.profiles (
user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
username CITEXT NOT NULL UNIQUE,
is_private BOOLEAN NOT NULL DEFAULT FALSE,
accept_friend_requests BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT username_max_length CHECK (length(username) <= 20),
CONSTRAINT username_allowed_characters CHECK (username ~ '^[A-Za-z0-9._]+$')
);
Used data in testing:
[image: image.png]
Inventories table:
CREATE TABLE public.inventories (
id SERIAL PRIMARY KEY,
item_id INT REFERENCES public.items(id) ON DELETE CASCADE NOT NULL,
user_id UUID REFERENCES public.profiles(user_id) ON DELETE CASCADE NOT NULL,
amount BIGINT DEFAULT 0 NOT NULL,
parameter JSONB DEFAULT '{}'::JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
updated_at TIMESTAMPTZ,
CONSTRAINT parameter_size_limit CHECK(pg_column_size(parameter) <= 1048576)
);
Used data:
[image: image.png]

To note the profiles table is public visible to anyone so that is not the
problem. I searched the whole internet and haven't found anything that
describes this behavior.
I would like to hear some new about this bug because it really slow me down
in MVP development.

I am thanking in advanced

With best regards

Wladimir Trubizin