How to Protect Base Tables with Views? (view, security_invoker, authenticated user)
Hi guys. I'm a making a FlutterFlow app and use Supabase as backend. I'm not sure how to best protect my base tables and some of the columns. Here's my setup:
- Users have to log into the app, so I guess only authenticated users are at play.
- I have, for example, a reviews table with RLS policies (all tables have policies), for reviews table authenticated users can SELECT, INSERT, UPDATE, DELETE, but only their own rows as I use (( SELECT auth.uid() AS uid) = user_id) / (auth.uid() = user_id). I don't know what's the difference.
- I created a reviews_view as suggested by the Supabase AI with security_invoker = on and I was also suggested to REVOKE SELECT on the base table FROM public, anon, authenticated; and GRANT SELECT on the new view TO authenticated. However, when I REVOKE SELECT on base table FROM authenticated, I can't load data. From what I have gathered, it's because security_invoker = on does it from the point of view of the caller, and the authenticated user does not have SELECT on the base table to load anything.
- Then suggestions were to either 1) use security_barrier, security_invoker = off so it's done from view owner perspective (supabase admin) and it can be OK if RLS policies are correct. Is that true? or 2) write some helper function and the view should use the helper function to gather the data and then I have to restrict execute or something on the function (this is more complicated for me).
I currently have 1) in use while developing, but Supabase shows a red text "Unrestricted" (Data is publicly accessible via API as this is a Security definer view.), but does having RLS policies and REVOKE INSERT FROM public, anon, authenticated actually protects the data?
Could you help me out here, what is the set up to make sure the base data with all the records are not accessible, but only a view I create (occassionally only the rows readable where the user_id = authenticated user's ID).
Many many thanks in advance!
3 Replies
I've adapted the PostgREST schema isolation approach which works fine for me. Basically, you would only expose views, and RPC functions in the Data API, and hide the underlying tables in some kind of private schema.
I don't know what's the difference.Use
(SELECT auth.uid()) = user_id (see Supabase docs). Copplestone in this video suggests using SELECT to cache the auth.uid() for subsequent calls.
No clue about why you would want to use AS uid though.
However, when I REVOKE SELECT on base table FROM authenticated, I can't load data.Yes, because the AI is hallucinating here. You must at least grant access to
authenicated so that they can access the underlying tables that the view is referencing. I believe you should find a Permission denied error in the logs.
From what I have gathered, it's because security_invoker = on does it from the point of view of the callerCorrect.
Security_barrier, security_invoker = off
security_barrier incurs a performance penalty, it doesn't enforce RLS unless combined with security_invoker, and is primarily intended for enforcing WHERE clause to be fully checked before returning any value . You should stick to security_invoker = TRUE to enforce RLS.Thank you the detailed answer! This is my first Supabase project, so some of it is still over my head.
I currently have all data tables and views in the public section. Is it enough security if in RLS I set that only authenticated users can SELECT, INSERT, UPDATE, DELETE (when necessary) their own rows based on authenticated user ID?
In my reviews table I collect reviews people write, and I also attached a personal_note column to the table which should be visible only to the given user in the app.
In one instance, I need to query the reviews table to display a list of all reviews on one page — so I guess I can't filter out personal_note when querying the table. However, if I make a view of the reviews table I can set it so that personal_note is only shown if the user_id is your own. But since security_invoker = on, I need to give SELECT anyway to authenticated on the reviews table, so it's not much of a protection anyway, am I seeing this right?
Should I just make a separate personal_note table and query whenever specifically needed?
if I were to move my data tables to a private schema (which I assume means creating a new schema and it becomes private by REVOKE SELECT or REVOKE ALL ON private.reviews FROM public, anon, authenticated;), and then in SQL I would write code to create a public view that SELECT FROM private.reviews, GRANT SELECT on public view TO authenticated;, would that be the way to go?
What I don't get is,
If I REVOKE ALL on the private tables, how can users insert data into there? Do I just allow INSERT? What if they have to modify a review and need UPDATE? or DELETE when deleting a review? How does that differ from having the table in public and having views on it next to them in the same schema?
My goal is to only show the necessary data when querying something, with no access to raw data tables (so people can't view/scrape all reviews at once, see other people's personal_note values, etc.)
I currently have all data tables and views in the public section. Is it enough security if in RLS I set that only authenticated users can SELECT, INSERT, UPDATE, DELETE (when necessary) their own rows based on authenticated user ID?Firstly, you would have to create a custom, private schema: Thereafter, you can move your tables to the new schema. As for access privileges, see the docs for using custom schemas. Replace
myschema with the name of your actual schema. In your situation, you would only want grant access to the authenticated role, and optionally, service_role if you need to bypass RLS from within a server context (and only server; it should never be used in the client).
so I guess I can't filter out personal_note when querying the table.Correct. The way RLS works is that you either grant access to the entire row, or nothing—there is no in-between. For this reason, if you search this forum, and Supabase discussion elsewhere, you'll always stumble upon Gary Austin's suggestion to separate the tables. Basically, you should not mix public- and private-columns in the same table. Other options such as using CLS is discouraged (see Supabase notes), and only operates on roles, not individual users.
However, if I make a view of the reviews table I can set it so that personal_note is only shown if the user_id is your own.Can you elaborate on this part?