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.
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
[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.
As an aside, this is a good read on the topic: https://cazzer.medium.com/designing-the-most-performant-row-...
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`.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 100We 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.
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...
So instead we've stuck to having that filtering logic in the application side. The main concern is how user auth/etc works in Postgres. (lack of knowledge, not lack of trust).
Because we also have complex filtering like, "let me see all the people in my team if I have this role, but if i'm a public user, only show this person" etc
One of the things I think is important is to make the RLS query is super efficient - make the policy function STABLE and avoid database lookups, get the context from settings, etc.
RLS is pretty great as a backstop, but I found Supabase over-reliant on RLS for security, when other RBACs are available in regular PG. I can’t remember the details now.
I’ve found RLS is great with Postgraphile which uses a similar system to Supabase but is a bit more flexible.
I had seemingly-simple authz rules that RLS made challenging to express. I needed some operations honor the user's row access privileges, but with different column SELECT/UPDATE privileges. E.g., a user can only change a value after the backend validates and processes the input, or they shouldn't be allowed to retrieve their password hash.
Expressivity was challenging, but was compounded by security being implicit. I couldn't look at any given spot in my code and confirm what data it's allowed to access - that depends on the privileges of the current DB connection. Once you mix in connections with cross-user privileges, that's a risky situation to try to secure.
fwiw, Prisma has a guide on how to do RLS with it's client. While the original issue[0] remains open they have example code[1] with the client using client extensions[2]. I was going to try it out and see how it felt.
[0]: https://github.com/prisma/prisma/issues/12735
[1]: https://github.com/prisma/prisma-client-extensions/blob/main...
[2]: https://www.prisma.io/docs/concepts/components/prisma-client...