Using Materialized Views for RLS in Supabase

For many reasons we have to use Materialized Views, maybe to improve query performance.

I even designed a system using Materialized Views and created a 5 minute schedule to refresh them

Please give more comments from readers

### 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

I had performance issues with the above view design. I implemented and used the following view:

Mix view

create or replace view

  view_1 -- with(security_invoker= true) -- no longer important

  as

select

  m_view_1.*

from

  m_view_1

where

  (

    (

      (

        select

          auth.uid ()

      ) is null

    )

    and (

      (

        select

          auth.role ()

      ) is null

    )

  )

  or -- note: postgres role can be read

  m_view_1.user_id = (

    select

      auth.uid ()

  );