Supavisor insufficient privileges on schema _supavisor

[briankariuki]

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

I'm self hosting supabase with the latest master branch. I've setup all the env variables and followed the self host tutorial. However all containers spin up apart from the supavisor. On checking the logs, it throws an Ecto migrator error.

Logs

Setting RLIMIT_NOFILE to 100000
15:55:17.424 [error] Could not create schema migrations table. This error usually happens due to the following:

  * The database does not exist
  * The "schema_migrations" table, which Ecto uses for managing
    migrations, was defined by another library
  * There is a deadlock while migrating (such as using concurrent
    indexes with a migration_lock)

To fix the first issue, run "mix ecto.create" for the desired MIX_ENV.

To address the second, you can run "mix ecto.drop" followed by
"mix ecto.create", both for the desired MIX_ENV. Alternatively you may
configure Ecto to use another table and/or repository for managing
migrations:

    config :supavisor, Supavisor.Repo,
      migration_source: "some_other_table_for_schema_migrations",
      migration_repo: AnotherRepoForSchemaMigrations

The full error report is shown below.

** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied for schema _supavisor
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.14.3) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1154: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.10.2) lib/ecto/migrator.ex:756: Ecto.Migrator.verbose_schema_migration/3
    (ecto_sql 3.10.2) lib/ecto/migrator.ex:564: Ecto.Migrator.lock_for_migrations/4
    (ecto_sql 3.10.2) lib/ecto/migrator.ex:433: Ecto.Migrator.run/4
    (ecto_sql 3.10.2) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
    nofile:1: (file)

I've tried granting all permissions to this schema by adding this code to the pooler.sql file

GRANT ALL PRIVILEGES ON SCHEMA _supavisor TO :pguser;

System information

• OS: [14.6.1]
• supavisor: [supabase/supavisor:1.1.62]
• database: [supabase/postgres:15.8.1.003]

[xzenon]

I encountered the same issue with a clean setup for self-hosting Supabase. I managed to fix it by changing pooler.sql with the following:

\c _supabase
create schema if not exists _supavisor;
alter schema _supavisor owner to postgres;

[briankariuki]

I encountered the same issue with a clean setup for self-hosting Supabase. I managed to fix it by changing pooler.sql with the following:

\c _supabase
create schema if not exists _supavisor;
alter schema _supavisor owner to postgres;

I believe POSTGRES_USER is postgres. which is same as :pguser ?

\set pguser `echo "$POSTGRES_USER"`

\c _supabase
create schema if not exists _supavisor;
alter schema _supavisor owner to :pguser;

[xzenon]

I believe POSTGRES_USER is postgres. which is same as :pguser ?

I also believe it should - but it is not by some reason. To debug I added the following into pooler.sql:

\set pguser `echo "$POSTGRES_USER"`

\echo Now pguser is: :pguser

\c _supabase
create schema if not exists _supavisor;
alter schema _supavisor owner to :pguser;

Then if I make a clean run I can see in the logs of "db" container that supabase_admin is used:

2024-11-21 21:19:54 /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/99-pooler.sql 2024-11-21 21:19:54 Now pguser is: supabase_admin 2024-11-21 21:19:54 You are now connected to database "_supabase" as user "supabase_admin". 2024-11-21 21:19:54 CREATE SCHEMA 2024-11-21 21:19:54 ALTER SCHEMA

I think POSTGRES_USER is changed somewhere under the hood of custom postgres image.

[kiinoo]

got the same problem

[Illusionist0]

got the same problem，can it be fixed correctly now?

[Illusionist0]

bro, I found a new way to fix this problem,you can add"POSTGRES_USER=postgres" into your /docker/.env file

[htetlynnhtun]

got the same problem for a fresh install setup

[briankariuki]

got the same problem for a fresh install setup

Change your pooler.sql like this

https://github.com/supabase/supavisor/issues/490#issuecomment-2489527220

[Pooort]

The same issue. @briankariuki Doesn't work for me. Still restarting with: ** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied for schema _supavisor

[Illusionist0]

The same issue.

edit ur .env file,add "POSTGRES_USER=postgres".

delete the volumes/db/data file ,then restart your service

[Pooort]

@Illusionist0 It works, thank you. Related to data left after first start.

[Illusionist0]

@Illusionist0 It works, thank you. Related to data left after first start.

yes, i found that there's no POSTGRES_USER in .env file,but in xxx.sql,there are many "set pguser $POS..",so i add this,and it finaly works. by the way ,u need to del the volumes/db/data folder,unless the initialization won't work

[dericdesta]

This has been fixed in https://github.com/supabase/supabase/commit/419eba99a167499ace8c11798ddec36750e5daff 🎉

[abc3]

@hauleth this could be closed