supabase cb remote commit fails with pg_dump error

[paulgibbs]

Bug report

Same as https://github.com/supabase/cli/issues/437 so I am copying it over.

Describe the bug

I have a supabase project that is running in production. Now I want to setup supabase for local development so that I don't develop against the production data. Therefore I run:

supabase init

supabase link --project-ref <id>

supabase db remote commit  // Fails with pg_dump error

Error:

Error: Error running pg_dump on remote database: pg_dump: error: query failed: ERROR:  permission denied for table _type

pg_dump: error: query was: LOCK TABLE "graphql"."_type" IN ACCESS SHARE MODE

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Create new DB on sup abase
2. supabase init
3. supabase link --project-ref
4. supabase db remote commit // Fails with pg_dump error

Expected behavior

db remote commit command works as expected and it create a migrations file locally with the remote database.

Additional context

I had to disable the graphql extension, run the command, then re-enabled graphql. I don't know enough to know if this is going to cause any problems.

@barrelltech had the same issue which was added to the bottom of a closed issue #437 as a comment

[sweatybridge]

Hello, thanks for following up on this issue. If toggling pg_graphql extension doesn't work, you might need to grant permissions manually to postgres role. Could you run the following statements in your project's SQL Editor and try remote commit again?

grant all on all tables in schema graphql to postgres, anon, authenticated, service_role;

grant all on all functions in schema graphql to postgres, anon, authenticated, service_role;

grant all on all sequences in schema graphql to postgres, anon, authenticated, service_role;

Ideally we want to handle this for all hosted projects but I'm not sure how long that would take. Meanwhile I hope this temporary solution helps you get past the issue. Let me know if it works.

[paulgibbs]

That did the job @sweatybridge - thank you