[Regression] Realtime fails on supabase cli v1.48.1

[evelant]

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

During local development, realtime fails to initialize with Could not create schema migrations table. This behavior started I believe somewhere around Supabase v1.48.1

To Reproduce

1. Install supabase 1.48.1
2. supabase start
3. Attempt to connect a realtime postgres changes listener
4. Observe log failure in docker pasted below

Expected behavior

Realtime used to work in local development. This broke in a recent version of supabase cli.

Screenshots

N/A

System information

• OS: macOS
• Browser (if applies) [e.g. chrome, safari]
• Version of supabase: 1.48.1
• Version of Node.js: 16.19.0
• Version of realtime image in Docker: public.ecr.aws/supabase/realtime:v2.10.1

Additional context

This error fills the logs hundreds of times per second. There are no objects in the realtime schema in the database.

3:42:14.625 [debug] Starting postgres stream extension with args: %{
2023-04-05 19:42:14   "db_host" => "peGf+T6wZjVSy1z8t5R3PpIF6FAwDJZbFio6IQh4dhw=",
2023-04-05 19:42:14   "db_name" => "sWBpZNdjggEPTQVlI52Zfw==",
2023-04-05 19:42:14   "db_password" => "sWBpZNdjggEPTQVlI52Zfw==",
2023-04-05 19:42:14   "db_port" => "+enMDFi1J/3IrrquHHwUmA==",
2023-04-05 19:42:14   "db_socket_opts" => [:inet],
2023-04-05 19:42:14   "db_user" => "sWBpZNdjggEPTQVlI52Zfw==",
2023-04-05 19:42:14   "id" => "realtime-dev",
2023-04-05 19:42:14   "ip_version" => 4,
2023-04-05 19:42:14   "poll_interval_ms" => 100,
2023-04-05 19:42:14   "poll_max_changes" => 100,
2023-04-05 19:42:14   "poll_max_record_bytes" => 1048576,
2023-04-05 19:42:14   "publication" => "supabase_realtime",
2023-04-05 19:42:14   "region" => "us-east-1",
2023-04-05 19:42:14   "slot_name" => "supabase_realtime_replication_slot",
2023-04-05 19:42:14   "subs_pool_size" => 5
2023-04-05 19:42:14 }
2023-04-05 19:42:14 23:42:14.645 project=realtime-dev external_id=realtime-dev [error] Could not create schema migrations table. This error usually happens due to the following:
2023-04-05 19:42:14 
2023-04-05 19:42:14   * The database does not exist
2023-04-05 19:42:14   * The "schema_migrations" table, which Ecto uses for managing
2023-04-05 19:42:14     migrations, was defined by another library
2023-04-05 19:42:14   * There is a deadlock while migrating (such as using concurrent
2023-04-05 19:42:14     indexes with a migration_lock)
2023-04-05 19:42:14 
2023-04-05 19:42:14 To fix the first issue, run "mix ecto.create".
2023-04-05 19:42:14 
2023-04-05 19:42:14 To address the second, you can run "mix ecto.drop" followed by
2023-04-05 19:42:14 "mix ecto.create". Alternatively you may configure Ecto to use
2023-04-05 19:42:14 another table and/or repository for managing migrations:
2023-04-05 19:42:14 
2023-04-05 19:42:14     config :realtime, Realtime.Repo,
2023-04-05 19:42:14       migration_source: "some_other_table_for_schema_migrations",
2023-04-05 19:42:14       migration_repo: AnotherRepoForSchemaMigrations
2023-04-05 19:42:14 
2023-04-05 19:42:14 The full error report is shown below.
2023-04-05 19:42:14 
2023-04-05 19:42:14 23:42:14.646 [warning] PostgresCdcRls terminated: "realtime-dev" [email protected]
2023-04-05 19:42:14 23:42:14.653 project=realtime-dev external_id=realtime-dev [error] Error starting Postgres Extention: {:error,
2023-04-05 19:42:14  {:shutdown,
2023-04-05 19:42:14   {:failed_to_start_child, Extensions.PostgresCdcRls.Migrations,
2023-04-05 19:42:14    {%Postgrex.Error{
2023-04-05 19:42:14       message: nil,
2023-04-05 19:42:14       postgres: %{
2023-04-05 19:42:14         code: :insufficient_privilege,
2023-04-05 19:42:14         file: "aclchk.c",
2023-04-05 19:42:14         line: "3650",
2023-04-05 19:42:14         message: "permission denied for schema realtime",
2023-04-05 19:42:14         pg_code: "42501",
2023-04-05 19:42:14         position: "28",
2023-04-05 19:42:14         routine: "aclcheck_error",
2023-04-05 19:42:14         severity: "ERROR",
2023-04-05 19:42:14         unknown: "ERROR"
2023-04-05 19:42:14       },
2023-04-05 19:42:14       connection_id: 11407,
2023-04-05 19:42:14       query: nil
2023-04-05 19:42:14     },
2023-04-05 19:42:14     [
2023-04-05 19:42:14       {Ecto.Adapters.SQL, :raise_sql_call_error, 1,
2023-04-05 19:42:14        [
2023-04-05 19:42:14          file: 'lib/ecto/adapters/sql.ex',
2023-04-05 19:42:14          line: 932,
2023-04-05 19:42:14          error_info: %{module: Exception}
2023-04-05 19:42:14        ]},
2023-04-05 19:42:14       {Enum, :"-map/2-lists^map/1-0-", 2, [file: 'lib/enum.ex', line: 1658]},
2023-04-05 19:42:14       {Ecto.Adapters.SQL, :execute_ddl, 4,
2023-04-05 19:42:14        [file: 'lib/ecto/adapters/sql.ex', line: 1024]},
2023-04-05 19:42:14       {Ecto.Migrator, :verbose_schema_migration, 3,
2023-04-05 19:42:14        [file: 'lib/ecto/migrator.ex', line: 696]},
2023-04-05 19:42:14       {Ecto.Migrator, :lock_for_migrations, 4,
2023-04-05 19:42:14        [file: 'lib/ecto/migrator.ex', line: 510]},
2023-04-05 19:42:14       {Ecto.Migrator, :run, 4, [file: 'lib/ecto/migrator.ex', line: 422]},
2023-04-05 19:42:14       {Realtime.Repo, :with_dynamic_repo, 2,
2023-04-05 19:42:14        [file: 'lib/realtime/repo.ex', line: 24]},
2023-04-05 19:42:14       {Extensions.PostgresCdcRls.Migrations, :init, 1,
2023-04-05 19:42:14        [file: 'lib/extensions/postgres_cdc_rls/migrations.ex', line: 90]}
2023-04-05 19:42:14     ]}}}}

[lauri865]

I can confirm the same on my end.

[evelant]

@sweatybridge I confirmed this by rolling back to 1.47.0 -- realtime works fine there. The bug was introduced in 1.48.0 or 1.48.1.

@lauri865 try rolling back to [email protected], that's working for me.

[sweatybridge]

This happened because we changed the postgres role to non-superuser in 1.47.1. I've updated realtime to use the supabase_admin role instead. Feel free to reopen if this error still exists in 1.49.3+.

[evelant]

@sweatybridge I'm not sure if this matters but there appear to be some other downstream effects of changing the postgres role. Since updating I continually see this message every time I refresh schema details in DataGrip

ERROR: permission denied for sequence users_id_seq

Looks like the reduced permissions have locked postgres out of some objects that it should be able to access?

I also see the following errors when copying my supabase hosted staging db to my local development instance using pg_dump and pg_restore

pg_restore: error: could not execute query: ERROR:  must be member of role "supabase_admin"
Command was: ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO service_role;
pg_restore: error: could not execute query: ERROR:  must be member of role "supabase_admin"
Command was: ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO service_role;
pg_restore: error: could not execute query: ERROR:  must be member of role "supabase_admin"
Command was: ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO service_role;