Why is my query so much slower with a `security_barrier` view, even without row-level policy?

53 Views Asked by At

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.

pg-admin view of EXPLAIN ANALYZE

Note: I'm running Postgresql 15.4 (and db-fiddle claims to run 15 as well).

0

There are 0 best solutions below