SafeGrd / Supabase backup

Supabase backups outside the project, restored to test them

SafeGrd backs up a Supabase database from a scheduled GitHub Actions job, so there is no server to run. The job dumps the database, encrypts it on the runner, and writes it to storage that refuses deletes until the lock ends. SafeGrd then restores it on a machine started for that drill and destroyed when it ends, and checks every table's row count.

Or let SafeGrd take the backup itself, with no workflow to keep: Back up on SafeGrd under Surfaces in the console takes the project's connection string, and a machine SafeGrd starts for each backup dumps and encrypts it, then is destroyed. The string is sealed and given only to that machine (how).

Run your first Fire Drill Read the setup guide 

- uses: safegrd/backup-action@v0
  with:
    config: ${{ secrets.SAFEGRD_CONFIG }}
    database-url: ${{ secrets.DATABASE_URL }}

Why a copy outside Supabase

Supabase's daily backups belong to the project. Supabase's documentation says that deleting a project removes all of its data, including its backups. Pro keeps 7 days of them, Team 14 and Enterprise up to 30.

A SafeGrd backup lives in a bucket outside the project, under S3 Object Lock in compliance mode, so nobody can delete it before its date, including anyone holding the project's keys. It keeps as many daily, weekly and monthly copies as you set.

What the backup holds

Connecting from GitHub Actions

The direct host, db.[PROJECT-REF].supabase.co, has only an IPv6 address unless you buy Supabase's IPv4 add-on, and GitHub's runners have no IPv6. Use the session pooler on port 5432 with ?sslmode=require. The transaction pooler on port 6543 shares server connections between clients, which breaks binary COPY and the single snapshot the backup reads every table from.

How the restore is tested

A Supabase project already holds Supabase's own schemas, and its postgres user is not a superuser, so a backup cannot load into a new project. The drill restores into Supabase's PostgreSQL image as supabase_admin instead, which carries the extensions a stock PostgreSQL image lacks, such as supabase_vault, and compares every table's row count with the manifest written at backup time. With Drill on SafeGrd set on the surface, SafeGrd runs that drill for you on a machine started for that drill and destroyed when it ends. The guide has the restore commands for a real recovery.

How to restore

A project is never empty, so restore the schemas that are yours into it and leave Supabase's alone: --schema public recreates that schema's tables and rows, and --data-only-schema loads a schema's rows into tables the project already has.

safegrd restore --snapshot SNAPSHOT_ID --target env:RESTORE_URL --schema public --data-only-schema auth --data-only-schema storage

Drop or rename the tables in public first, because the restore creates them. The whole restore is one transaction: a row count that does not match the manifest rolls all of it back.

To restore the whole database instead, start Supabase's PostgreSQL image and point --target at an empty database on it. Both paths, with the connection strings, are in the guide; what a restore checks is on Restore and Fire Drills.

Questions

Do I need a server?

No. Either SafeGrd backs it up (Back up on SafeGrd in the console, with the connection string), or the GitHub Action is the host: it needs a SafeGrd config in a repository secret and the database URL in another. Storage can be your own S3-compatible bucket or SafeGrd's hosted storage, which is locked in compliance mode.

Who holds the decryption key?

With a SafeGrd-managed key, SafeGrd keeps your key sealed and releases it only to your enrolled hosts, so you can restore even after losing a host, and the workflow needs no key of its own. With a customer-managed key, only you can decrypt these backups, and the drill job takes the key from a secret.

How is this different from BackupDrill?

BackupDrill connects to Supabase by OAuth and drills in its own cloud. SafeGrd backs up from your workflow into storage under Object Lock, and drills other databases, files and mail the same way (SafeGrd vs BackupDrill).

Related