6 ms·

Realtime Postgres Row Level Security

169 points by kiwicopple 5y ago

kiwicopple 5y ago
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 just give a recap for the people who jump straight to comments (like me). Supabase was launched here on HN when we open sourced our Realtime engine[0] - an Elixir server which clients (i.e. website visitors/users) can connect to via websockets and receive a stream of PostgreSQL changes. The server receives those changes via a logical replication slot - the same system that PostgreSQL uses for replicating to other databases. To achieve RLS we added a few SQL functions, the main one is apply_rls[1] which the stream is filtered through. For every user connected to the Elixir server, the Postgres function checks if they have access to the database change and appends an array of allowed user IDs. The Realtime server then delivers the change to the user only if the connected user is matched in this array. This one has been a long time coming, and it's one of the reasons why we have maintained our "beta" badge for so long. A few of the team will be here to answer any questions - my cofounder @awalias and @steve-chavez from PostgREST, @inian, @wenbo and @1_over_n [0] Realtime Show HN: https://news.ycombinator.com/item?id=22114560 https://news.ycombinator.com/item?id=22114560) [1] SQL function: https://github.com/supabase/realtime/blob/master/server/priv/repo/migrations/20211124070109_enable_realtime_apply_rls_function_postgrest_9_compatibility.exs https://github.com/supabase/realtime/blob/master/server/priv...
- me_me_mu_mu 5y ago
  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
  - kiwicopple 5y ago
    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-maps.html/ https://geoexamples.com/svelte/2021/07/18/svelte-supabase-ma...
- golemiprague 5y ago
  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?
- BrandiATMuhkuh 5y ago
  I just want so say thank you. RLS is the feature I was waiting for. It's an impressive work you have done!
tehnorm 5y ago
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.
- pgroves 5y ago
  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-level permissions. The docs are all about auth for users that are already in there.
  - kiwicopple 5y ago
    In Supabase we use a separate Auth server [0]. This stores the user in an `auth` schema, and these users can login to receive a JWT. Inside the JWT is a "role", which is, in fact, a PostgreSQL role ("authenticated") that has certain grants associated to it, and the user ID (a UUID). Inside your RLS Policies you can use anything stored inside the JWT. My cofounder made a video [1] on this which is quite concise. Our way of handling this is just an extension of the PostgREST Auth recommendations: https://postgrest.org/en/v9.0/auth.html https://postgrest.org/en/v9.0/auth.html [0] Auth server: https://github.com/supabase/gotrue https://github.com/supabase/gotrue [1] RLS Video: https://supabase.com/docs/learn/auth-deep-dive/auth-row-level-security https://supabase.com/docs/learn/auth-deep-dive/auth-row-leve...
  - michelpp 5y ago
    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.
