Securing access to database functions only
I am considering only having database functions in the public schema to ensure that the frontend application we are building receives very specific data already formatted as JSON. My hope is to prevent any direct table access through the API which reduces the number of decisions my frontend team needs to make. This should also prevent n+1 queries and any other performance issues that may happen when calling the API using JS. My question is, if I provide access using RLS to the underlying tables in a different schema to my users with the idea that they will only access the data using the database functions, is it possible for those users to access the direct tables in the non-public tables through the API?
7 Replies
If you do not put the schema in the permitted schema API settings then users will have no direct access. Your public rpc functions, will have access and enforce RLS if not security definer type.
Excellent news. Would you please confirm what you mean by "if not security definer type"?
@garyaustin
You can make a rpc called Postgres function "security definer" and it will run as the owner (usually super admin) and bypass RLS. If using the dashboard UI you set to security invoker which is default if you specify nothing. Invoker runs with the user's permissions.
Oh if you create alternate schemas you will need to set up grants on them so they can be accessed by PostgREST roles. That is documented somewhere I believe.
Hrm, I don't want to bypass RLS though. I'm not using the dashboard UI at all. It sounds like it is possible, I just need to research a little bit more. I wanted to confirm if the theory was plausible though which you have done, thank you @garyaustin!
Yes, no problem doing it and enforcing RLS and no direct API access.
I'm planning to create a use case as well to confirm it all works as expected, thank you again.
Only functions is a good approach, also previously discussed on https://github.com/supabase/supabase/discussions/1360
This should also prevent n+1 queries and any other performance issues that may happen when calling the API using JS.Note that every call to the API always generates a single SQL query, so no risk of n + 1.
GitHub
Limit only to functions - more secure than row-level? · Discussion ...
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 ow...