PostgreSQL

SafeGrd backs up PostgreSQL 13 and later from a read-only role. This page covers what a backup holds, the role to create, how each backup uploads only what changed, and what to know for Supabase, Amazon RDS and Neon.

The schema comes from pg_dump: types, tables, functions, views, indexes, constraints and triggers, exactly as Postgres describes them. The rows are copied over the wire with binary COPY, table by table, in chunks, so a table larger than the host's memory backs up without staging anything on disk. Both are read from one snapshot, so a database being written to during the backup still produces rows, row counts and sequence positions that agree.

• The host needs a pg_dump at least as new as the server: the PostgreSQL client package (postgresql-client-16 on Debian and Ubuntu, brew install libpq on macOS). SafeGrd finds versioned installs under /usr/lib/postgresql and Homebrew's keg-only libpq by itself. SAFEGRD_PG_DUMP names one explicitly. The password is passed to it in the environment, never on its command line.
• If pg_dump is missing, the backup copies row data over binary COPY, but the schema is incomplete. The CLI prints a warning, and Fire Drills fail because constraints, views, triggers, and enum types cannot be restored without pg_dump.
• Superuser privileges are not required unless specific extensions demand them. Standard database owner roles (such as those provisioned by AWS RDS or Cloud SQL) can back up, drill, and restore databases. Trusted extensions like pgcrypto and citext can be created directly by the owner role, while pre-installed cloud provider extensions and system tables remain untouched. Any extension requiring superuser privileges must be installed in the target database before restoring.
• Restores require an empty target database. A Supabase project is never empty, so a Supabase backup restores into Supabase's PostgreSQL image (how).

A drill on a database surface asserts table counts, row counts, column counts, installed extensions and that every role the schema names is in the backup. See Restore and Fire Drills.

Roles, owners and grants

The schema is dumped with each object's owner, its grants and its default privileges, and the policies on a table name the roles they apply to. The roles those name (owners, grantees, policy roles) are backed up as pg_dumpall --roles-only writes them, in roles.sql beside the schema, with their password hashes. The rest of the cluster's roles are not backed up.

