CODEWITHSUNDEEP
supabasesupabase-database
Vladimirzb
Vladimirzb

Reputation: 497

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

Body

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.

Context:

Why Materialized View?

I opted for a materialized view for two main reasons:

  1. Performance: Materialized views store the result of the query, making data retrieval faster, especially when dealing with complex joins and calculations.

  2. 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:

  1. 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?

  2. 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!

Upvotes: 1

Views: 1613

Answers (2)

Diep Gepa
Diep Gepa

Reputation: 515

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

Upvotes: 0

dshukertjr
dshukertjr

Reputation: 18680

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)

Upvotes: 2

Related Questions