Using Materialized Views for RLS in Supabase: Best Practices and UI Limitations [closed]

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 6 months ago.

Improve this question

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.

Context:

I have a table called gastos that stores individual expenses.
I have another table called taxesValues that stores different types of taxes.
A third table, gastosAppliedTaxes, establishes a many-to-many relationship between gastos and taxesValues, indicating which taxes are applied to each expense.
I initially used a view called 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.

Why Materialized View?

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.

Questions:

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?

dshukertjr · Accepted Answer · 2023-10-02 06:20:14Z

2

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)

answered Oct 2, 2023 at 6:20

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diep Gepa · Accepted Answer · 2025-01-13 11:16:54Z

-2

RLS

BEGIN;
CREATE POLICY "all" ON "public"."table_a" USING ( user_id= auth.uid() );
COMMIT;

Materialized Views

hypothesis that: m_view_1 has column user_id of table_a with foreign key

Mix view (repeat join table_a)

create or replace view
  view_1 with(security_invoker= true) as
select m_view_1.* from m_view_1 join table_a on m_view_1.user_id= table_a.user_id;

Conclusion

Well the problem is we can m_view_1 query the entire record with supabase.
Let's block requests to /rest/v1/m_view_1 or even /rest/v1/m_view_* with nginx or kong. https://www.nodejsauto.com/2025/01/using-materialized-views-for-rls-in.html

answered Jan 13 at 11:16

Diep Gepa

5215 silver badges5 bronze badges

Collectives™ on Stack Overflow

Using Materialized Views for RLS in Supabase: Best Practices and UI Limitations [closed]

Context:

Why Materialized View?

Questions:

2 Answers 2

Comments

RLS

Materialized Views

Mix view (repeat join table_a)

Conclusion

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Context:

Why Materialized View?

Questions:

2 Answers 2

Comments

RLS

Materialized Views

Mix view (repeat join table_a)

Conclusion

Comments

Related