• A restore creates the roles the target lacks before the schema runs, and prints each one as Created role. A created role is never a superuser or a replication role; its login and password are as they were, so an application role logs in on the new cluster as it did. safegrd restore --no-owner restores without owners and grants, so every object belongs to the restoring role; the roles are still created, because a policy cannot be restored without them.
• A restore run by a role that is not a superuser skips the owners and grants it may not set (an owner it is not a member of, a grant on an object it does not own), says how many, and gives those objects to the restoring role. A role it creates gets no CREATEDB, CREATEROLE or BYPASSRLS, which only a role holding them may set. Restore as a superuser to keep every owner and attribute.
• The daemon's sandbox drill creates the roles the same way and drops them with the sandbox. The role the sandbox URL connects as needs CREATEROLE when the cluster lacks a role the schema names. verify --sandbox-target keeps what it restored, roles included, for you to look at.
• roles_without_passwords: true on a surface, or --roles-without-passwords on safegrd backup, leaves the hashes out; a restore then creates the roles without a password. A backup role that may not read pg_authid (an RDS master user, Supabase's postgres) gets the same, and the backup says so. A surface SafeGrd backs up for you has no switch and keeps the hashes.
• The in-memory drill fails a snapshot whose schema names a role it does not carry, since no fresh cluster could load it. Snapshots taken before 2026-10-05 carry no roles; the next backup does.

Each backup uploads what changed

A PostgreSQL backup is a run of the surface's repository. Each table is one file of the run, cut into chunks of about 64 KiB, and a later run in the same month uploads and locks only the chunks that changed. A table nobody wrote, or one that only grows at the end, adds almost nothing. A table updated at random rows all over can add its whole size on every run. The snapshot detail in the console shows what each table uploaded.

Every run is a restore point: a restore brings the database back as that run read it, and nothing between two runs. Pass --format tar, or set format: tar on a surface, for one archive per backup instead. When you add a surface in the console, Backup type sets this: Incremental or Full. On hosted storage, the console can download a full backup as one file; an incremental one restores on a host (hosted storage). A SQLite database is incremental by default too (below). MySQL and MongoDB backups are one archive each.

--table can be given more than once. The tables come from one run, so they are consistent with each other, and load in one transaction that commits only if every table matched its SHA-256 and its row count. The target's other tables stay as they are. A table the target no longer has, such as one an agent dropped, is created as the run defined it, with its sequences, constraints and indexes, and its sequences are set to where the run left them. A table the target has must be empty and defined as it was. The command prints the run's time, the tables the restored ones point at that it did not load, and the sequences of an existing table that it did not move. restore --table public.orders --version 3 restores the third version find --table lists.

Skip tables nobody wrote

A run reads every table, because a database has no file times to compare. On a large database that read takes minutes and loads the server on every run. --change-log, or change_log: true on a surface, lets a run skip each table nothing wrote since the last run:

• It creates a safegrd schema in the database and a trigger on each table, which logs the table and the transaction once per transaction after an insert, update, delete or truncate. The role taking the backup needs to own the tables (or hold TRIGGER on them) and be able to create a schema, so it is not the read-only role above. A table the role cannot add the trigger to is read on every run, and the backup names it.
• A table is skipped only when the log shows no write to it since the last run, its definition is unchanged, and a run read it within the last 7 days. A restore of the database to an earlier point, a failover to another timeline, a renamed enum label or a server upgrade makes the next run read every table. Statistics counters are never used.
• Each transaction pays one row in the log for each table it writes, about 12 µs per table on a laptop running PostgreSQL 16. Rows are deleted once every backup that reads the log has seen them.
• PostgreSQL 13 or newer. Surfaces SafeGrd backs up itself do not offer it yet.
• DROP SCHEMA safegrd CASCADE; removes the log and every trigger.

Give SafeGrd a read-only role

SafeGrd only reads your database. Give it a role of its own with no write permissions, so that a leaked backup credential cannot change or delete the data it backs up.

pg_read_all_data is built in from PostgreSQL 14. It grants SELECT on every table, view and sequence and USAGE on every schema, including ones created later, so a new table is covered without anyone remembering to grant it. PostgreSQL 13 has no such role, so grant it explicitly and set the default for tables created later:

Run the ALTER DEFAULT PRIVILEGES statement too. Without it, tables created later are not readable by the role and are left out of future backups. pg_dump runs as the same role. Check that the role cannot write before you go on:

safegrd doctor checks this for every PostgreSQL surface the host can open, and safegrd claim checks the surfaces it adds. For a database SafeGrd backs up itself, the console checks it when the connection string is saved and shows the same advice. The line Surface <id> role warns when the role is a superuser, can create roles or databases, or can write to any table, names up to three of those tables, and prints the two lines above as the fix. A second line, Surface <id> row security, names the tables whose rows row-level security hides from the role. A role that is not a superuser cannot read password hashes, so roles are backed up without their passwords (roles).

A table the role cannot read does not fail the backup. The snapshot leaves it out, with its partitions and sequences, and the backup prints a warning naming it and the grant that would include it. Views and row-level security policies that read a left-out table are left out too, and named in a second warning. A restore or restore --to-sql leaves out the foreign keys that point into those tables and says which, and a Fire Drill of the snapshot passes. The snapshot records the list in left-out.json. Needs CLI v0.0.39.

• Row-Level Security: pg_read_all_data does not automatically bypass RLS. If a table has policies, the backup holds only the rows those policies let the role see. SafeGrd checks each table before it copies it: the backup prints every table RLS applied to, with the rows copied and the table's estimated size, and the console marks the table Row-level security in the snapshot. Grant the role BYPASSRLS, or back up as the tables' owner, to capture every row.
• Large objects: pg_read_all_data applies to relations rather than pg_largeobject. If your application relies on large objects (lo_*), test a restore in advance to verify complete coverage.
• Replicas: Configuring SafeGrd against a read replica offloads dump traffic from your primary writer, as backups require only read access.

Supabase

Supabase runs PostgreSQL with pre-installed schemas for auth, storage and realtime. To back it up from GitHub Actions, with no server of your own, follow Back up Supabase with GitHub Actions. Or let SafeGrd take the backup: Back up on SafeGrd under Surfaces in the console (how it runs). Use the session pooler address below either way.

Pick a Supabase project in that form signs you in to Supabase and lists your projects. Then choose how SafeGrd connects:

• Sign in through Supabase for each backup. No password is typed. SafeGrd holds the Supabase sign-in, encrypted, and for each backup asks Supabase for a read-only role whose password expires within minutes. The machine that takes the backup gets that role and never the sign-in. The backup reads as postgres through that role where the project allows it, and a table it still cannot read is left out and named. The SafeGrd app asks Supabase for Projects read and Database read and write: Supabase makes the role through a write call, even a read-only role. SafeGrd opens the database with the role before the surface is added and before each backup. If row-level security hides rows from it, SafeGrd refuses the sign-in, or fails that backup, and names the tables. If the database's firewall lets the backup machines in but not SafeGrd's server, that check cannot run, so allow both.
• Type the database password. SafeGrd holds the connection string, as for any other database. The project's postgres user reads every row.

A surface switches between the two from its Details: Credential replaces the sign-in with a connection string, and Sign in through Supabase does the reverse. If Supabase stops accepting the sign-in (you disconnected SafeGrd there, or removed the account's access to the project), the next backup fails before any machine starts and says so; Reconnect Supabase on the surface signs in again.

• Port 5432: Connect to the direct host, db.[PROJECT-REF].supabase.co:5432. It has only an IPv6 address unless you buy Supabase's IPv4 add-on, so a host with no IPv6 (GitHub's runners among them) uses the session pooler instead, aws-0-[REGION].pooler.supabase.com:5432 with user postgres.[PROJECT-REF]. Do not use the transaction pooler on port 6543: it shares server connections between clients, which breaks binary COPY and the one snapshot the backup reads every table from.
• SSL: Add ?sslmode=require to the database URL.
• Row-Level Security: pg_read_all_data does not bypass RLS. Either give the backup role BYPASSRLS or grant explicit SELECT policies on tenant tables, otherwise those tables back up with 0 rows. A project's own postgres user already has BYPASSRLS.
• Restoring: A Supabase project already holds Supabase's own schemas and its postgres user is not a superuser, so a new project cannot take the restore either. Restore into Supabase's PostgreSQL image as supabase_admin, following the guide's restore section. Stock postgres images lack extensions such as supabase_vault.

Amazon RDS and Aurora (PostgreSQL)

RDS restricts superuser access, but SafeGrd only needs a read-only role.

• Back up from a read replica: Point database_url at your replica endpoint. SafeGrd only reads, so using a replica keeps dump traffic off your primary writer.
• SSL certificates: Download Amazon's root certificate bundle (global-bundle.pem) and pass ?sslmode=verify-full&sslrootcert=/path/global-bundle.pem.
• Role creation: Create a read-only role with GRANT pg_read_all_data TO safegrd_backup;. Superuser access is not needed.

Neon

Neon is serverless Postgres with compute autosuspend.

• Use the direct endpoint: Use the direct connection string (e.g. ep-xyz.region.aws.neon.tech:5432) without the -pooler suffix so binary streaming works.
• SSL: Neon requires TLS. Add ?sslmode=require to the URL.
• Cold starts: If compute autosuspend is on, the initial connection wakes the instance. Allow a few seconds for cold starts.