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:
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:
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
Вложения
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Andres FreundДата:
Сообщение: Re: BUG #18259: Assertion in ExtendBufferedRelLocal() fails after no-space-left condition
Следующее
От: Michael PaquierДата:
Сообщение: Re: BUG #18259: Assertion in ExtendBufferedRelLocal() fails after no-space-left condition