[Ethan_Mick]

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.

[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...

[doctor_eval]

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.

[spiffytech]

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.

[crooked-v]

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.

[jgraettinger1]

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.

[steve-chavez]

> 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...

[kiwicopple]

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

[notyograndma]

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.

[atonse]

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

[esafak]

I use a database that supports unlimited databases, tables, and views. Makes it easy to separate tenants.

[plugin-baby]

Is it Postgres? Schema-per-tenant + table inheritance?