Restoration failure due to logical backup error

[PavelStanoev]

When a supabase project with pointsource-supabase_rbac is paused or upgraded it is very likely the process of resuming to fail due to the fact that logical backups don't always correctly capture dependencies between database objects. This TLE needs auth.users to be created before it can be installed.

I am not familiar if we can do something about this but wanted to share some light on the issue.

[samuelnh]

@point-source is this something you can look into?

[point-source]

Hey, just dropping in here to say that I am paying attention and I recognize this is high priority. That said, I've been very overloaded with my primary work lately so I don't have an eta on when I can research this.

Any assistance that the community can contribute towards identifying a detailed cause/solution would be greatly appreciated, even if it's not a PR. I'd really like to keep this project moving as supabase evolves but it I haven't been as immersed in it lately as other work has demanded increasing amounts of time.

I will do my best to fit this in until then. Thanks for the report.

[PavelStanoev]

A little more info:
Non-free plans can leverage physical backups which will resolve the problem with the logical backups messing up the dependancies. This is good because supabase team is working on introducing physical backups for all projects. From what I know tho, this won't be happening soon.

[point-source]

Thanks. Is there a way to reproduce this other than waiting for a project to pause on its own?

[samuelnh]

Yeah - this wouldn't happen with physical backups as Supabase support let us know, but as @PavelStanoev says, I don't think this will be soon either.

@point-source I believe you can create a new project, install the this TLE, and then restore from a backup - at least, mine occurred when applying from a backup.

[faustodc]

Had the same problem after my project got paused, here’s a relevant extract from Supabase support after they managed to restore my project by removing the extension:

It was failing to restore because logical backups don't always correctly capture dependencies between database objects. Specifically, your project was using Trusted Language Extensions (TLE) pointsource-supabase_rbac. The pointsource-supabase_rbac extension creates tables which are not captured in the backups, but there are commands in the backup which assume the presence of these tables.

I have removed the pointsource extension from the backup to be able to restore the data. The data in your tables is back but the pointsource-supabase_rbac extension is not available. It is advised that to avoid such failures in future you do not use pointsource-supabase_rbac or other TLEs. If the TLEs are used, the project will fail to restore again in the future when paused or upgraded. We are working on enabling physical backups for all projects to avoid these kinds of failures but that is still a ways off.

[samuelnh]

To get around this, I was able to install this TLE directly from the SQL scripts as part of my db migrations. Less than ideal, but a workaround that works for me for now nonetheless.

[point-source]

I've opened a discussion about this here. I am considering migrating this project off of dbdev / tle and back to just standard migration files.

EDIT: As of version 5.2.0, this package ships with an install.sh script which can generate a migration file for install and for updates, just like dbdev but without the drawback of breaking db backup/restore. Unfortunately, there isn't a clean migration path from dbdev-based install to install.sh based, but for future projects, this is fixed.