Applying a row-level-security policy on a view

Question

I'm a bit new to the whole Supabase thing. I'm trying to apply a policy to a view that I created. I'm not sure if it's even possible but I can't see why it wouldn't be. I don't think that this question is a duplicate of this question because of the Supabase aspect.

I tried to use the following example but instead of using a table, I used a view.

create policy "Team members can update team details if they belong to the team."
  on teams_view
  for select using (
    auth.uid() in (
      select user_id from members
      where team_id = id
    )
  );

Unfortunately, postgresql complains that "teams_view" is not a table, which is ofcourse correct. The question is: is there a correct way of applying a policy on views and if so, how would this look like?

Answer 1

That is easy to do with the security_invoker option on views available since v15. That will make PostgreSQL check permissions on the underlying tables as the user that uses the view, as well as use the row level security policy for the user.

In the words of the documentation:

If the view has the security_invoker property set to true, access to the underlying base relations is determined by the permissions of the user executing the query, rather than the view owner. Thus, the user of a security invoker view must have the relevant permissions on the view and its underlying base relations.

If any of the underlying base relations is a security invoker view, it will be treated as if it had been accessed directly from the original query. Thus, a security invoker view will always check its underlying base relations using the permissions of the current user, even if it is accessed from a view without the security_invoker property.