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

21–30 of 34 posts

Re: Realtime Postgres Row Level Security

#21

Post by BrandiATMuhkuh »

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…

I just want so say thank you. RLS is the feature I was waiting for. It's an impressive work you have done!

Re: Realtime Postgres Row Level Security

#22

Post by polycaster »

What are approaches to you prevent DoS attacks with such an architecture? It seems trivial for an attacker to generate super expensive queries en masse.

Strict row limits could bring some relief but don't solve the problem.

I bet there are solutions for this but it's not very obvious. Would someone briefly explain?

Re: Realtime Postgres Row Level Security

#23

Post by steve76 »

polycaster wrote: post #22

What are approaches to you prevent DoS attacks with such an architecture? It seems trivial for an attacker to generate super expensive queries en masse. Strict row limits could bring some relief but don't solve the problem. I bet there are solutions for this but it's not very obvious. Would someone briefly explain?

You can put traefik in front of it and cache. Then setup a max limit on query execution. I don't use REST. Everything I need I write out as a postgres function. The other side is monitoring. Setup logging and run fail2ban, or an alarm to kick the user and require manual oversight.

Re: Realtime Postgres Row Level Security

#24

Post by kiwicopple »

me_me_mu_mu wrote: post #15
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 wh…

Your use-case is completely plausible, and if there are any blockers then we would consider it a bug that needs fixing. Postgres + PostGIS + PostgREST are a perfect match, and over time we will make mapping a first class citizen with proper documentation. The Realtime server is just a layer on top of PostgreSQL, so I'm confident it will work out-of-the-box with PostGIS

I haven't seen a lot of mapping use-cases in Supabase _yet_, but there are some: https://geoexamples.com/svelte/2021/07/18/svelte-supabase-ma...

Re: Realtime Postgres Row Level Security

#25

Post by kiwicopple »

thelastbender12 wrote: post #17
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 ?

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

This is completely reasonable an almost encouraged. We give you a full PG database so that you can use it in any way you wish, with any combination of Supabase features that want or don't want.

For example, we have a lot of people who use Prisma with Supabase, rather than the APIs we provide.

Re: Realtime Postgres Row Level Security

#26

Post by kiwicopple »

cpursley wrote: post #19
michelpp wrote: post #13

Earlier quoted context omitted.

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?

WALRUS is all SQL, so you could use it with anything (probably with a bit of extra code-wrangling): https://github.com/supabase/walrus

Re: Realtime Postgres Row Level Security

#27

Post by kiwicopple »

polycaster wrote: post #22

What are approaches to you prevent DoS attacks with such an architecture? It seems trivial for an attacker to generate super expensive queries en masse. Strict row limits could bring some relief but don't solve the problem. I bet there are solutions for this but it's not very obvious. Would someone briefly explain?

Supabase uses Kong (https://konghq.com/kong/) as an API gateway, which has a Rate-limiting plugin (https://docs.konghq.com/hub/kong-inc/rate-limiting/)

Our platform is also behind Cloudflare to protect agains DDoS, and we hope to use this to add some smart caching for the API service

Re: Realtime Postgres Row Level Security

#28

Post by kiwicopple »

SahAssar wrote: post #12
kiwicopple wrote: post #10

Earlier quoted context omitted.

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

> this feature is only available for authenticated users

Every client is expected to provide an API key at a minimum. The API key is actually a long-lived "anon" JWT, so unauthenticated users can connect and listen to "public" changes (for example, you might want to add a table of stock prices which update every second)

> only queries the primary key

That's correct. We use PostgreSQL logical replication to capture changes, which requires PK's. This is expected for replication because otherwise there would be no way of definitely identifying an update/delete. IMO it's a good practice to add a PK to every table anyway (a simple "serial" will suffice), especially for auditing and historical analysis

Re: Realtime Postgres Row Level Security

#29

Post by SahAssar »

kiwicopple wrote: post #28
SahAssar wrote: post #12

Earlier quoted context omitted.

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?

> this feature is only available for authenticated users Every client is expected to provide an API key at a minimum. The API key is actually a long-lived "anon" JWT, so unauthenticated users can connect and listen to "public" changes (for example, you might want to add a table of stock prices which update every second) > only queries the primary key That's correct. We use PostgreSQL logical replication to capture ch…

> IMO it's a good practice to add a PK to every table anyway (a simple "serial" will suffice), especially for auditing and historical analysis

The question wasn't if a primary key was required, it was "does this also work for tables where the GRANT hides certain columns from different users?"

Re: Realtime Postgres Row Level Security

#30

Post by kiwicopple »

SahAssar wrote: post #29
kiwicopple wrote: post #28

Earlier quoted context omitted.

> this feature is only available for authenticated users Every client is expected to provide an API key at a minimum. The API key is actually a long-lived "anon" JWT, so unauthenticated users can connect and listen to "public" changes (for example, you might want to add a table of stock prices which update every second) > only queries the primary key That's correct. We use PostgreSQL logical replication to capture ch…

> IMO it's a good practice to add a PK to every table anyway (a simple "serial" will suffice), especially for auditing and historical analysis The question wasn't if a primary key was required, it was "does this also work for tables where the GRANT hides certain columns from different users?"

thanks - long week. I should read more carefully.

Inside a supabase project users can connect to their projects as either "anon" or "authenticated". The role information is passed inside a JWT.

I'll need to confirm with Oli (asleep right now) on the technical implementation but I believe either of these roles is assumed when running the RLS checks.

Post reply on HN