Securing Materialized Views

RLS🟢SQL

I'm new to materialized views (MV) and am trying to figure out how to lock them down. I understand I can't add RLS to the MV and found that a common practice is to:

REVOKE ALL ON public.my_mv FROM PUBLIC;
REVOKE ALL ON public.my_mv FROM anon;
REVOKE ALL ON public.my_mv FROM authenticated;

REVOKE ALL ON public.my_mv FROM PUBLIC;
REVOKE ALL ON public.my_mv FROM anon;
REVOKE ALL ON public.my_mv FROM authenticated;

and then create a role that will be granted SELECT privileges to the MVs:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'secure_view_owner') THEN
    CREATE ROLE secure_view_owner NOLOGIN;
  END IF;
END $$;

GRANT SELECT ON public.my_mv TO secure_view_owner;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'secure_view_owner') THEN
    CREATE ROLE secure_view_owner NOLOGIN;
  END IF;
END $$;

GRANT SELECT ON public.my_mv TO secure_view_owner;

then create a regular view that is basically a secure wrapper of the MV and grant it priviledges:

CREATE OR REPLACE VIEW public.my_mv_secure AS
SELECT *
FROM public.my_mv
WHERE
    company_id IN (
        SELECT company_id
        FROM public.membership
        WHERE
            user_id = auth.uid()
    );

ALTER VIEW public.my_mv_secure SET(security_barrier = true);
ALTER VIEW public.my_mv_secure SET(security_invoker = on);

REVOKE ALL ON public.my_mv_secure FROM PUBLIC;
GRANT SELECT ON public.my_mv_secure TO authenticated;

CREATE OR REPLACE VIEW public.my_mv_secure AS
SELECT *
FROM public.my_mv
WHERE
    company_id IN (
        SELECT company_id
        FROM public.membership
        WHERE
            user_id = auth.uid()
    );

ALTER VIEW public.my_mv_secure SET(security_barrier = true);
ALTER VIEW public.my_mv_secure SET(security_invoker = on);

REVOKE ALL ON public.my_mv_secure FROM PUBLIC;
GRANT SELECT ON public.my_mv_secure TO authenticated;

When I go to the supabase console and look at my_mv_secure, I don't see any rows. I want to confirm i'm doing this correctly/securely and the reason I don't see them in the console is because their is no auth.uid() set?

Securing Materialized Views

RLS🟢SQL

I'm new to materialized views (MV) and am trying to figure out how to lock them down. I understand I can't add RLS to the MV and found that a common practice is to:

REVOKE ALL ON public.my_mv FROM PUBLIC;
REVOKE ALL ON public.my_mv FROM anon;
REVOKE ALL ON public.my_mv FROM authenticated;

REVOKE ALL ON public.my_mv FROM PUBLIC;
REVOKE ALL ON public.my_mv FROM anon;
REVOKE ALL ON public.my_mv FROM authenticated;

and then create a role that will be granted SELECT privileges to the MVs:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'secure_view_owner') THEN
    CREATE ROLE secure_view_owner NOLOGIN;
  END IF;
END $$;

GRANT SELECT ON public.my_mv TO secure_view_owner;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'secure_view_owner') THEN
    CREATE ROLE secure_view_owner NOLOGIN;
  END IF;
END $$;

GRANT SELECT ON public.my_mv TO secure_view_owner;

then create a regular view that is basically a secure wrapper of the MV and grant it priviledges:

CREATE OR REPLACE VIEW public.my_mv_secure AS
SELECT *
FROM public.my_mv
WHERE
    company_id IN (
        SELECT company_id
        FROM public.membership
        WHERE
            user_id = auth.uid()
    );

ALTER VIEW public.my_mv_secure SET(security_barrier = true);
ALTER VIEW public.my_mv_secure SET(security_invoker = on);

REVOKE ALL ON public.my_mv_secure FROM PUBLIC;
GRANT SELECT ON public.my_mv_secure TO authenticated;

CREATE OR REPLACE VIEW public.my_mv_secure AS
SELECT *
FROM public.my_mv
WHERE
    company_id IN (
        SELECT company_id
        FROM public.membership
        WHERE
            user_id = auth.uid()
    );

ALTER VIEW public.my_mv_secure SET(security_barrier = true);
ALTER VIEW public.my_mv_secure SET(security_invoker = on);

REVOKE ALL ON public.my_mv_secure FROM PUBLIC;
GRANT SELECT ON public.my_mv_secure TO authenticated;

Securing Materialized Views

Similar Threads

Securing Materialized Views

Similar Threads

Similar Threads

Similar Threads