I am developing an application where I want to make use of the builtin row-level security in Postgresql. To make sure my views also respect it, I enabled the security_barrier and security_invoker option on my views. This made one particular query take more than 20 seconds whereas before it executed in milliseconds.
Minimal example
-- Two simple tables
CREATE TABLE foo (
name varchar(20),
id uuid
);
CREATE INDEX IF NOT EXISTS foo_idx ON foo (name);
CREATE TABLE bar (
id uuid primary key,
some_data text -- needed to make sequential scans actually slow
);
-- Generate some data
INSERT INTO bar (id, some_data)
SELECT gen_random_uuid(), repeat(md5(random()::text) || md5(random()::text), 10)
FROM generate_series(1, 100000);
INSERT INTO foo (name, id)
SELECT substr(md5(random()::text), 0, 5), bar.id FROM bar;
-- Create the problematic view
CREATE VIEW bar_protected WITH (security_barrier) AS (
SELECT * FROM bar
);
The slow query: SELECT * FROM foo INNER JOIN bar_protected ON bar_protected.id = foo.id WHERE foo.name LIKE 'ab%' LIMIT 10
The equivalent but fast query: SELECT * FROM foo INNER JOIN bar ON bar.id = foo.id WHERE foo.name LIKE 'ab%' LIMIT 10
Try it out on db-fiddle. Execution of the slow query is usually >100ms, whereas the fast query takes <10ms.
Analysis
I also tried it locally and looked at the execution plans, and it seems the slow query (on the left) uses a sequential scan on bar taking a lot of time. I'm not sure how to avoid that.
Note: I'm running Postgresql 15.4 (and db-fiddle claims to run 15 as well).
