Implementing Row Level Security with Dynamic Permissions in Supabase for Role Management

Question

Imagine two tables:

Website

name	type
id	int

Website Contributor

name	type	description
account_id	uuid	FK to supabase's authenticated users table; auth.uid()
website_id	int	FK to website
role	enum	OWNER or EDITOR

The only scenarios which a website_contributor should be inserted is as such:

• When a new website is created, it will auto-insert a website_contributor to add the current user as OWNER.
• When the current user requests to add a new editor into a website, insert website_contributor with the other user and EDITOR role.
  (The current user must be the website's owner for this to succed)

---

Question is, how would I go about to handle these scenarios with Row Level Securities or Edge Functions?

If I use edge functions, how would I still make the RLS permissive to my edge function, but restrictive enough for security reasons?

---

I am new to Supabase and BaaS in general.

Currently I am thinking of creating a BFF, and a secret super-user account.
Whereby the BFF would use the super-user account to perform website_contributor operations, and Supabase would set RLS to only allow the super-user to perform those operations.
But I would like to avoid creating a BFF if possible.