2 ms·

Aren't these extended queries with joins simply a function of the granular access rules you are trying to enforce? In other words, the downside of RLS that you

Aren't these extended queries with joins simply a function of the granular access rules you are trying to enforce? In other words, the downside of RLS that you mention is that it can destroy performance if the rules get complex. But the upside is that those complex rules are available. So there is no "true" downside, since the downside that is mentioned is only a feature of using the upside. One can presumably use RLS in a way that does not make use of all the advanced features it offers, but also does not destroy performance.

zoogeny 3y ago
Consider you have to do joins in the RLS for policy enforcement and you also have to do the same joins in your query for the business logic. One question is whether or not the query optimizer can recognize that the joins you do in the RLS are the same joins you are doing in your query, or if it will perform the subqueries multiple times. The bigger downside, IMO, is the dev experience. They are hard to debug and to test their performance. Of course, everyone has a different bar for what they consider "hard", but if I start getting back result sets from the db that don't match my expectations, or if the performance isn't what I expect, I have to track down if the culprit is my query, the RLS or some combination of those. And while I am pretty confident with SQL, I am not so confident in PL/pgsql - which was the point of my original comment. You will have to get confident in that if you go down this route. You'll have to learn what amounts to a complete language but you won't have logging, a debugger, etc. just a rudimentary set of tools. I'm not telling people not to do it - just warning them that the path passes through some thorny territory and you may get scratched up. It isn't all roses.
- jononomo 3y ago
  The business logic has to live somewhere, and it makes sense to me to start thinking of everything in terms of PL/pgSQL if you're building with Supabase, which I'm thinking of doing. I suppose I would like to have the problem of having so many users that my app begins to get bogged down, so I'll cross that bridge when I come to it. The RLS stuff seems like a bonus that I can choose to use if I want to, and I was happy not having it before, so I'm planning to be careful about actually activating it in any projects going forward -- it would have to be a use-case that I thoroughly understand.

zoogeny 3y ago

Consider you have to do joins in the RLS for policy enforcement and you also have to do the same joins in your query for the business logic. One question is whether or not the query optimizer can recognize that the joins you do in the RLS are the same joins you are doing in your query, or if it will perform the subqueries multiple times. The bigger downside, IMO, is the dev experience. They are hard to debug and to test their performance. Of course, everyone has a different bar for what they consider "hard", but if I start getting back result sets from the db that don't match my expectations, or if the performance isn't what I expect, I have to track down if the culprit is my query, the RLS or some combination of those. And while I am pretty confident with SQL, I am not so confident in PL/pgsql - which was the point of my original comment. You will have to get confident in that if you go down this route. You'll have to learn what amounts to a complete language but you won't have logging, a debugger, etc. just a rudimentary set of tools. I'm not telling people not to do it - just warning them that the path passes through some thorny territory and you may get scratched up. It isn't all roses.

jononomo 3y ago
The business logic has to live somewhere, and it makes sense to me to start thinking of everything in terms of PL/pgSQL if you're building with Supabase, which I'm thinking of doing. I suppose I would like to have the problem of having so many users that my app begins to get bogged down, so I'll cross that bridge when I come to it. The RLS stuff seems like a bonus that I can choose to use if I want to, and I was happy not having it before, so I'm planning to be careful about actually activating it in any projects going forward -- it would have to be a use-case that I thoroughly understand.