How to safely restore a Supabase backup without reactivating revoked permissions or external jobs?

[PlanetaryGenesisDev]

Hi everyone,

I'm evaluating Supabase as the backend for a small Android application with user accounts, private groups, memberships, invitations, and user consent settings.

I have a concern about disaster recovery.

Imagine restoring Monday's database backup on Wednesday. In the meantime, some users deleted their accounts, others were excluded from groups, and some revoked their consent.

Restoring the backup could unintentionally reactivate those accounts and permissions.

Ideally, I'd like to restore the database in an isolated environment, prevent APIs and background jobs (cron, webhooks, etc.) from running, then reapply known deletions and keep uncertain permissions disabled until they can be safely revalidated.

Is there a safe, supported way to do this with Supabase, or a simpler approach you would recommend?

I'm working alone, so I'd like to avoid building unnecessary infrastructure.

Thanks for your advice !
David

[devcaiada]

Hey David, you don't need to build heavy custom infrastructure for this. You can handle almost all of this natively using Supabase's Restore to a new project workflow combined with a lightweight "tombstone" log and RLS.

Here is how I'd structure it as a solo dev:

1. Restore in isolation (API traffic blocked by default)
   Instead of doing an in-place restore over your live project, use Database → Backups → Restore to a New Project (or restore a dump into a fresh staging project).
   Because the restored database spins up under a brand-new Project URL and new anon / service_role API keys, your Android app won't be able to hit it while you clean up the state.

