Hello Hackers, While working on pgactive I re-read the dump/restore warning that 71ea0d6795 added to pg_dump, pg_dumpall and pg_restore:
Restoring a dump causes the destination to execute arbitrary code of the source superusers' choice. Scoping this to source superusers is right for the psql meta-command vector that commit addressed, since that executes on the client. It seems too narrow for the destination-server execution described in the same sentence: any source role that owns a dumped object can supply code the destination executes, and it runs with the privileges of the role performing the restore. Two cases I confirmed on master (9673a0aa92f), with the objects owned by a non-superuser and the restore run by a superuser: - A CHECK constraint is inlined into CREATE TABLE and evaluated as each row is loaded by COPY. - A stored generated column is omitted from the COPY column list, so its expression is recomputed during that same load. As the non-superuser: CREATE FUNCTION ck(i int) RETURNS bool LANGUAGE plpgsql AS $$ BEGIN RAISE WARNING 'ran as %', current_user; RETURN true; END $$; CREATE TABLE t (i int CHECK (ck(i))); INSERT INTO t VALUES (1); Dumping that and restoring as a superuser: WARNING: ran as postgres (RAISE NOTICE is not enough to see this — pg_dump emits SET client_min_messages = warning.) A domain's CHECK constraint behaves the same way. The attached patch widens the warning's scoping and adds those two examples. The mitigation is unchanged: inspect the dumped statements before restoring. The distinction matters when the source superusers are trusted but the source's ordinary object owners are not, which is the common shape of a multi-tenant source. Two judgment calls in the patch, both worth a second opinion: Index expressions are not an example of this, though they look like one. CREATE INDEX switches to the table owner and runs the expression in a security-restricted operation (a117cebd638), so it gains the owner nothing. I mention it because it was my first instinct, and may be someone else's. pg_upgrade's reference page carries a shorter form of the same warning. I left it alone, since pg_upgrade restores schema only — no COPY runs, so neither expression is evaluated. Happy to update it too if that reasoning is wrong. 71ea0d6795 was back-patched through 13, so presumably this should be as well, but I'll leave that to the committer. Disclaimer: Claude was used to re-verify the findings. -- Kind Regards, Yogesh Sharma
From d22f9c319e9bde2c43fd5bee7e3feae63115a402 Mon Sep 17 00:00:00 2001 From: Yogesh Sharma <[email protected]> Date: Mon, 24 Aug 2026 14:27:50 +0000 Subject: [PATCH v1] Clarify whose code a restore executes in the dump/restore warning The warning added by 71ea0d6795 says restoring a dump causes the destination to execute arbitrary code of the source superusers' choice. That understates the exposure: any source role that owns a dumped object can supply code the destination executes. A CHECK constraint is inlined into CREATE TABLE and enforced as each row is loaded by COPY. A stored generated column is omitted from the COPY column list, so its expression is recomputed during that same load. Both run with the privileges of the role performing the restore, which need not be the object's owner. A domain's CHECK constraint behaves the same way. The superuser scoping is apt for the psql meta-command vector that commit addressed, which executes on the client. It is too narrow for the destination-server execution described in the same sentence, which needs only a source object owner. The distinction matters when the source superusers are trusted but the source's ordinary object owners are not, as in a multi-tenant source. pg_upgrade's reference page carries a shorter form of this warning and is left alone: it restores schema only, so no COPY runs and neither expression is evaluated. The mitigation is unchanged: inspect the dumped statements before restoring. Keep the three pg_dump-family reference pages carrying this warning in sync. --- doc/src/sgml/ref/pg_dump.sgml | 16 ++++++++++------ doc/src/sgml/ref/pg_dumpall.sgml | 12 ++++++++---- doc/src/sgml/ref/pg_restore.sgml | 16 ++++++++++------ 3 files changed, 28 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 774be23b4f9..04be5e6675d 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -99,12 +99,16 @@ PostgreSQL documentation <warning> <para> Restoring a dump causes the destination to execute arbitrary code of the - source superusers' choice. Partial dumps and partial restores do not limit - that. If the source superusers are not trusted, the dumped SQL statements - must be inspected before restoring. Non-plain-text dumps can be inspected - by using <application>pg_restore</application>'s <option>--file</option> - option. Note that the client running the dump and restore need not trust - the source or destination superusers. + source superusers' choice, or of the choice of any role that owns a + dumped object. For example, a table owner can supply such code through + a <literal>CHECK</literal> constraint or a generated column expression, + which the destination evaluates as it loads the table's rows. Partial + dumps and partial restores do not limit that. If those roles are not + trusted, the dumped SQL statements must be inspected before restoring. + Non-plain-text dumps can be inspected by using + <application>pg_restore</application>'s <option>--file</option> option. + Note that the client running the dump and restore need not trust the + source or destination superusers. </para> </warning> diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index 238c87c13f5..f8cd894af43 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -69,10 +69,14 @@ PostgreSQL documentation <warning> <para> Restoring a dump causes the destination to execute arbitrary code of the - source superusers' choice. Partial dumps and partial restores do not limit - that. If the source superusers are not trusted, the dumped SQL statements - must be inspected before restoring. Note that the client running the dump - and restore need not trust the source or destination superusers. + source superusers' choice, or of the choice of any role that owns a + dumped object. For example, a table owner can supply such code through + a <literal>CHECK</literal> constraint or a generated column expression, + which the destination evaluates as it loads the table's rows. Partial + dumps and partial restores do not limit that. If those roles are not + trusted, the dumped SQL statements must be inspected before restoring. + Note that the client running the dump and restore need not trust the + source or destination superusers. </para> </warning> diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index b6c5299c36e..fbe59543487 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -72,12 +72,16 @@ PostgreSQL documentation <warning> <para> Restoring a dump causes the destination to execute arbitrary code of the - source superusers' choice. Partial dumps and partial restores do not limit - that. If the source superusers are not trusted, the dumped SQL statements - must be inspected before restoring. Non-plain-text dumps can be inspected - by using <application>pg_restore</application>'s <option>--file</option> - option. Note that the client running the dump and restore need not trust - the source or destination superusers. + source superusers' choice, or of the choice of any role that owns a + dumped object. For example, a table owner can supply such code through + a <literal>CHECK</literal> constraint or a generated column expression, + which the destination evaluates as it loads the table's rows. Partial + dumps and partial restores do not limit that. If those roles are not + trusted, the dumped SQL statements must be inspected before restoring. + Non-plain-text dumps can be inspected by using + <application>pg_restore</application>'s <option>--file</option> option. + Note that the client running the dump and restore need not trust the + source or destination superusers. </para> </warning> </refsect1> -- 2.55.0
