Support WALRUS (Postgres RLS) Integration with Realtime Server

[w3b6x9]

Feature request

Is your feature request related to a problem? Please describe.

Currently, Realtime server sends all database changes to all connected clients despite Postgres Row Level Security policies. This poses security concerns when developers wish to broadcast database changes containing sensitive data to an authorized subset of connected clients based on tables with RLS enabled and row security policies.

Realtime server will integrate WALRUS (Write Ahead Log Realtime Unified Security), which means there are some changes that lib clients need to make in order to support this new security functionality.

Describe the solution you'd like

The following changes will need to be made:

• Pass user auth token, if available, as channel params with key user_token when subscribing client to Realtime channel.
• Update Realtime transformers to pass through changes when they're already arrays.
  Realtime w/ WALRUS will pass Postgres array types as [1, 2, 3], _int4, and ["a", "b", "c"], _text, instead of "{1,2,3}" (_int4/_text) so this can be forwarded without any transformations. However, clients should maintain backward compatibility so they should still be able to handle stringified Postgres array (e.g. "{1,2,3}") cases.

The changes have already been applied to supabase-js and realtime-js and their PRs can be referenced while making the necessary changes:

• Passing user auth token as user_token

feat: add user_token when creating realtime channel subscription #270

• Update Realtime transformers (including bug fixes)

feat: update transformers to accept already transformed walrus changes #107

fix: error parsing JSON when transforming array data types #113

The bug fixes include longstanding issues with transformers in realtime-js where range types are first JSON parsed (which results in an error sometimes due to Postgres' range exclusive and inclusive bounds) and how stringified (e.g. "{1,2,3}") array types are split (can't always split on "," in cases like _daterange). Please see PR for additional context.

Additional context

We're looking to launch WALRUS in Realtime at the end of November, and all developers using the JS client will have to do is version bump their supabase-js to v1.2.0, which contains all the changes described above.

Please reach out if there's any questions and definitely tag me to confirm PRs if you'd like! Thank you!

[acupofjose]

Progress:

• Realtime-csharp support for subscription parameters (supabase-community/realtime-csharp@6742690)
• Postgrest-csharp support for int[] (supabase-community/postgrest-csharp@5d200c7)
• Realtime-csharp support for WALRUS transforms (int[] and string[])
• Supabase-csharp pass user_token as channel initializing parameter (d518332)

[acupofjose]

@w3b6x9 is it possible to access a docker image that sends the realtime int[] in the new array format (instead of postgrest format)?

[w3b6x9]

@acupofjose yep, so I just pushed up some changes to https://github.com/supabase/realtime/tree/rls. You can spin up Postgres and Realtime with docker-compose.dev.yml. There's some setup after both are up and running:

• Generate a uuid and insert it in db's auth.users table
• Generate a JWT with that uuid under the sub key and use that as user_token when subscribing to channel. You can also use it as apikey when opening up a socket conn for dev purposes (it's normally the Supabase anon key in prod)

Feel free to use

bbb51e4e-f371-4463-bf0a-af8f56dc9a73

and

eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJzdXBhYmFzZSIsImlhdCI6MTYzMjI4MzE5MSwiZXhwIjoxNjYzODE5MjExLCJhdWQiOiJhdXRoZW50aWNhdGVkIiwic3ViIjoiYmJiNTFlNGUtZjM3MS00NDYzLWJmMGEtYWY4ZjU2ZGM5YTczIn0.imL7XhNMrS523vvdzQ93iRIw3OhjJutamLEoiZnJDbI

for your convenience. The above JWT was signed with the secret found in docker-compose.dev.yml.

[w3b6x9]

Just a heads up I just updated the toArray function in realtime-js to accommodate types like _text where Realtime is sending "{a,b,c}" which can't be parsed to JSON. This is only for backward compatibility reasons as WALRUS Realtime will be sending the actual array of strings so it'll just pass through toArray. Here's the PR: fix: error parsing JSON when transforming array data types #113

[acupofjose]

Thanks for the issue @w3b6x9! This is supported in 94b8b63 - looking forward to seeing this live upstream!

[w3b6x9]

@acupofjose wow that was fast! awesome work!