# pg_dump fails with "query would be affected by row-level security policy"

> Why pg_dump stops on tables with RLS when the dumping role can't bypass it, what --enable-row-security really does, and the simplest way to get a full dump of a SupaHost project.

Source: https://supahost.app/kb/pg-dump-rls-error · Updated 2026-11-11

## What you see

```
pg_dump: error: Dumping the contents of table "messages" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR:  query would be affected by row-level security policy for table "messages"
pg_dump: detail: Command was: COPY public.messages (id, channel_id, author_id, body, created_at) TO stdout;
```

## Why it happens

pg_dump turns row security off for its session (`row_security = off`) so that it copies every row, not just the rows its role is allowed to see. With row security off, Postgres refuses to read a table with RLS enabled unless the role:

- owns the table (and the table doesn't use `force row level security`), or
- has the `BYPASSRLS` attribute, or is a superuser.

If neither is true, Postgres stops with this error rather than hand back a silently incomplete dump. You usually hit it when dumping with a custom role you created, such as a read-only reporting user, or with a tool that connects as `anon`/`authenticated`.

Check your role:

```sql
select current_user, rolbypassrls, rolsuper
from pg_roles where rolname = current_user;
```

## How to fix it

**On SupaHost, use the built-in export.** *Dashboard → your project → **Backups** tab → **Create pg_dump***. SupaHost runs pg_dump for you as an admin role, so RLS doesn't get in the way, and you get a custom-format `.dump` file with a download link that lasts 24 hours. Only the project owner can create exports. This works on every plan.

Restore it anywhere with `pg_restore`:

```bash
pg_restore --no-owner --no-privileges -d "$TARGET_DATABASE_URL" app-one-20261111-0930.dump
```

**On Business, if you run pg_dump yourself** over the SSH tunnel:

1. Open the tunnel: `ssh -N -L 5432:127.0.0.1:5432 supahost@acme.supahost.space`.
2. Get the connection string from *Dashboard → your project → **Connect** tab → Database → Show connection string*. It connects as `postgres` to `p_<project>`.
3. Run pg_dump 17 or newer (the server is Postgres 17):

```bash
pg_dump "postgresql://postgres:$DB_PASSWORD@127.0.0.1:5432/p_app_one" \
  -Fc --schema=public -f app-one.dump
```

Dump as `postgres`, not as a custom role; in the Supabase Postgres image `postgres` normally has `BYPASSRLS` (check with the query above). If you must use a role that can't bypass RLS, the error still tells you exactly which table it couldn't read.

## About `--enable-row-security`

This flag makes the error go away, but it doesn't give you a full backup. It tells pg_dump to leave row security on, so each table is dumped with your role's policies applied: you get only the rows that role can see. For `anon`, that is often nothing. Use it only when a filtered dump is what you want, for example to share one tenant's data, and never as a backup.

## Related

- [Exporting your data](/docs/platform/exports)
- [SSH access and direct Postgres](/docs/platform/ssh)
- [Backups](/docs/platform/backups)
- [Connecting directly to Postgres (psql, pg_dump, ORMs)](/kb/direct-postgres-connection)