Ericson2314 5y ago
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 through certain indices. But this is in fact good for row level security too! Functional programming is deeply related to capability-based security, in that both try to get rid of all ambient authority and mediate all actions through some sort of reference. Rust, with it's many types of reference makes the relationship even clearer. From these vantage points, it is very natural to use data structure both to make things performant and enforce certain invariant. Indices are the closes data structures in RDBMSs, because they do promote only certain access patterns. With the rule that queries must go through the indices, they enforce those access pattern, and thus we have the same proven tool. Combine them with views for filtering out data, (which we can think in conjunction with more indices of as sort of a curried indexes Map<UserID, Map<Stuff, OtherStuff>> even if they aren't implemented that way), and now we have very powerful tools for guaranteeing performance and security. As an icing on the cake, I think enforcing limited access patterns could get us closer to that long-sought goal of a RMBDS that automatically knows how to distribute itself. The limited access patterns open up possible options, because we which of the pathological joins that every sharding possibility run into are no longer allowed. Reusing the same query planner tricks, "cardinality analysis" type stuff can be used to choose between those possibilities. Here's a worked out concrete example: Tables: User: { k: UserID PK, t: TennentId, n: Name, isAdmin: Bool }, Todo: { k: TodoID PK, u: UserID, d: Date, m: Msg } Indices and Views: # exists_classical means proof of existence but we don't care which. Map<(t: TennentId, exists_classical u: User. u.t = t, u.isAdmin = true), {u2 <- User, u2.t = t}> Map<(t: TennentId, exists_classical u: User. u.t = t, u.isAdmin = true), {t <- Todo, u2 <- User, t.u = u2.k, u2.t = t}> ^ that's a joined view with compression syntax # exists_constructive means need to show existence and we do see what item witness it Map<t: TennantID, Map<uk: UserID, exist_constructive u: User. u.t = t, u.k = uk>> Map<(t: TennentId, Map<uk: UserId, exists_classical u: User. u.t = t, u.k = uk, {t <- Todo, t.u = u.k}>> The more worked out system would probably allow tupling stuff to not repeat the "Map<(t: TennentId," at the beginning. A few things to note: 1. When we do a comprehension syntax, we are "returning the whole table" i.e. an unconstrained view that can be queried in an arbitrary way. A joined table has to be joined in the given way, but is otherwise unconstrained. 2. exist_constructive is the same as a { ... } comprehension, but the cardinality must be 1 as proven by the unique constraints. exists_classical is a comprehension for an arbitrary number of stuff, that is then truncated in the type theory sense. https://xenaproject.wordpress.com/2021/05/19/the-trace-of-an-endomorphism-without-picking-a-basis/ https://xenaproject.wordpress.com/2021/05/19/the-trace-of-an... is a good discussion of an example of this truncation. 3. Normally one puts the TennentID on every table, even though it is denormalizing. Now the access patterns enforce this. Clearly this is a few PhD theses away from actually existing :), but all the embryonic theory comes from good well-established things, which makes me confident.
SahAssar 5y ago
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 also send the allowed groups in the NOTIFY so that the program could filter without needing a separate PG connection per each subscriber. In my testing it scales very well. Anyone from supabase that can comment why LISTEN/NOTIFY triggers were not used, and why WS was used over SSE when the communication is not bi-directional? It'd be interesting to hear your thoughts.
- kiwicopple 5y ago
  > 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 we will add other multiplayer useful features (like presence)
  - SahAssar 5y ago
    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?
    - kiwicopple 5y ago
      > 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
polycaster 5y ago
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?
- steve76 5y ago
  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.
