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

Realtime Postgres Row Level Security

supabase.com

Post reply on HN
Print view View original

11–20 of 34 posts

Re: Realtime Postgres Row Level Security

#11

Post by michelpp »

pgroves wrote: post #4
tehnorm wrote: post #3

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it's realtime nature to have data directly in the UI. Rough numbers are showing we cut development time by a third vs a traditional approach.

I'm on a POC project that's using PostgREST and it's been extremely fast to get a big complicated data model working with an API in front of it. But I guess I don't get how to really use this thing in reality? What does devops look like? Do you have sophisticated db migrations with every deploy? Is all the SQL in version control? I also don't really get where the users get created in postgres that have all the row-le…

This is my personal experience with using PostgREST (I haven't had the full supabase experience yet):

> What does devops look like?

I usually spin PostgREST workers up in some kind of managed container service, like Google Compute Engine. PostgREST is stateless, so other than upgrades, you never really need to cycle the services. As for resources PostgREST is extremely lean, I usually try to run 4 to 8 workers per gigabyte of RAM.

> Do you have sophisticated db migrations with every deploy?

You can use whatever migration tool your want. Sqitch is quite popular. I've even worked on projects that were migrated by Django but PostgREST did the API service.

> Is all the SQL in version control?

Yes this is a good approach, but it means needing a migration tool to apply the migrations in the right order, this is what Sqitch does and many ORMy libraries have migration sort of half-baked in.

It's worth noting that because many of the objects that PostgREST deals with are views, which have no persistent state, the migration of the views can be decoupled from the migration of the persistent objects like tables. Replacing a view (with CREATE OR REPLACE VIEW) can be done very quickly without locking tables as long as you don't change the view's schema.

Re: Realtime Postgres Row Level Security

#12

Post by SahAssar »

kiwicopple wrote: post #10
SahAssar wrote: post #8

I've been developing an app on postgrest (which supabase uses) for a while, and my solution to this was to have a NOTIFY trigger and a small program that broadcasted the changed ID's over SSE to clients. The clients would then fetch the changes. As for the RLS aspect I considered it "good enough" to allow all clients to know the changed ID (an UUID) as long as they got no other info, and planned to have the trigger a…

> LISTEN/NOTIFY triggers were not used We started with this implementation, however there are 2 issues with this approach. 1) NOTIFY has a limit of 8000 bytes, so large rows are silently dropped. 2) you need to attach the notify trigger to every table (vs a Publication, which can listen to an entire database in a single line of code). > why WS was used The Server is Elixir (Erlang) so it's very good at WS. Over time…

Thanks for the response! I have a few more questions if you have the time:

If I'm reading the article correctly this feature is only available for authenticated users? Is that restriction based on scaling or some other limitation?

When I looked at the example query in the article it only queries the primary key, does this also work for tables where the GRANT hides certain columns from different users?

Re: Realtime Postgres Row Level Security

#13

Post by michelpp »

iwebdevfromhome wrote: post #7
tehnorm wrote: post #3

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it's realtime nature to have data directly in the UI. Rough numbers are showing we cut development time by a third vs a traditional approach.

When I think of postgrest, supabase and other tools that allows you skip the backend completely and go straight to the DB; is how do you handle business logic that doesn't make sense to have in either the frontend or the DB ?

My go-to approach is to insert jobs into a queue table, and then have backend workers that consume items from the queue. This has a number of advantages:

1. Faster user experience, the user isn't waiting for the business logic to complete, inserting in the queue should only take a couple of milliseconds.

2. It's more secure, the web worker can have minimal privileges (INSERT only on the queue table) but the backend workers can have much more privilege because they are not user facing.

3. You can scale the web workers orthogonal to the queue workers, as they'll likely have very different scaling properties.

Supabase also has a WAL decoding tool called WALRUS that I have not tried yet, and that could be the most efficient approach going forward. The tradeoff with queue tables is that the tables are persistent and a bit more resilient to failure/retry.

Re: Realtime Postgres Row Level Security

#14

Post by steve-chavez »

Ericson2314 wrote: post #5

The row level security people should talk to the capability security people. Probably the main problem with RDBMS is also it's biggest benefit: you write code that naively looks some bad polynomial time, and then query planner does the magic and use indices. The problem is, of course, when the index doesn't exist or the query planner doesn't do a good job. What would be nice is a way to mandate that queries must go t…

> What would be nice is a way to mandate that queries must go through certain indices.

Yes, this is an interesting idea. We're kinda gravitating towards this approach in PostgREST lately. For example, on a recent discussion about integrating with PGroonga[1], we were thinking of only exposing its operators once an index is enabled on a column:

  CREATE INDEX index_name ON table USING pgroonga (column);
Enables doing:

  GET /table?column=groonga_query.PostgreSQL
Which roughly translates to:

  SELECT * FROM table WHERE column &@~ 'PostgreSQL';
Of course this is PostgREST-specific, only when going through it you'd enforce this restriction.

