Views with security_invoker are being migrated without this option

[luizzappa]

Bug report

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

Describe the bug

When creating a view remotely (supabase.com) with the security_invoker = true option, migrations to be applied locally do not come with this option.

To Reproduce

1. Create a view remotely (supabase.com)

CREATE VIEW test_security WITH (security_invoker) as SELECT 2 as value;

2. Locally pull migrations

supabase db pull

3. Note that migrations do not come with the security invoker = true option

+ create or replace view "public"."test_security" as  SELECT 2 AS value;
- create or replace view "public"."test_security" WITH ("security_invoker"='true') as  SELECT 2 AS value;

Expected behavior

The migration should go like this:

create or replace view "public"."test_security" WITH ("security_invoker"='true') as  SELECT 2 AS value;

System information

• OS: Windows
• Version of supabase-js: 2.39.3
• Version of Node.js: v18.18.2

[igorlanko]

Thanks for reporting this, I just uncovered this myself too, unfortunately.

I think you summed it up really well, but just a few additional (maybe obvious) details about the issue:

• the with (security_invoker) argument is missing in the schema of the view in the Supabase UI too;
• neither the auto migration supabase db diff -f includes that in the migration script, nor does it detect it as a change to the view if modified from the Supabase's SQL editor.

Update: there's also actually a related discussion over here https://github.com/orgs/supabase/discussions/901#discussioncomment-6517175.

[Hallidayo]

Hi all, Just checking in here are you still experiencing this?

[FrostedBiscuit]

Hi @Hallidayo this is in fact still an issue. Not a big deal to manually change the migration after it gets generated but it'd be much more convenient if the view's permissions were included by default.

[Hallidayo]

Transferred from the supabase repo.

[sweatybridge]

closing as duplicate