Hacker News Reader

Comment by crooked-v | Hacker News Reader

The main issue we've had with it is that it's just plain slow for a lot of use cases, because Postgres will check the security for all rows before filtering on the joins, doing anything with WHERE clauses, doing anything to even tentatively take LIMIT into account, etc.

Imagine a 1-million-row table and a query with `WHERE x=y` that should result in about 100 rows. Postres will do RLS checks on the full 1 million rows before the WHERE clause is involved at all.

View on HN

I'm having a hard time relating to this comment given our own experience.

We use RLS extensively with PostgREST implementing much of our API. It _absolutely_ uses WHERE clauses and those are evaluated / indexes consulted before RLS is applied. Anything else would be madness.

> because Postgres will check the security for all rows before filtering on the joins, doing anything with WHERE clauses, doing anything to even tentatively take LIMIT into account, etc.

Note that the above only happens for non-inlinable[1] functions used inside RLS policies.

Going from what you mentioned below, it seems your main problem are SECURITY DEFINER functions, which aren't inlinable.

It's possible to avoid using SECURITY DEFINER, but that's highly application-specific.

[1]:https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions#I...

Try it with RLS policies that have any plain JOINs in them to reference other tables and you'll see execution times balloon massively (as in, orders of magnitude worse) for a lot of simple use cases, because it's then doing the RLS checks against every involved table to determine if your original RLS check is allowed to use them. The only way around that if you have multiple tables involved in determining access is to use cached subqueries with SECURITY DEFINER functions that aren't subject to the recursive RLS checking.

With PostgREST you can use the pre-fetch method to solve this: https://postgrest.org/en/stable/references/transactions.html...

You can use that to inject your ACL/permissions into a setting - set_config('permissions', '{"allowed":true}'). Then in your RLS rules you can pluck them out - current_setting('permissions'::jsonb).

This should make your RLS faster than most other options, in theory, because of data co-location

That seems deeply impractical for a lot of cases. If user A has access to 80,000 of those 1,000,000 rows in a way that's determined from another table rather than as part of in-row metadata, doing the lookups to JSONify 80,000 UUIDs as an array to pass along like that really isn't going to help beyond cutting down a 20-second query response to a still-unacceptable 7-second query response [1] just to get 100 rows back.

[1]: Both numbers from our own testing, where the 7 seconds is the best we've been able to make it by using a SECURITY DEFINER function in a `this_thing_id IN (SELECT allowed_thing_ids())` style, which should have basically the same result in performance terms as separately doing the lookup with pre-fetching, because it's still checking the IN clause for 1,000,000 rows before doing anything else.

You certainly wouldn't want to inject 80K UUIDs. I'm not sure I understand the structure you're using but if you want to send me some details (email is in my profile) I'd like to dig into it

As an aside, this is a good read on the topic: https://cazzer.medium.com/designing-the-most-performant-row-...

At its core it's a pretty simple multi-tenancy arrangement. Think something like this:

    tenants (id, updated_at)
    tenants_users (id, updated_at, tenant_id, user_id)
    products (id, updated_at, name, tenant_id)
    product_variants (id, updated_at, product_id, name)

One of the tenants views a page that does a simple `SELECT * FROM products ORDER BY updated_at LIMIT 100`. The RLS checks have to reference `products` -> `tenants` -> `tenant_users`, but because of how Postgres does it, every row in products will be checked no matter what you do. (Putting a WHERE clause on the initial query to limit based on tenant or user is pointless, because it'll do the RLS checks before the WHERE clause is applied.) Joins in RLS policies are awful for performance, so your best bet is an IN clause with the cached subquery function, in which case it's still then got the overhead of getting the big blob of IDs and then checking it against every row in `products`.

Do you have an index on `updated_at` ?

Yes. That's also irrelevant to the cause of the performance issues, which all happen before the ORDER BY and LIMIT even come into the picture in Postgres' query optimization.

Edit: To give a better idea of the impact of RLS here, writing up an equivalent query outside of the RLS context [1] has an under-1-second response time, where RLS turns that into 10x the time even in the most optimized case.

[1]: This kind of thing, roughly:

    SELECT *
    FROM products
    JOIN tenants ON products.tenant_id = tenants.id
    JOIN tenants_users ON tenants.id = tenants_users.tenant_id
    WHERE tenant_users.user_id = auth.uid()
    ORDER BY updated_at
    LIMIT 100

Reply on news.ycombinator.com