The problem
As many of you already know, in my free time, or whenever the situation requires it, I do some no-code consulting sessions. These sessions tend to be quite varied, I meet a lot of cool no-code founders, big and small agencies, or even solo builders, simply put, each session is a presents a whole different challenge to solve.
Among these various challenges, one keeps popping up though. I’d say one way or another, the setup of the Supabase Auth with WeWeb, and the Role Based Access, is the most frequent issue I see you guys are dealing with. This made me undertand, that this is a problem worth solving.
The solution
I actually took a big step back, so I could look at it from a wider POV and I decided this can be solved. I took my experiences from talking to you, the WeWeb and Supabase users and created a tool, that might simplify the process of setting the RLS and Role Based Access to simply running a script and setting the rules in a very simple fashion.
I created a script, that does the following:
- Creates the tables required for WeWeb to work with Supabase, e.g. the roles table, and the user_role table
- Creates a trigger, that adds the newly generated users to the public users table, as reccomended by Supabase
- Sets up the initial RLS rules properly, so that the auth tables are locked down, but the users still can fetch their roles
- Creates a set of utility functions so that you don’t have to write a single line of SQL, other than the one invoking the function itself. These functions are:
rbac_authorize(<action>, <table_name>)- This function handles the whole RLS checking, you basically just need to set up four of these for the INSERT, DELETE, SELECT and UPDATE for each of your tables.
rbac_add_permission(<role>, <table>, <action>)- This one adds a permission to perform an action on the table, for the given role - so no extra SQL required again. You’re not touching the RLS anymore.
rbac_revoke_permission(<id>)- This function works the same way as the previous one, with the difference that it revokes the permission to perform an action on the table, for the given role - so no extra SQL required again. Still without touching any RLS.
select * from rbac_util_overview- This utility view shows you all the existing permissions and the corresponding roles to which they’re assigned. Here you also can get the<id>for revoking permissions.
4. There is also a whole option to delete and clean up all of the changes that the script has done, via running the queries in the “Danger zone”
Demo of the script
Where to get the script
You can DM me to get the latest version of this script file.
I also advice you to back up your project before running this, just in case, but I think there shall not be any conflicts, and if there is any, it is possible to remove the script easily.
Special thanks goes to @greger for actually inspiring me to make the script what it is today.
Edit: If anyone experiences the email missing, please make sure to check out your spam folder 
See you at the integration ;D