Different RLS (Row Level Security) between listing and viewing.

[enesozturk]

Hey everyone.

In Supabase, I have a table called posts. This table has columns that related to users; author which keeps the id of the user who created that post.

I created a Policy to enable RLS for the "posts" table. Now users only can list their own items. But I also have a post viewer page in my web app: x.com/posts/{POST_UID}. So on this page, I'm getting the posts in the following way:

const { data } = await supabase
    .from("fine-tuning")
    .select("*")
    .eq("uid", params.uid);

But the problem is, this page should be public and can be visible by anyone. Since posts table have policy. I cannot do that.

Is there any way to "list only users items, if they have a where clause with post UID, provide it".

I don't have expertise in SQL but I'm sure there are different solutions to provide it. I'm just curious about how to do this with Policies if it's possible.

Thank you!

[GaryAustin1]

A default view will ignore the RLS on the table. I would create a view for your table and call that if public.

But if all rows can be seen by the public why do you have your RLS for select at all? Just use a filter when you just want a users rows.

[enesozturk]

Yeah, as a result, I needed to come up with a filtering way. I just wanted to make them secure at the database level.

As I mentioned I'm not an expert on it and not sure if it's possible to handle both cases with policies somehow. I just didn't find them safe to make my table public for the SELECT method.

[GaryAustin1]

You can make your table select policy just for authenticated users.
Then only signed in users can view the table with or without the filter.
Other operations can use auth.uid() to protect updates/inserts to only be by the specific user.

[GaryAustin1]

Maybe I am missing what you are actually trying to do or protect.
I assume your viewer shows the entire table, if not then maybe be clear on what specifically the post viewer can do or not do versus a user viewing their own row.

[cohogan]

Any chance you found a solution, @enesozturk? I'm also looking for something exactly like this from Firebase, but with RLS and Supabase. Ideally it uses RLS and not an RPC because I'll be subscribing to realtime updates from my front end. Thank you so much!

[gc-ft]

There is no exact solution to the problem, because RLS has nothing to do with limiting the kind of queries that are run but just which row is returned.

To make matters worse, this feature would require a change in PostgREST not in Supabase...

I do understand why people ask for this kind of control every couple of weeks. I wrote a small post about an idea of mine a few days ago which achieves what you want SOMEWHAT, in the sense that results can be always limited to just the FIRST ROW of the query without RLS. But I doubt this will work in connection with Realtime.