Realtime Postgres Row Level Security • Hacker News Board

Re: Realtime Postgres Row Level Security

#31

Post by oliverrice » Thu, Dec 02, 2021, 12:32 PM UTC

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?

> 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/f030688d9d94520b916d...

And here is there they are filtered out https://github.com/supabase/walrus/blob/f030688d9d94520b916d...

Re: Realtime Postgres Row Level Security

#32

Post by SahAssar » Thu, Dec 02, 2021, 12:38 PM UTC

oliverrice wrote: post #31

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?

> 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/f030688d9d94520b916d... And here is there t…

Thanks! Seems like you guys thought of everything :)

Re: Realtime Postgres Row Level Security

#33

Post by SahAssar » Thu, Dec 02, 2021, 12:38 PM UTC

kiwicopple wrote: post #30

SahAssar wrote: post #29

Earlier quoted context omitted.

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

No problem, totally understandable!

Re: Realtime Postgres Row Level Security

#34

Post by twoquestions » Thu, Dec 02, 2021, 2:17 PM UTC

polskibus wrote: post #9

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 ?

You can do business logic in stored procedures, functions and views.

This is extremely painful to debug and troubleshoot compared to normal code.