Permission errors when restoring database backups in local development

[itinance]

trafficstars

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