Feedback-ID: i76984098:Fastmail
Message-ID: <cedab53f-e2c2-4c59-a7ba-279dc0c02034@aklaver.com>
Date: Mon, 15 Sep 2025 07:17:50 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How to use \restrict or \unrestirct in python
To: ma lz <ma100@hotmail.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <TY7PR01MB14856A5E385BBD81F8DAB2D64F215A@TY7PR01MB14856.jpnprd01.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <TY7PR01MB14856A5E385BBD81F8DAB2D64F215A@TY7PR01MB14856.jpnprd01.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/cedab53f-e2c2-4c59-a7ba-279dc0c02034%40aklaver.com>
Precedence: bulk

On 9/15/25 00:16, ma lz wrote:
> To fix CVE-2025-8714, PG introduce \restrict , but if we use python 
> ( like psycopg2), it does not support slash command.

The backslash commands are specific to psql:

https://www.postgresql.org/docs/current/app-psql.html

Per the release notes:

https://www.postgresql.org/docs/current/release-17-6.html

"
Since dump/restore operations typically involve running SQL commands as 
superuser, the target database installation must trust the source 
server. However, it does not follow that the operating system user who 
executes psql to perform the restore should have to trust the source 
server. The risk here is that an attacker who has gained superuser-level 
control over the source server might be able to cause it to emit text 
that would be interpreted as psql meta-commands. That would provide 
shell-level access to the restoring user's own account, independently of 
access to the target database.

To provide a positive guarantee that this can't happen, extend psql with 
a \restrict command that prevents execution of further meta-commands, 
and teach pg_dump to issue that before any data coming from the source 
server.
"

psycopg2/psycopg uses the same underlying library, libpq, as psql but 
psycopg does not support backslash commands. Therefore is cannot execute 
them directly in the manner explained above. Python in general can 
execute them indirectly by using something like:

subprocess.check_output()

to execute psql -f some_text_dump_file. It would be up to you to verify 
what is in the the dump file.


-- 
Adrian Klaver
adrian.klaver@aklaver.com