agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pkopylov@cloudlinux.com
Subject: BUG #19718: pg_dump -Ft: restore.sql gets "\unrestrict (null)"/"\restrict (null)", so psql skips \i data files
Date: Thu, 24 Sep 2026 21:24:46 +0000
Message-ID: <19718-7e945d0ff9b5a589@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19718
Logged by:          Pavel Kopylov
Email address:      pkopylov@cloudlinux.com
PostgreSQL version: 17.11
Operating system:   Debian 13
Description:        

Since commit 71ea0d6795 ("Restrict psql meta-commands in plain-text dumps"),

```c
  _reconnectToDB() in src/bin/pg_dump/pg_backup_archiver.c writes

      ahprintf(AH, "\\unrestrict %s\n", ropt->restrict_key);
      ...
      ahprintf(AH, "\\restrict %s\n\n", ropt->restrict_key);
```
without checking ropt->restrict_key.

RestoreArchive() emits the same  markers only "if (ropt->restrict_key)".

pg_dump generates a restrict key only for --format=plain.  The tar format,
however, still writes a plain-text restore.sql through RestoreArchive() in
 _CloseArchive() (pg_backup_tar.c), using a copy of the dump's
 RestoreOptions, where restrict_key is NULL.  pg_dump always sets
 outputCreateDB for non-plain formats, so the DATABASE TOC entry always
 reaches _reconnectToDB(), and every tar-format restore.sql contains:

```
      \unrestrict (null)
      \connect srcdb
      \restrict (null)
```

  When the script is run with "psql -f restore.sql", the first line fails
  with "\unrestrict: not currently in restricted mode".  psql then enters
  restricted mode with the key "(null)" and rejects every later
  meta-command.  With pg_dump -Ft --inserts the table data is loaded through
  "\i $$PATH$$/NNNN.dat", so no table data is restored at all.

Reproduced on 18.6 and 17.11 (official Docker images).
The code on master is the same.

The logged output run on the official Docker image is:

```
$ cat repro-debian13.log
### Debian GNU/Linux 13 (trixie), postgresql-15-pllua postgresql-17
17.11-0+deb13u1postgresql-17-jit-llvm postgresql-17-pllua postgresql-9.1
+ createdb srcdb
+ psql -Xq srcdb -c 'CREATE TABLE t(i int); INSERT INTO t SELECT
generate_series(1,10)'
+ mkdir /tmp/x
+ cd /tmp/x
+ pg_dump -Ft --inserts srcdb -f d.tar
+ tar xf d.tar
+ sed -i 's|[$][$]PATH[$][$]|/tmp/x|g' restore.sql
+ dropdb srcdb
+ createdb srcdb
+ psql -X -d srcdb -f restore.sql
+ grep -i restrict
psql:restore.sql:37: error: \unrestrict: not currently in restricted mode
psql:restore.sql:72: error: backslash commands are restricted; only
\unrestrict is allowed
+ psql -XAt -d srcdb -c 'SELECT count(*) FROM t'
0
```

The expected output number MUST be 10 instead of 0.

view thread (2+ messages)  latest in thread

Message-ID: <19718-7e945d0ff9b5a589@postgresql.org>
Permalink:  ../19718-7e945d0ff9b5a589@postgresql.org/
Also on:    postgresql.org/message-id/19718-7e945d0ff9b5a589@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, pkopylov@cloudlinux.com
  Subject: Re: BUG #19718: pg_dump -Ft: restore.sql gets "\unrestrict (null)"/"\restrict (null)", so psql skips \i data files
  In-Reply-To: <19718-7e945d0ff9b5a589@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox