SaaS architecture
Exporting one tenant with pg_dump and row-level security
pg_dump has no WHERE clause, but row-level security can be one. Tested on PostgreSQL 17: one tenant's rows, the sequence values that leak, restoring under RLS.
A tenant asks for a copy of all their data, or you want to move one tenant to another database. In a multi-tenant SaaS where every tenant shares the same tables, the first tool you reach for is pg_dump — and it has no --where option. It dumps a table or it does not.
If your tenants are separated by row-level security, you already have the WHERE clause. We tested how far that gets you on PostgreSQL 17.10, in a throwaway container: 311,205 invoices and 1,244,820 invoice lines across 100 tenants, row-level security with FORCE on all three tables, and an application role app that is not the owner. The policy reads the tenant from a session variable, nullif(current_setting('app.tenant', true), '')::int.
Can pg_dump export only one tenant’s rows?
Yes, with --enable-row-security and the tenant set for the dump’s session:
PGOPTIONS="-c app.tenant=50" pg_dump -U app --data-only --enable-row-security \
-t tenants -t invoices -t invoice_lines -f tenant-50.sql
What came out:
Run as app | Result |
|---|---|
without --enable-row-security | ERROR: query would be affected by row-level security policy for table "tenants" |
| with it, no tenant set | three empty tables, no error |
| with it, tenant 50 | exactly tenant 50: 1 tenant, 1,200 invoices, 4,800 lines; 0.09 s, 453 kB |
| with it, tenant 1 (the largest) | 1 tenant, 60,000 invoices, 240,000 lines; 0.24 s, 21.9 MB |
The first line is deliberate: by default pg_dump turns row security off “to ensure that all data is dumped”, and refuses when it cannot. The second is the one to watch in a script. With a nullif policy a missing tenant means zero rows — our RLS article calls that “wrong in the safe direction”, and for queries it is. For an export it is a silent failure: a job whose tenant variable did not arrive produces a valid, empty file. Count the rows in what you hand over.
pg_dump “makes consistent backups even if the database is being used concurrently”: the invoices and the lines in the export come from one snapshot and belong together, even while the tenant keeps working. The sequence values below are the exception; they are read as they are at that moment.
What leaks in a one-tenant pg_dump?
The sequence values. The export for tenant 50, with 1,200 invoices, ended with:
SELECT pg_catalog.setval('public.invoice_lines_id_seq', 1244820, true);
SELECT pg_catalog.setval('public.invoices_id_seq', 311205, true);
Those are counters shared by all tenants. Handing this file to tenant 50 tells them how many invoices and lines exist in your whole product — a number you may not publish anywhere. Row-level security does not apply to sequences, and pg_dump -t includes the sequences owned by the tables you select. Excluding both sequences with -T invoices_id_seq -T invoice_lines_id_seq did not remove them; --exclude-table-data='*_seq' did. That pattern relies on the default naming of sequences, so check it against your own.
Without the sequence data, pg_dump also no longer needs SELECT on the sequences: with it included, it stopped with permission denied for sequence invoice_lines_id_seq; with --exclude-table-data='*_seq' it ran without that privilege. The application itself can still see the total — a nextval returned 311,206 — but the application is not the tenant. The leak is in handing the file over.
Can you restore a tenant’s dump under row-level security?
Not in the default format. As app, with the tenant set, loading the dump stopped at the first table:
ERROR: COPY FROM not supported with row-level security
HINT: Use INSERT statements instead.
The COPY documentation says as much, and the pg_dump documentation for --enable-row-security recommends the INSERT format for that reason. With --inserts or --rows-per-insert the restore went through the policies’ WITH CHECK, which is what you want when the target is a shared database:
| Restore of tenant 1 (60,000 invoices, 240,000 lines), one transaction | Time | Dump size |
|---|---|---|
| COPY format, as superuser | 9.1 s | 21.9 MB |
--rows-per-insert=1000, as app under RLS | 11.3 s | 25.2 MB |
--inserts (one row per statement), as app under RLS | 30.2 s | 36.6 MB |
The superuser row is there as a yardstick, not as an equal comparison: it skips the policies. All three dumps for the app restores were made with --exclude-table-data='*_seq'. Tenant 50 with --rows-per-insert=1000 took 0.24 s. With the tenant variable set to a different tenant, the restore refused on its first row: new row violates row-level security policy for table "tenants". The database will not load one tenant’s file into another.
What the setval does to a live database
This is the one that breaks production, for a while. A tenant’s dump carries the ids of its rows and the value of the shared counter at the moment of the dump. Restore it as a superuser into the live database — say, after an accidental delete — and the setval puts the counter back. We inserted an invoice (id 311,206), ran the setval line from the earlier dump, and inserted another:
ERROR: duplicate key value violates unique constraint "invoices_pkey"
DETAIL: Key (id)=(311206) already exists.
Each failed insert still uses up a number, so new invoices of every tenant fail until the counter has passed every id handed out since the dump — one in our test, thousands on a busy day. As app the same setval was refused (permission denied for sequence invoices_id_seq, since the application only has USAGE), which is the safe outcome; the danger is the superuser restore. Leave the sequence data out of tenant exports — the same --exclude-table-data='*_seq' — and the problem does not arise.
A checklist for exporting one tenant
pg_dump --enable-row-securityas the application role, with the tenant inPGOPTIONS. Never as a role that bypasses row security.- Check the row counts against the database before you hand anything over. An empty export is a valid file.
--exclude-table-data='*_seq': shared counters are neither the tenant’s data nor safe to restore.--rows-per-insertif the file will be loaded back under row-level security; COPY cannot be.- Restore with the tenant set in a single transaction (
psql -1), so a wrong tenant or a duplicate rolls back the whole restore.
How long removing the tenant afterwards takes, and what stays on disk when you do, is in deleting one tenant in PostgreSQL.
What we did not measure
The custom and directory formats (-Fc, -Fd) with pg_restore, tables without a tenant_id column that still belong to a tenant (join tables, files), a role per tenant instead of a session variable, and restoring into a database where the tenant’s ids are already used by someone else.
Sources: the PostgreSQL 17 documentation on pg_dump (--enable-row-security, consistency, --exclude-table-data) and COPY (“COPY FROM is not supported for tables with row-level security”), read on 1 October 2026. All results are our own, on PostgreSQL 17.10, on 1 October 2026.