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:
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!
- 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).
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!