[1]: https://github.com/PostgREST/postgrest/issues/2028

Re: Realtime Postgres Row Level Security

#15

Post by me_me_mu_mu »

kiwicopple wrote: post #2

Hey HN, Supabase is an open source Firebase alternative. We're building the features of Firebase using enterprise-grade open source tools. We're particularly focused on scalability. We take proven tools like Postgres, and we make them as easy to use as Firebase. Today, Supabase is adding Row Level Security (RLS) to our Realtime engine. The linked blog post goes into depth around the technical implementation, so I’ll…

Looks really interesting, looking at your docs. I have an application using firebase + geofire where users can see things on a map, and those results are themselves updated (ex. real time reactions). However, geofire limits me heavily because I can't add additional filtering (ex. timestamp) and lack of bbox queries.

Can I use supabase's postgres + postgis setup, and support the same type of real time functionality where I can get a bbox result set to display on a map, and then when one of those items gets a reaction or something from some user, that change should propagate to anyone else who might have that particular item in their current map bbox result set. Just wondering if you know if that's possible. Thanks

Re: Realtime Postgres Row Level Security

#16

Post by pgroves »

iwebdevfromhome wrote: post #7
tehnorm wrote: post #3

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it's realtime nature to have data directly in the UI. Rough numbers are showing we cut development time by a third vs a traditional approach.

When I think of postgrest, supabase and other tools that allows you skip the backend completely and go straight to the DB; is how do you handle business logic that doesn't make sense to have in either the frontend or the DB ?

There are lots of simple things that are normally easier to do in the web framework that are suddenly easier to do in the database (with the side effect that you can do DB optimizations much easier).

But the other consideration is that you likely need to do a lot with a reverse-proxy like traefik to have much control of what you are really exposing to the outside world. PostgREST is not Spring, it doesn't have explicit control over every little thing so you're likely to need something in front of it. Anyway, point is that having a simple Flask server with a few endpoints running wouldn't complicate the architecture very much b/c you are better off with something in front of it doing routing already (and ssl termination, etc).

Re: Realtime Postgres Row Level Security

#17

Post by thelastbender12 »

iwebdevfromhome wrote: post #7
tehnorm wrote: post #3

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it's realtime nature to have data directly in the UI. Rough numbers are showing we cut development time by a third vs a traditional approach.

When I think of postgrest, supabase and other tools that allows you skip the backend completely and go straight to the DB; is how do you handle business logic that doesn't make sense to have in either the frontend or the DB ?

I have the same question. Since Supabase still exposes the connection parameters to the database, we could spin up another Flask server to deal with routes that are not expressible as simple CRUD operations. The JWT secret shared by the auth service (GoTrue) with Postgrest is available too, so I could also use it to authenticate the users myself.

@supabase developers, is this a reasonable way to do things or am I missing something?

Re: Realtime Postgres Row Level Security

#18

Post by cpursley »

iwebdevfromhome wrote: post #7
tehnorm wrote: post #3

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it's realtime nature to have data directly in the UI. Rough numbers are showing we cut development time by a third vs a traditional approach.

When I think of postgrest, supabase and other tools that allows you skip the backend completely and go straight to the DB; is how do you handle business logic that doesn't make sense to have in either the frontend or the DB ?

I actually extracted out the WAL listening bit of code from Supabase and use that to listen to changes in Postgres then do callback style business logic in Elixir. If you like Elixir, this could be an option.

I haven't had time to work on it myself until recently, but I brought up the idea of making that bit a separate library (and I understand that the Supabase folks are super busy).

https://github.com/supabase/realtime/issues/146#issue-874855...

Re: Realtime Postgres Row Level Security

#19

Post by cpursley »

michelpp wrote: post #13
iwebdevfromhome wrote: post #7

Earlier quoted context omitted.

When I think of postgrest, supabase and other tools that allows you skip the backend completely and go straight to the DB; is how do you handle business logic that doesn't make sense to have in either the frontend or the DB ?

My go-to approach is to insert jobs into a queue table, and then have backend workers that consume items from the queue. This has a number of advantages: 1. Faster user experience, the user isn't waiting for the business logic to complete, inserting in the queue should only take a couple of milliseconds. 2. It's more secure, the web worker can have minimal privileges (INSERT only on the queue table) but the backend w…

Do you have more info on the WALRUS stuff? Is that part of the Elixir lib?

Re: Realtime Postgres Row Level Security

#20

Post by golemiprague »

kiwicopple wrote: post #2

Hey HN, Supabase is an open source Firebase alternative. We're building the features of Firebase using enterprise-grade open source tools. We're particularly focused on scalability. We take proven tools like Postgres, and we make them as easy to use as Firebase. Today, Supabase is adding Row Level Security (RLS) to our Realtime engine. The linked blog post goes into depth around the technical implementation, so I’ll…

Looking at Hasura they provide also column level security and option to add custom logic via actions. Is this something planned in the future and in general how would you compare the two systems and what are the advantages or disadvantages of Supabase?
Post reply on HN