Limit only to functions - more secure than row-level?

[nruffilo]

I'm having some difficulty with row level security because I want a complex security model. I'm doing a task app, but you can have groups and other people in your groups can take ownership of tasks and assign people tasks, etc - so a user doesn't just manage their own data.

I can fairly easily do this within the function that i've been writing to ensure that the user has permissions, but I'm finding it very difficult to figure out how to go about writing the row-level security.

So - my big question is, is there a way that I can disable ALL table access and only allow access via functions? That way I could ensure that security was maintained?

Or - is there a way to handle complex row-level security (for example, writing to a "Task" table might mean that the userid is within the same group as the existing user, etc...

[steve-chavez]

So - my big question is, is there a way that I can disable ALL table access and only allow access via functions? That way I could ensure that security was maintained?

Yes. You can create a private schema where all your tables live and then only have functions on the public schema. This way you only have to worry about execute privileges on your functions - and not worry about crud privileges on your tables, because they're invisible to clients.

This is indeed the most strict/secure model under the schema isolation concept.

Or - is there among other thingsa way to handle complex row-level security (for example, writing to a "Task" table might mean that the userid is within the same group as the existing user, etc...

That being said, this should be possible. Perhaps you can share a sample schema so we can point you in the right direction?

(Some policies examples here: https://supabase.io/docs/guides/auth#policy-examples)

Only-functions is a valid model, but it takes more work since you'll have to create a function for each crud operation per table. RLS policies would be more simple.

[nruffilo]

Thank you for your great response. The policy examples were actually very helpful and might be the way I go, although I am liking the whole "more control" by using functions. It might be more work, but it will ensure that I don't mess up a policy and allow bad actors, and makes the back-end more like an API and less like direct database access - which is what I'm used to.

The examples meet my needs, but since you asked for examples, here's a complex table structure:

Task (TaskId, TaskName, Task Description...)
UserTask (UserId, TaskId)
Group (GroupId)
UserGroup (GroupId, UserId, RoleId)

I want anyone in a group with the RoleId of 3 to be able to create a task for any other person in the group.

[universse]

One potential downside is when you need to update policies in the future. You would need to update your access control logic in all your functions, assuming there are a lot of those. In contrast, with policy, there is a centralized place to update such logic, and your query can be kept simple eg. select * from tasks.

For many-to-many relationships like your eg, this could help https://supabase.io/docs/guides/auth#policies-with-security-definer-functions