2. Pause pg_cron and webhooks immediately after restore
   Cloning a project only restores the Postgres database (Edge Functions aren't copied over automatically), but pg_cron entries and pg_net webhook triggers inside Postgres do carry over. Right after the restore finishes, run this in the SQL Editor to freeze background jobs while you sanitize:

-- Pause all scheduled pg_cron jobs
SELECT cron.alter_job(jobid, active => false) FROM cron.job;

3. Reapplying deletions and revocations (the "Solo Dev" way)
   If you restore Monday's backup on Wednesday, anything stored only inside that database between Monday and Wednesday is obviously gone unless you have one of two things:

Point-in-Time Recovery (PITR): If budget allows later on, PITR streams WAL logs continuously, so you restore to the exact second before the incident on Wednesday instead of losing 48 hours.

A zero-maintenance Tombstone Log (if using daily backups): In 95% of real-world "bad migration / accidental delete" incidents, your Wednesday DB is still readable. You can query the Wednesday DB directly (or via postgres_fdw) to diff which user_ids were deleted or had consent/group membership revoked since Monday, and replay those deletions on the restored project.

To cover a total Wednesday DB outage without building extra servers, just fire a tiny append-only log (only UUIDs + action + timestamp, zero PII: {"op": "revoke_consent", "user_id": "...", "ts": "..."}) to an external bucket (like S3/R2) or even a single table on a separate free-tier Supabase project whenever a user deletes their account, gets removed from a group, or revokes consent.

4. Keeping uncertain permissions disabled via RLS
   Rather than deleting memberships or consents you aren't 100% sure about, add a needs_revalidation boolean NOT NULL DEFAULT false column to group_memberships and user_consents, and enforce it directly in your Row Level Security (RLS) policies:

CREATE POLICY "Active members only"
ON public.group_memberships
FOR SELECT
USING (
  auth.uid() = user_id
  AND needs_revalidation = false
);

During recovery, before re-enabling Supabase Cron and pointing your app to the restored database, you run a single UPDATE to flag any memberships/consents that need re-confirmation, and RLS will safely block access until the user revalidates in the Android app.

[PlanetaryGenesisDev]

Thanks for the detailed explanation!

There's one point I'm still concerned about. According to the Supabase documentation, Restore to a new project can automatically restart pg_cron and pg_net jobs as soon as restoration completes.

If I disable them afterward, some external actions might already have occurred.

For example:

• A scheduled job could send a notification to someone who was removed from a private group after the backup.
• A webhook could send data to an external service based on permissions or consent that had since been revoked.

Is there a supported way to prevent these jobs from running before the restored database becomes active?

That's the main safety guarantee I'm trying to achieve.

Thanks again!

[devcaiada]

That is a completely valid concern, and you've hit on a very real edge case with physical restores.

To be direct: No, for native binary restores ("Restore to a new project"), there is currently no supported way to pause or intercept pg_cron and pg_net before the database boots up. Because it performs a binary copy of the Postgres volume, background workers spin up the moment the restore completes. Any jobs scheduled to run will fire before you have a chance to connect and disable them.

If preventing premature external actions is a hard safety requirement, here are the two standard ways to achieve that guarantee:

1. The Official Alternative: Logical Restore via Supabase CLISupabase's official recommendation when you need to inspect or strip out cron jobs, webhook triggers, or foreign wrappers before they execute is to perform a logical restore instead of a binary restore:

• Export your database schema and data using supabase db dump (or pg_dump).

• Exclude or clear out the entries in cron.job and your webhook trigger definitions prior to applying the dump to the new project.

• Keep in mind: A logical restore does not automatically transfer the encryption root key, so Vault secrets and encrypted columns need to be manually reconfigured.

2. Defense-in-Depth Inside the JobsIf you want to keep the convenience of native backups or Point-in-Time Recovery (PITR) without the race condition risk, you can add guardrails directly into the functions called by pg_cron / pg_net:

• Environment Verification: Have the function check a configuration row or custom database setting (e.g., current_setting('app.is_active_production', true)) before dispatching any HTTP request or notification. In any restored database, this flag remains inactive until manually flipped.

• Endpoint / Credential Isolation: If webhooks rely on Vault secrets or external URLs, ensuring the target services only accept calls with production tokens prevents a cloned environment from triggering live actions.

Hope this helps clarify the available paths! Let me know if you'd like more details on either approach.

[PlanetaryGenesisDev]

Thank you very much for taking the time to explain these options so clearly. Your answers have been really helpful in understanding the risks and possible solutions.

I truly appreciate your help!

God bless you!

[devcaiada]

Any time, bro! 👊

[kushsharma]

Restoring into a separate place first is the right instinct, and the tombstone log devcaiada described is the piece I'd try.

Two things from doing this on our own projects:

Take your own logical dump as well. pg_dump through the session pooler as postgres brings auth along (a role you create only sees public). You can restore that dump into a plain Postgres on your laptop or a scratch server where pg_cron and pg_net aren't installed. Nothing can fire, because the extensions the jobs need aren't there. Check it, replay the tombstones, then decide what to promote.

Then rehearse it on a schedule. Monthly is plenty for a solo project: restore, replay, and check that a deleted account is still deleted and a revoked membership is still gone. The first rehearsal usually turns something up (an extension that didn't come back, a sequence behind the data).

[cekuu35]

The instinct to isolate before reconciling is the right one, and Supabase's supported primitives get you most of the way without extra infrastructure.

What a restore actually is

A PITR restore or a pg_dump restore replaces the entire database state — it is not a merge, so any row deleted after the backup point comes back, and any grant or consent row revoked after that point is restored to its backup state. There is no supported per-table or per-row restore. So the plan is not "restore carefully", it is "restore to somewhere safe, then reconcile deliberately."

The supported flow

1. Restore into an isolated project or branch, not into production. A restore is a new database; until you point clients at it, it can serve no traffic. On a branch/restored project, do this immediately, before anything else:
   • Rotate the keys. The restored project inherits the old project's JWT secret in a branch restore; generate a new anon/service key pair on the restored project so leaked or cached keys from the old project do not authenticate against it. On a PITR clone this is the single step that makes it inert as an API surface.
   • Disable the Data API and any Storage exposure while you reconcile (Dashboard → API settings), so the restored data is not reachable even if a key passes.
2. Freeze background work before it starts. Two things come back with the restore: pg_cron jobs (they live in cron.job, which is part of the dump) and any scheduled Edge Functions / webhooks (those are project resources, and on a branch they do not carry over; on a restore into the same project they do). On the restored database, list the jobs with select jobid, schedule, command from cron.job; and unschedule each with select cron.unschedule(jobid);. Webhooks are dashboard resources, not database rows — a restore does not re-arm them in a new project, so the branch route gives you this for free.
3. Reconcile the deletions and revocations, by design not by memory. The rows that must stay deleted (closed accounts, revoked consents, removed memberships) are known only if you recorded them. Two supported shapes for that: an append-only audit table in your application schema (consent_audit, membership_events), which survives a restore because it records what happened after — you replay it; or, if you have no audit trail, a reconciliation pass comparing the restored snapshot against the current live project's state before you swap. For consent specifically, an append-only log is also the shape a GDPR accountability request wants, so building it now pays twice.
4. Validate against the API with a real user JWT — a deleted account trying to authenticate (expect deny), a user whose consent was revoked hitting an endpoint that requires consent (expect deny), a restored user hitting their own data (expect allow). The second one is the assertion people skip, and it is the one your use case names.
5. Promote by pointing clients at the restored project, or migrate the reconciled delta into a fresh project. DNS/env flip, not in-place restore, is what keeps step 1's containment meaningful.

The honest limitation

Consent revocations that you did not log cannot be reconstructed from a backup — the backup does not know they happened. That is not a Supabase limitation, it is what a backup is. For your specific case (user consent), the fix forward is the audit table above, plus writing consent state as an append-only event log rather than a mutable flag, so "user revoked consent at T" is a fact you hold independently of any restorable snapshot.

What I would do concretely, small app, solo

PITR is your friend here: enable it, and when you need recovery, restore to a new project at the backup timestamp rather than overwriting the live one. Reconcile the delta between new-project-restored and live (both readable at once, so the comparison is a script, not a memory exercise), then flip the env. That keeps the blast radius of step 3 contained and gives you the "keep uncertain permissions disabled until revalidated" behaviour you asked for — uncertain rows are simply the ones your reconciliation script could not match, and you leave them inactive until you look at them.

One more thing for the solo-maintainer case: the restore-verification steps 1–4 are a checklist, not a one-off, and they belong in the same repo as your migrations so the next person (or you, at 3am) runs them in order. The failure mode of DR is not the restore, it is the restore plus the assumption that everything after the backup point still applies.

[PlanetaryGenesisDev]

Thank you all for taking the time to reply and explain things so clearly. I really appreciate your help!

I'm going to test a small, independent project acting as a fail-closed switch for outbound calls during recovery, alongside an isolated restore and a separate deletion registry.

The main challenge is making sure nothing unsafe can happen before reconciliation, including cron jobs and database operations that might bypass the switch.

If anyone is interested in the results, just let me know and I'll happily share them here.

Thanks again.
God bless you!

[sjraaijmakers]

On the cron and webhook part: a restore into a new project can bring those back paused. ReviveDB restores a recovery point into a separate, empty Supabase project with the pg_cron jobs set to inactive and Database Webhook triggers disabled, so nothing calls out until you switch them on yourself. Your live project is not affected. A trigger of your own that calls pg_net directly comes back as it was, so keep your fail-closed switch for those.

For the deletion registry, each recovery point records capturedAt in its manifest.json. That moment is always before the database snapshot was taken, so replaying every deletion and revocation logged from capturedAt onwards covers everything. A few deletions then run twice, which does nothing when they are idempotent.

https://revivedb.dev (I build ReviveDB)

[PlanetaryGenesisDev]

Hi, thanks for your helpful reply!

ReviveDB looks really interesting for my recovery strategy.

I'm developing an Android community app and need a safe way to restore an old backup into a new Supabase project, keep community access blocked, fix outdated accounts and permissions, and only reopen after verification.

I have three questions that are quite important for my project:

1. Cron/webhooks: Are they disabled before they have any chance to run, including during the restore itself?
2. Outbound requests: Apart from custom triggers calling pg_net directly, are there other automatic outbound actions ReviveDB leaves enabled? Can I disable them before restoration?
3. Isolation (my main concern): Can I keep the restored project's Auth/API/Edge Functions inaccessible to users while still accessing PostgreSQL to make corrections, then reopen manually? Is this something ReviveDB handles, or would I need to set it up myself?

I'll handle the independent deletion registry and permission revalidation on my side.

Thanks again!

[sjraaijmakers]

Hi David,

Cron jobs are recreated as inactive, and recognised Database Webhooks are recreated disabled, including during the restore.

My earlier “nothing calls out” was too broad. Other SQL can still make outbound requests, and ReviveDB has no general outbound block. Your independent switch would need to cover those paths.

You would need to arrange access isolation on the destination before restoring. ReviveDB preserves destination settings, but does not block Auth, APIs or Edge Functions or manage reopening them. A separate project alone does not guarantee that isolation.