Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Migrating from Supabase

blog.val.town

Post reply on HN
Print view View original

61–70 of 136 posts

Re: Migrating from Supabase

#61

Post by crooked-v »

kiwicopple wrote: post #56
crooked-v wrote: post #50

Earlier quoted context omitted.

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 row…

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-locati…

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.

Re: Migrating from Supabase

#62

Post by pluto_modadic »

kiwicopple wrote: post #26
simonw wrote: post #22

The documentation section here applies to so many products I've battled in the past. > The command supabase db remote commit is documented as "Commit Remote Changes As A New Migration". The command supabase functions new is documented as "Create A New Function Locally." The documentation page is beautiful, but the words in it just aren't finished. Great documentation is such a force multiplier for a product. It's so…

this is very fair criticism. We hired a Head of Docs in March. I hope the improvements are evident since he joined, both in content and in usability. We have a long way to go, but we're working on it.

yeah, most golang/rust/API documentation in products seems to think that "the function name is documentation", which.... no it's not. that's a tooltip in an IDE, not a docs website.

Re: Migrating from Supabase

#63

Post by kiwicopple »

crooked-v wrote: post #61
kiwicopple wrote: post #56

Earlier quoted context omitted.

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-locati…

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…

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

Re: Migrating from Supabase

#64

Post by crooked-v »

kiwicopple wrote: post #63
crooked-v wrote: post #61

Earlier quoted context omitted.

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…

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

Re: Migrating from Supabase

#65

Post by doctor_eval »

Ethan_Mick wrote: post #44

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…

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.

Re: Migrating from Supabase

#66

Post by neximo64 »

Can someone explain a bit better what the issues are. What exactly are the issues with migration if you use an SQL script to do the migration instead of the supabase interface?

Re: Migrating from Supabase

#67

Post by bodecker »

kiwicopple wrote: post #59
bodecker wrote: post #53

(Significantly edited after discussion) I also had a tough time working w/ an app someone else built on Supabase. We kept bumping up against what felt like "I know feature X exists in postgres, but it's 'coming soon' in Supabase." IIRC the blocker was specific to the trigger/edge function behavior. However after reflecting more, I don't remember enough to make a detailed case. Perhaps the issue was with our use of th…

(supabase ceo) > "I know feature X exists in postgres, but it's 'coming soon' in Supabase." There is no feature that exists in postgres that doesn't already exist in Supabase. In case it's not clear, supabase is just Postgres. We build extensions, we host it for you, and we build tooling around the database. Our Dashboard is one of those tools, but there is always an escape hatch - you can use it like any other postg…

Thanks for the response. I do recall hitting some product limitations (a webhooks "beta" that we tried to use but hit a blocker). Reflecting more, I don't recall the supporting details specifically enough though. Edited original post and apologies for the added noise.

Re: Migrating from Supabase

#68

Post by jgraettinger1 »

ilrwbwrkhv wrote: post #13

I also had the same experience with Supabase. Even though it looks like a great product initially, it has a lot or errors and bugs when you are trying to actually build something more robust than a toy app. Local development is a massive pain with random bugs. The response time of the database also varies all over the place. But the most important problem that we faced, was having so much of application logic in the…

> Row level security is their "foundational piece", but there is a reason why we moved away from database functions and application logic in database over a decade ago: that stuff in unmaintainable.

Funny. In my experience, application-level authorization checks are very error-prone, easy to accidentally omit, and difficult to audit for correctness."Unmaintainable", I suppose.

Whereas RLS gives you an understandable authorization policy with a baseline assurance that you're not accidentally leaking records you shouldn't be.

Re: Migrating from Supabase

#69

Post by solarkraft »

refulgentis wrote: post #60
tr3ntg wrote: post #34

Earlier quoted context omitted.

Appreciate this well-thought out response. As someone who has built several proof-of-concepts on Supabase (but never going far enough to test its limits), articles by Val Town here and responses like yours all work towards my analysis of the platform for future projects. It's funny that threads like these bring up comments like "Well I use XYZ and it solves all of my problems." As if a one-time mention of a new PaaS…

+1, paradoxically, I’m even more likely to use supabase after this. Really thoughtful

Not paradoxical at all. They're clearly interested in competing fairly instead of locking you in. That's a big advantage. They're also critically evaluating their approach. Exactly what I as a customer would want!

Re: Migrating from Supabase

#70

Post by esafak »

Ethan_Mick wrote: post #44

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…

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