How do people on HN like Row Level Security? Is it a better way to handle multi-tenant in a cloud SaaS app vs `WHERE` clauses in SQL? Worse? Nicer in theory but less maintainable in practice?
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.
I use both for defence in depth. The SQL always includes the tenant ID, but I add RLS to ensure mistakes are not made. It can happen both ways: forget to include the tenant in the SQL, or disable RLS for the role used in some edge case. For multitenancy, I think it’s absolutely critical to have cross-tenancy tests with RLS disabled.
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 found RLS challenging to work with when I prototyped an app with it and postgraphile.
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.
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.
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.
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.
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
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`.
Hi - We're an analytics solution for a specific vertical, so this is probably not appropriate for everyone but - what we did was create partitioned data tables that are named using a hash of the user UUID and other context to create the partition table name upon provisioning data tables for the user. The parent table is never accessed directly. We're using Supabase, but we don't use Supabase's libraries to operate this.
It is highly appealing to have that defense in depth. However, when building a prototype or a product, not having experience in it causes me to worry that we will end up being stuck with a choice where it's very hard to pull ourselves out of.
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.