Permission errors when restoring database backups in local development

[itinance]

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 restoring a PostgreSQL database backup in a local Supabase development environment, I encounter persistent permission errors that prevent normal operation. This creates a difficult situation where database restores cannot be properly integrated into development workflows.

To Reproduce

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

Steps to reproduce

1. supabase init
2. Restore the database using:

supabase db start --from-backup /path/to/backup.backup

3. superbase stop
4. supabase start
5. add a new migration (like CREATE TRIGGER... or CREATE TABLE)
6. Try to run any Supabase operation that accesses or modifies the database structure (like running migrations)

Error:

failed to create migration table: ERROR: permission denied for database postgres (SQLSTATE 42501)

Current behavior

After restoring a database backup, I encounter the following error:

failed to create migration table: ERROR: permission denied for database postgres (SQLSTATE 42501)

Additionally, attempting to fix permissions via SQL commands fails with:

ERROR: permission denied for database postgres

Even direct container access using docker exec as the postgres user fails to provide the necessary permissions to modify the database structure.

Expected behavior

When restoring a database backup in a local development environment:

1. The restore operation should maintain proper permissions for the Supabase service accounts
2. Migration tables should be accessible/creatable after a restore
3. No manual permission fixes should be required for standard operations

Related issues

The main problem is that restoring database backups results in permission errors that prevent further development

Attempted solutions

I've tried the following approaches, all without success:

1. Connecting to the database via supabase sql and granting permissions
2. Accessing the PostgreSQL container directly and attempting to grant permissions as the postgres user
3. Various permission modification commands targeting the supabase_migrations schema
4. Multiple connection methods to try to gain superuser privileges

Current workaround

Currently, the only functional but extremely inefficient workaround is:

1. Export data only (not schema) from the restored database
2. Start a fresh Supabase instance
3. Run migrations to recreate the schema
4. Import the data back

Impact

This issue significantly hampers development workflows that rely on database restores, making it difficult to:

1. Share database states between team members
2. Test migrations against production-like data
3. Streamline local development setup

Additional context

There seems to be multiple interconnected permission issues:

1. The postgres role doesn't retain expected superuser privileges after restore
2. The Supabase service roles don't have permissions to access or create required structures
3. Even direct container access doesn't provide sufficient permissions to fix the issue

Any guidance or fixes would be greatly appreciated as this currently blocks efficient development workflows.

Possible solution directions

Some potential approaches to investigate:

1. Enhance the --from-backup functionality to properly restore role permissions
2. Add an option to automatically grant necessary permissions after restore
3. Provide clearer documentation on managing permissions in restored databases
4. Create a dedicated workflow for restoring backups with proper permissions

System information

Supabase: 2.22.6
Node.js: 20.18.3
V8: 13.2.152.41-electron.0
OS: Darwin arm64 23.6.0

[Hallidayo]

Moved from the supabase repo.

[sweatybridge]

The permission issues are due to old versions of postgres. In order to fix them, you need to do a db dump locally after restoring from backup. For eg.

supabase db start --from-backup /path/to/backup.backup
supabase db dump --local | supabase migration new local_schema
# Optionally dump the data as well
supabase db dump --local --data-only -f supabase/seed.sql

After that, restart that local stack using your schema dump instead of the backup.

supabase stop --no-backup
supabase start

[itinance]

thx, @sweatybridge

The permission issues are due to old versions of postgres. In order to fix them, you need to do a db dump locally after restoring from backup. For eg.

supabase db start --from-backup /path/to/backup.backup

this gives no another error:

{"level":"fatal","msg":"running db migrations: Migrator: problem creating schema migrations: couldn't start a new transaction: could not create new transaction: failed to connect to `host=supabase_db_xxxx-backend user=supabase_auth_admin database=postgres`: failed SASL auth (FATAL: password authentication failed for user \"supabase_auth_admin\" (SQLSTATE 28P01))","time":"2025-05-29T11:26:46Z"}

I did supabase link before that, where i had to put in my database password (that even works inside my application perfectly, when connecting to the remote database).

But though it reports this error.

[dccakes]

I am having the same issue with the permission error

[roo12312]

same error

[hmprt]

bumping this. Specifically, I can create tables and insert / read data but can't dreate indices without hitting permission denied for schema public in psql

[hmprt]

Update: while waiting for a supabase response, we cooked a script which uses psql in combo with the original backup file, resulting in a local copy of your remote DB that works exactly as intended (meaning no permissions bugs)

You might have to customize a little to your use case, but here you go:

All code provided without warranty or any obligation of ongoing support. Use at your own risk.