Hello everyone,
I'm working on a project where I need to calculate the final cost (gasto) for each expense by applying various taxes. I also need to implement Row-Level Security (RLS) to restrict data access based on the user ID.
gastos that stores individual expenses.taxesValues that stores different types of taxes.gastosAppliedTaxes, establishes a many-to-many relationship between gastos and taxesValues, indicating which taxes are applied to each expense.final_gastos to calculate the final cost (gasto) for each expense after applying the taxes. The view essentially joins gastos, taxesValues, and gastosAppliedTaxes to perform this calculation.I opted for a materialized view for two main reasons:
Performance: Materialized views store the result of the query, making data retrieval faster, especially when dealing with complex joins and calculations.
Row-Level Security (RLS): Unlike regular views, materialized views in PostgreSQL allow for the application of RLS policies. This is crucial for my application as I need to restrict data based on the user ID.
Materialized Views for RLS:
Given the above, I switched from using a view to a materialized view called final_gastos so that I can apply RLS. Is this a good approach? Are there any downsides or better alternatives?
Supabase UI Limitations:
I noticed that the Supabase UI doesn't show policies for materialized views and lacks a UI for setting policies like it does for normal tables. Is this a limitation of Supabase, or am I missing something?
Any insights or recommendations would be greatly appreciated.
Thank you!
RLS policies are set on tables and not views. When you have a view, the RLS of underlying tables are respected if the view is a security invoker. I believe in Postgres, materialized views do not support RLS at the moment.
You can create a security invoker view like this:
CREATE VIEW .. WITH (security_invoker=on)