Freezing writes during a migration

Before a risky migration, a data backfill or a move between projects, it helps to stop writes for a few minutes while the app keeps serving reads. Postgres has no single switch for "this database is read-only for the app but not for me", so you combine a few pieces. All of them are scoped to one project.

1. Find your project's database name

Each SupaHost project is its own database on the instance's Postgres server. In the project's SQL editor:

select current_database();  -- e.g. p_app_one

Roles such as anon, authenticated and authenticator are shared by every project on the instance, so every role setting below uses in database p_app_one. Without it, you'd freeze all your projects at once.

2. Reject Data API writes

PostgREST can run a function before every request. This one lets reads through and refuses everything else with HTTP 503:

create or replace function public.reject_writes()
returns void
language plpgsql
set search_path = ''
as $$
begin
  -- uncomment to let server-side code using the service_role key keep writing
  -- if current_user = 'service_role' then return; end if;

  if current_setting('request.method', true) not in ('GET', 'HEAD') then
    raise sqlstate 'PT503'
      using message = 'Writes are paused for maintenance. Please try again in a few minutes.';
  end if;
end;
$$;

Check whether the project already uses a pre-request function, so you can put it back afterwards:

select d.datname, s.setconfig
from pg_db_role_setting s
left join pg_database d on d.oid = s.setdatabase
where s.setrole = 'authenticator'::regrole;

Then turn the freeze on and tell PostgREST to reload its settings:

alter role authenticator in database p_app_one
  set pgrst.db_pre_request = 'public.reject_writes';
notify pgrst, 'reload config';

POST calls to /rpc/ functions are blocked too, even ones that only read. Call read-only functions with get: true in supabase.rpc() if they need to keep working.

3. Pause Storage changes

Storage doesn't go through PostgREST. Restrictive policies block uploads, overwrites and deletes for app users while leaving downloads alone:

create policy "freeze uploads" on storage.objects
  as restrictive for insert to anon, authenticated with check (false);
create policy "freeze updates" on storage.objects
  as restrictive for update to anon, authenticated using (false);
create policy "freeze deletes" on storage.objects
  as restrictive for delete to anon, authenticated using (false);

4. Pause background writers

• Cron jobs: Dashboard → your project → Cron tab, switch off Active on jobs that write.
• Sign-ups: if your migration touches profiles created on sign-up, turn on Disable new sign-ups in Dashboard → your project → Auth tab → Sign-in & security. Existing users can still sign in.
• Edge Functions that use the service_role key are blocked by step 2 unless you uncommented the exception.

5. Check it works

curl -i -X POST "https://app-one.acme.supahost.space/rest/v1/notes" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" \
  -d '{"body":"test"}'
# expect: HTTP/1.1 503

Your own work in Studio's SQL editor isn't affected, because it runs as postgres and doesn't go through the Data API.

Undo it

alter role authenticator in database p_app_one reset pgrst.db_pre_request;
-- or set it back to the function you found in step 2
notify pgrst, 'reload config';

drop policy "freeze uploads" on storage.objects;
drop policy "freeze updates" on storage.objects;
drop policy "freeze deletes" on storage.objects;

Then switch your cron jobs and sign-ups back on.

Why not default_transaction_read_only on anon and authenticated?

alter role ... set default_transaction_read_only = on is often suggested. It applies when a session logs in as that role. The Data API logs in as authenticator and switches to anon or authenticated inside each request's transaction, after that transaction has already started, so the setting doesn't make the request read-only. It does work for roles your own tools log in with directly (for example over the Business SSH tunnel):

alter role etl_worker in database p_app_one set default_transaction_read_only = on;
-- undo
alter role etl_worker in database p_app_one reset default_transaction_read_only;

Existing connections keep the old value until they reconnect.

Related

• SQL migrations
• Partial migrations from transaction mistakes
• Monitoring and debugging cron jobs
• Storage access control