You should not back up a database as its superuser. But a role with only SELECT writes dumps that are quietly incomplete, and the gap between those two is what this page is.

The failures that say nothing

Most missing privileges are loud: the dump stops, nothing is written, and you find out immediately. Three are not, and they are the reason this page exists.

MySQL and MariaDB · read the trigger definitions

the dump program exits 0 and the dump carries no triggers. Nothing in the output says so

The grant that fixes it

GRANT TRIGGER ON {database}.* TO {role};

MySQL · read a stored routine it did not define

the dump program exits 0 and writes a dump with every stored routine missing from it. Nothing in the output says so: a backup that looks fine has quietly lost every function and procedure

The grant that fixes it

GRANT SHOW_ROUTINE ON *.* TO {role};

MariaDB · read a stored routine it did not define

the dump program exits 0 and writes a dump with every stored routine missing from it. Nothing in the output says so: a backup that looks fine has quietly lost every function and procedure

The grant that fixes it

GRANT SHOW CREATE ROUTINE ON *.* TO {role}; -- before 11.3: GRANT SELECT ON mysql.proc TO {role};

A backup that looks fine, exits 0, has a plausible size and a valid checksum — and has lost every function, every procedure and every trigger in the database. You would find out at the restore, which is the worst possible moment to find out anything.

sloop doctor asks, so you do not find out later

Rather than discovering this during a backup, doctor asks each registered connection what its role can really do — and prints the exact GRANT for anything missing:

exit 8

$ sloop doctorPrivileges  Every engine wants more than SELECT, and not every gap is loud: short of one grant,  MySQL and MariaDB write a dump with your triggers and stored routines left out of it,  say nothing, and exit 0. So sloop asks each registered connection what its role can  really do, rather than finding out during a backup.   weak  postgres://weak@127.0.0.1:5450/shop    postgres 17.9, connected as weak    — to back this database up: 1 of 4 missing      ✗ read every table, view and sequence          2 of 2 cannot be read: public.customers, public.customers_id_seq          pg_dump stops on the first table it cannot lock, and the dump is not written      grant:        GRANT pg_read_all_data TO weak;    — to restore into it: 2 of 2 missing      ✗ create a schema      ✗ enter and create in every schema          cannot enter or create in: public      grant:        GRANT CREATE ON DATABASE shop TO weak;        GRANT USAGE, CREATE ON SCHEMA public TO weak;    — to delete it from the server: 1 of 1 missing      ✗ own the database          owned by postgres          `sloop db drop` is refused with `must be owner of database`, after everything          else about the run has already worked      grant:        ALTER DATABASE shop OWNER TO weak; $ echo exit $?exit 8

Exit 8 exists for this. A health check that exits 0 while a role is quietly writing incomplete dumps is worse than no health check, so doctor has a code of its own and automation can act on it.

Run the lines it printed — and only those — and it changes its mind:

exit 0

# exactly the GRANT lines it printed, and nothing else$ sloop doctorPrivileges  …   strong  postgres://strong@127.0.0.1:5450/shop    postgres 17.9, connected as strong    — ready to back this database up    — ready to restore into it    — ready to delete it from the server   weak  postgres://weak@127.0.0.1:5450/shop    postgres 17.9, connected as weak    — ready to back this database up    — ready to restore into it    — to delete it from the server: 1 of 1 missing      ✗ own the database          owned by strong          `sloop db drop` is refused with `must be owner of database`, after everything          else about the run has already worked      grant:        ALTER DATABASE shop OWNER TO weak; $ echo exit $?exit 0

It asks three questions, not one: can this role back the database up, can it restore into it, and can it delete it from the server. A role that dumps a database perfectly may be unable to restore one, and a role that can do both may still be refused at db drop — so they are reported apart, because they are needed at different times.

The third one has exactly one answer per database. PostgreSQL has no GRANT DROP: a role may drop a database when it owns it, and one role owns it. That is why weak above is still short of that one line after everything else was granted — and why the run exits 0 anyway. Only the first question reaches the exit code, because a role that takes complete backups is a healthy role; the other two are reported so that nothing is discovered at the moment it matters.

What each engine needs

Everything below is read from the same table doctor checks against. It is not a transcription of it — editing the check changes this page, and a row here that the code does not check cannot exist.

To back a database up

Everything backup needs, and the source half of mirror and sync.

reach the databasealways: nothing can connect at all; CONNECT; GRANT CONNECT ON DATABASE {database} TO {role};
read every table, view and sequencealways: pg_dump stops on the first table it cannot lock, and the dump is not written; USAGE on each schemaSELECT on each table and sequence; GRANT pg_read_all_data TO {role};
see every row of a table with row-level securityonly with a table that has row-level security enabled: pg_dump sets row_security to off so that it dumps whole tables, and a role that cannot bypass the policy fails rather than silently dumping a subset; BYPASSRLS; ALTER ROLE {role} BYPASSRLS;
read the large objectsonly with a large object: pg_dump carries large objects by default and stops on the first one it cannot open; SELECT on each large object; -- per object: GRANT SELECT ON LARGE OBJECT <oid> TO {role};

To restore into one

A role that dumps a database perfectly may be unable to restore one. Different job, different grants.

create a schemaalways: a restore stops at the first CREATE SCHEMA in the dump; CREATE on the database; GRANT CREATE ON DATABASE {database} TO {role};
enter and create in every schemaalways: a restore stops partway with `permission denied for schema` — on PostgreSQL 15 and newer this bites on `public`, which no longer grants CREATE to everybody; USAGE on each schemaCREATE on each schema; GRANT USAGE, CREATE ON SCHEMA public TO {role};
create the extensions this database usesonly with an extension that is not trusted: a dump of this database carries CREATE EXTENSION for an extension PostgreSQL does not mark trusted, and only a superuser can run that — restoring elsewhere needs the extension installed first; superuser; -- a superuser runs: CREATE EXTENSION <name>; on the destination first

To delete it from the server

Ownership, not a grant — and the one question doctor used to leave unasked, until a drop was refused by a role it had just passed.

own the databasealways: `sloop db drop` is refused with `must be owner of database`, after everything else about the run has already worked; ownership of the databaseor superuser; ALTER DATABASE {database} OWNER TO {role};

In practice

Make the role, run sloop doctor, paste what it prints. That is the whole workflow — and sloop db create skips it entirely by making the database and a role that already has what it needs.

doctor also checks the client tools and sloop's own PostgreSQL; the command reference has its flags.