manually rearrange and modify the generated migration - policies

[panchicore]

Bug report

supabase db remote commit works fine but even though it warns about it is not foolproof and that you need to manually modify the migration file, I would like to report what I had to modify in order to apply the migrations without problems

Describe the bug

supabase db remote commit generates the migration with an error on the declaration of the CREATE POLICY that makes supabase db changes to fail with Error: Error starting shadow database: ERROR: role "anon, authenticated, service_role" does not exist

To Reproduce

1. run supabase db remote commit
2. supabase db changes or restart local supabase
3. Output: Error: Error starting shadow database: ERROR: role "anon, authenticated, service_role" does not exist

Expected behavior

To generate the SQL code without quoting the roles on the TO statement :

CREATE POLICY "Enable insert access to all users"
    ON public.my_table
    AS PERMISSIVE
    FOR INSERT
    TO "anon, authenticated, service_role" -- HERE
    WITH CHECK (true);

When the quotes are removed the migration works.

Additional context

More fixes on the migration file:

Prevent:

Error: Error starting database: ERROR:  cannot drop function graphql.build_delete(jsonb,jsonb,jsonb,text,text) because extension pg_graphql requires it
HINT:  You can drop extension pg_graphql instead.

By commenting the lines:

-- DROP FUNCTION IF EXISTS graphql.build_delete(ast jsonb, variable_definitions jsonb, variables jsonb, parent_type text, parent_block_name text);

-- DROP FUNCTION IF EXISTS graphql.build_update(ast jsonb, variable_definitions jsonb, variables jsonb, parent_type text, parent_block_name text);

-- DROP FUNCTION IF EXISTS graphql.cache_key(role regrole, ast jsonb, variables jsonb);

-- DROP FUNCTION IF EXISTS graphql.build_insert(ast jsonb, variable_definitions jsonb, variables jsonb, parent_type text);

-- DROP TABLE IF EXISTS graphql._field CASCADE;

-- DROP TABLE IF EXISTS graphql._type CASCADE;

Not a SQL/Postgres coder here but commenting this doesn't feel right.

System information

Supabase CLI 0.26.0

[nahtnam]

Hello, I found this through search and would like to report that I also had to comment out the graphql lines and something feels wrong about that

[FragAverage]

I too, have had to comment out certain lines and also rearrange one of the migrations due to creating "types" near the bottom of the file that were required on create tables much higher up

[sweatybridge]

Hello, we are now using pg_dump with custom flags to create the initial schema the first time db remote commit is run. I believe this is more reliable than the schema diff tool we were using before.

Please feel free to reopen this issue if it is still reproducible in the latest version.