Security definer views should be allowed in certain conditions

[djgrant]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

"security definer" views are flagged as an error.

There are cases where RLS is not the right tool for the job, such as where the per-row checks are very expensive, or where certain columns should not be shown to the user. Postgres has two features I can think of that are helpful here:

1. A view with security_invoker=true that calls a security definer function
2. A view wth secuity_invoker=false, secuirty_barrier=true

This is touched on in #98, but the discussion focussed on dismissing the error.

I believe the correct behaviour is to allow security definer views if the following conditions are met:

1. security_barrier=true – this prevents predicate injection
2. grant is limited to select operation only – this prevents mutating the base table if the view is a simple view

Given the intention is to take away footguns from the user base, a warning that could be dismissed also seems fine.

Sketch

To verify the behaviour:

create schema app;

create table app.test (
  id uuid primary key default gen_random_uuid(),
  slug text unique
);

-- optionally
-- alter table app.test enable row level security;

create view app.test_v as select * from app.test;

create role app_user;
grant app_user to postgres;

revoke all privileges on app.test from app_user;
revoke all privileges on app.test_v from app_user;

-- if RLS is enabled
-- grant select on app.test 

grant select on app.test_v to app_user;
grant usage on schema app to app_user;

-- security vulnerability (simple views only)
-- grant insert, update, delete on app.text_v to app.user;

set role app_user; 

select * from app.test; -- ERROR: permission denied for table test
select * from app.test_v; -- rows
insert into app.test_v (slug) values ('hello'); -- ERROR:  permission denied for view test_v

reset role;