supabase db diff migration files does not include view with (security_invoker=on) clause

[simbas]

Bug report

Describe the bug

the diff tool does not add the with (security_invoker=on) clause for views created with this clause, this omission can cause security issues.

To Reproduce

in the supabase studio sql editor, create a table with RLS enabled, insert some data, and create a view with security invoker on:

create table test (
  name text
);

alter table "test" enable row level security;

insert into test values ('test1');
insert into test values ('test2');

create view view_with_security_invoker_on with (security_invoker=on) as select
name from test

you get the following result when calling the view with an anon key:

[]

then run migration and reset:

supabase db diff -f create_ view_with_security_invoker_on
supabase db reset

insert back the data (deleted during the reset) in the sql editor:

insert into test values ('test1');
insert into test values ('test2');

you get the following result when calling again the view with an anon key:

[{"name":"test1"}, 
 {"name":"test2"}]

the creation of the view in the migration file is done without the with (security_invoker=on) clause:

create table "public"."test" (
    "name" text
);


alter table "public"."test" enable row level security;

create or replace view "public"."view_with_security_invoker_on" as  SELECT test.name
   FROM test;

Expected behavior

migration file should include the with (security_invoker=on) clause for views created with with (security_invoker=on).

System information

• OS: macOS
• Version of supabase cli: 1.33.0
• Version of Node.js: 16.19.0

[gilbert]

Just ran into this. Glad I checked, this is a subtle security hazard.

[SamuraiT]

The same thing happens to me

[Palabola]

Same here. The update almost got live. Fix would be much appreciated.

[daroczig]

We have also encountered this and accidentally caught the problem while applying the auto-generated migration files to staging. Note that this is not only a bug, but a potentially serious security risk if a view gets into prod without security_invoker=on unintentionally :disappointed: