4 ms·

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 broadc

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
    - SahAssar 5y ago
      > 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?"
      - kiwicopple 5y ago
        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.
        SahAssar 5y ago
        No problem, totally understandable!
  - oliverrice 5y ago
    > does this also work for tables where the GRANT hides certain columns from different users? Yes, it does! Any columns that are not selectable by the user's role are hidden from the JSON `response` and `old_response` keys and are not allowed for user defined filtered. Here is where the columns are checked to see if they are selectable https://github.com/supabase/walrus/blob/f030688d9d94520b916db92b4a92940bc0172155/sql/walrus--0.1.sql#L315 https://github.com/supabase/walrus/blob/f030688d9d94520b916d... And here is there they are filtered out https://github.com/supabase/walrus/blob/f030688d9d94520b916db92b4a92940bc0172155/sql/walrus--0.1.sql#L394 https://github.com/supabase/walrus/blob/f030688d9d94520b916d...
    - SahAssar 5y ago
      Thanks! Seems like you guys thought of everything :)

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
  - SahAssar 5y ago
    > 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?"
    - kiwicopple 5y ago
      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.
      - SahAssar 5y ago
        No problem, totally understandable!
- oliverrice 5y ago
  > does this also work for tables where the GRANT hides certain columns from different users? Yes, it does! Any columns that are not selectable by the user's role are hidden from the JSON `response` and `old_response` keys and are not allowed for user defined filtered. Here is where the columns are checked to see if they are selectable https://github.com/supabase/walrus/blob/f030688d9d94520b916db92b4a92940bc0172155/sql/walrus--0.1.sql#L315 https://github.com/supabase/walrus/blob/f030688d9d94520b916d... And here is there they are filtered out https://github.com/supabase/walrus/blob/f030688d9d94520b916db92b4a92940bc0172155/sql/walrus--0.1.sql#L394 https://github.com/supabase/walrus/blob/f030688d9d94520b916d...
  - SahAssar 5y ago
    Thanks! Seems like you guys thought of everything :)