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

[vladimirzb]

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:

• 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:

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!

[GaryAustin1]

You can enforce RLS on regular views now with security_invoker. https://www.postgresql.org/docs/current/sql-createview.html
The UI is limited to displaying views and you need to use SQL to edit them.

[vladimirzb]

Thank you! I implemented this in my Supabase project recently and it has worked perfectly so far!

[benjaminpreiss]

I am wondering, though: If I create a view to "precalculate" a complex query to have it ready when needed, does RLS implicate that the view is recalculated upon each query? (Due to the fact that RLS on views is based on underlying tables)

That would undermine the idea of "precalculating" the query to have it ready upon request...

[stamler]

I'm fairly certain PostgreSQL as of v15 does NOT support RLS on materialized views, in contrast with regular views which do using security_invoker = true.

[sanchezcarlosjr]

As @stamler said, the current Postgres version doesn't support RLS on materialized views and will not be available anytime soon (https://postgrespro.com/list/thread-id/2400463).

I proposed a workaround design pattern for managing authorization when we use materialized views or any other object (e.g. FWD) in Postgres that doesn't support RLS by leveraging functions and views.

1. Create a Materialized View Accessible Only by Postgres role in a hidden schema:

CREATE SCHEMA analytics;
CREATE MATERIALIZED VIEW analytics.analytics_mv AS
SELECT
    user_id,
    COUNT(*) AS total_records,
    SUM(amount) AS total_amount
FROM
    public.sensitive_table
GROUP BY
    user_id;

REVOKE ALL ON analytics.analytics_mv FROM PUBLIC, ANON, AUTHENTICATED;
GRANT SELECT ON analytics.analytics_mv TO postgres;

2. Create a Security-Definer Function to Enforce RLS-like Behavior:

CREATE OR REPLACE FUNCTION analytics.get_user_analytics_mv()
RETURNS SETOF analytics_mv AS $$
BEGIN
    RETURN QUERY SELECT * FROM analytics.analytics_mv 
    -- RLS-like behavior
    WHERE user_id = auth.uid() AND auth.role() = 'authenticated';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = '';

GRANT EXECUTE ON FUNCTION analytics.get_user_analytics_mv() TO authenticated;

3. Create a Secure View That Utilizes the Function:

CREATE VIEW public.analytics WITH (security_invoker=on) AS
SELECT * FROM analytics.get_user_analytics_mv();

Complete POC SQL Script

-- Create 'sensitive_table' in the 'public' schema
CREATE TABLE public.sensitive_table (
    id SERIAL PRIMARY KEY,
    user_id uuid NOT NULL,
    amount NUMERIC NOT NULL,
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    constraint sensitive_table_user_id_fkey foreign key (user_id) references auth.users (id)
);

-- Insert sample data into 'sensitive_table'
INSERT INTO public.sensitive_table (user_id, amount) VALUES
('1c314945-6f17-4037-8130-6a23d3f3983e', 100.00),
('1c314945-6f17-4037-8130-6a23d3f3983e', 150.50),
('1c314945-6f17-4037-8130-6a23d3f3983e', 200.00),
('c4b6d34f-a414-477f-b6db-40d232a3c8e0', 300.75),
('c4b6d34f-a414-477f-b6db-40d232a3c8e0', 50.25);

-- Create 'analytics' schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS analytics;

-- Drop existing materialized view if it exists for idempotency
DROP MATERIALIZED VIEW IF EXISTS analytics.analytics_mv CASCADE;

-- Create the materialized view 'analytics_mv'
CREATE MATERIALIZED VIEW analytics.analytics_mv AS
SELECT
    user_id,
    COUNT(*) AS total_records,
    SUM(amount) AS total_amount
FROM
    public.sensitive_table
GROUP BY
    user_id;

REVOKE ALL ON analytics.analytics_mv FROM PUBLIC, anon, authenticated;

CREATE OR REPLACE FUNCTION analytics.get_user_analytics_mv()
RETURNS SETOF analytics.analytics_mv AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM analytics.analytics_mv
    WHERE analytics.analytics_mv.user_id = auth.uid() AND auth.role() = 'authenticated';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = '';


ALTER FUNCTION analytics.get_user_analytics_mv() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION analytics.get_user_analytics_mv() TO authenticated;


CREATE VIEW public.analytics AS
SELECT * FROM analytics.get_user_analytics_mv();

-- Revoke all access to the view from PUBLIC
REVOKE ALL ON public.analytics FROM PUBLIC;

-- Grant SELECT access on the view to the 'authenticated' role
GRANT SELECT ON public.analytics TO authenticated;

DROP TABLE IF EXISTS public.sensitive_table CASCADE;
DROP FUNCTION IF EXISTS analytics.get_user_analytics_mv() CASCADE;

-- Impersonate postgres
SELECT * FROM analytics.analytics_mv

user_id	total_records	total_amount
c4b6d34f-a414-477f-b6db-40d232a3c8e0	2	351.00
1c314945-6f17-4037-8130-6a23d3f3983e	3	450.50

-- Impersonate user c4b6d34f-a414-477f-b6db-40d232a3c8e0
SELECT * FROM public.analytics

user_id	total_records	total_amount
c4b6d34f-a414-477f-b6db-40d232a3c8e0	2	351.00

-- Impersonate user 1c314945-6f17-4037-8130-6a23d3f3983e
SELECT * FROM public.analytics

user_id	total_records	total_amount
1c314945-6f17-4037-8130-6a23d3f3983e	3	450.50

-- Impersonate user 1c314945-6f17-4037-8130-6a23d3f3983e and try to query other user
SELECT * FROM analytics where user_id = 'c4b6d34f-a414-477f-b6db-40d232a3c8e0';

No rows.