- kiwicopple 5y ago
  Supabase uses Kong (https://konghq.com/kong/ https://konghq.com/kong/) as an API gateway, which has a Rate-limiting plugin (https://docs.konghq.com/hub/kong-inc/rate-limiting/ 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

169 points by kiwicopple 5y ago

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 just give a recap for the people who jump straight to comments (like me). Supabase was launched here on HN when we open sourced our Realtime engine[0] - an Elixir server which clients (i.e. website visitors/users) can connect to via websockets and receive a stream of PostgreSQL changes. The server receives those changes via a logical replication slot - the same system that PostgreSQL uses for replicating to other databases. To achieve RLS we added a few SQL functions, the main one is apply_rls[1] which the stream is filtered through. For every user connected to the Elixir server, the Postgres function checks if they have access to the database change and appends an array of allowed user IDs. The Realtime server then delivers the change to the user only if the connected user is matched in this array. This one has been a long time coming, and it's one of the reasons why we have maintained our "beta" badge for so long. A few of the team will be here to answer any questions - my cofounder @awalias and @steve-chavez from PostgREST, @inian, @wenbo and @1_over_n [0] Realtime Show HN: https://news.ycombinator.com/item?id=22114560 https://news.ycombinator.com/item?id=22114560) [1] SQL function: https://github.com/supabase/realtime/blob/master/server/priv/repo/migrations/20211124070109_enable_realtime_apply_rls_function_postgrest_9_compatibility.exs https://github.com/supabase/realtime/blob/master/server/priv...

me_me_mu_mu 5y ago
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
- kiwicopple 5y ago
  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-maps.html/ https://geoexamples.com/svelte/2021/07/18/svelte-supabase-ma...
golemiprague 5y ago
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?
BrandiATMuhkuh 5y ago
I just want so say thank you. RLS is the feature I was waiting for. It's an impressive work you have done!

tehnorm 5y ago

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.

pgroves 5y ago
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-level permissions. The docs are all about auth for users that are already in there.
- kiwicopple 5y ago
  In Supabase we use a separate Auth server [0]. This stores the user in an `auth` schema, and these users can login to receive a JWT. Inside the JWT is a "role", which is, in fact, a PostgreSQL role ("authenticated") that has certain grants associated to it, and the user ID (a UUID). Inside your RLS Policies you can use anything stored inside the JWT. My cofounder made a video [1] on this which is quite concise. Our way of handling this is just an extension of the PostgREST Auth recommendations: https://postgrest.org/en/v9.0/auth.html https://postgrest.org/en/v9.0/auth.html [0] Auth server: https://github.com/supabase/gotrue https://github.com/supabase/gotrue [1] RLS Video: https://supabase.com/docs/learn/auth-deep-dive/auth-row-level-security https://supabase.com/docs/learn/auth-deep-dive/auth-row-leve...
- michelpp 5y ago
  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.

Ericson2314 5y ago

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 through certain indices. But this is in fact good for row level security too! Functional programming is deeply related to capability-based security, in that both try to get rid of all ambient authority and mediate all actions through some sort of reference. Rust, with it's many types of reference makes the relationship even clearer. From these vantage points, it is very natural to use data structure both to make things performant and enforce certain invariant. Indices are the closes data structures in RDBMSs, because they do promote only certain access patterns. With the rule that queries must go through the indices, they enforce those access pattern, and thus we have the same proven tool. Combine them with views for filtering out data, (which we can think in conjunction with more indices of as sort of a curried indexes Map<UserID, Map<Stuff, OtherStuff>> even if they aren't implemented that way), and now we have very powerful tools for guaranteeing performance and security. As an icing on the cake, I think enforcing limited access patterns could get us closer to that long-sought goal of a RMBDS that automatically knows how to distribute itself. The limited access patterns open up possible options, because we which of the pathological joins that every sharding possibility run into are no longer allowed. Reusing the same query planner tricks, "cardinality analysis" type stuff can be used to choose between those possibilities. Here's a worked out concrete example: Tables: User: { k: UserID PK, t: TennentId, n: Name, isAdmin: Bool }, Todo: { k: TodoID PK, u: UserID, d: Date, m: Msg } Indices and Views: # exists_classical means proof of existence but we don't care which. Map<(t: TennentId, exists_classical u: User. u.t = t, u.isAdmin = true), {u2 <- User, u2.t = t}> Map<(t: TennentId, exists_classical u: User. u.t = t, u.isAdmin = true), {t <- Todo, u2 <- User, t.u = u2.k, u2.t = t}> ^ that's a joined view with compression syntax # exists_constructive means need to show existence and we do see what item witness it Map<t: TennantID, Map<uk: UserID, exist_constructive u: User. u.t = t, u.k = uk>> Map<(t: TennentId, Map<uk: UserId, exists_classical u: User. u.t = t, u.k = uk, {t <- Todo, t.u = u.k}>> The more worked out system would probably allow tupling stuff to not repeat the "Map<(t: TennentId," at the beginning. A few things to note: 1. When we do a comprehension syntax, we are "returning the whole table" i.e. an unconstrained view that can be queried in an arbitrary way. A joined table has to be joined in the given way, but is otherwise unconstrained. 2. exist_constructive is the same as a { ... } comprehension, but the cardinality must be 1 as proven by the unique constraints. exists_classical is a comprehension for an arbitrary number of stuff, that is then truncated in the type theory sense. https://xenaproject.wordpress.com/2021/05/19/the-trace-of-an-endomorphism-without-picking-a-basis/ https://xenaproject.wordpress.com/2021/05/19/the-trace-of-an... is a good discussion of an example of this truncation. 3. Normally one puts the TennentID on every table, even though it is denormalizing. Now the access patterns enforce this. Clearly this is a few PhD theses away from actually existing :), but all the embryonic theory comes from good well-established things, which makes me confident.

SahAssar 5y ago

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 also send the allowed groups in the NOTIFY so that the program could filter without needing a separate PG connection per each subscriber. In my testing it scales very well. Anyone from supabase that can comment why LISTEN/NOTIFY triggers were not used, and why WS was used over SSE when the communication is not bi-directional? It'd be interesting to hear your thoughts.

kiwicopple 5y ago
> 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 we will add other multiplayer useful features (like presence)
- SahAssar 5y ago
  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?
  - kiwicopple 5y ago
    > 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

polycaster 5y ago

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?

steve76 5y ago
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.
kiwicopple 5y ago
Supabase uses Kong (https://konghq.com/kong/ https://konghq.com/kong/) as an API gateway, which has a Rate-limiting plugin (https://docs.konghq.com/hub/kong-inc/rate-limiting/ 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