# Freezing writes during a migration

> Pause writes to one project while you migrate or move data, and keep reads working. A Data API pre-request check, Storage policies, paused cron jobs, and how to undo it all.

Source: https://supahost.app/kb/read-only-mode · Updated 2026-11-11

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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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

```bash
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

```sql
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):

```sql
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](/docs/database/migrations)
- [Partial migrations from transaction mistakes](/kb/partial-migrations)
- [Monitoring and debugging cron jobs](/docs/cron-queues/monitoring-jobs)
- [Storage access control](/docs/storage/access-control)