Bug #23195
open
Drop \restrict and \unrestrict directives from structure.sql
Description
Postgresql recently (2025-08-25, e.g., https://www.postgresql.org/docs/release/14.19/) added support for \restrict and \unrestrict directives, and started adding them to pg_dump output as a security measure to ensure pg_load doesn't run meta-commands (like running a shell command) when loading a dump made from a compromised server.
- this protection is not especially relevant since anyone with enough access to the git repo to get malicious content into structure.sql can also remove or work around the
\restrictdirectives. - if we include the
\restrictdirectives, setting up a new database (e.g., installing a new cluster) won't work unless the postgresql server is up-to-date.
It's certainly better for the postgresql server to be up-to-date when installing, but I'm not sure it should be a hard requirement.
We should consider removing these directives until 2025-08-25 counts as old enough to stop supporting.
Currently, there are no \restrict tags in source:services/api/db/structure.sql, but any time we write/run a migration on a dev system (that has latest postgresql) they will be added automatically, and the developer will need to decide whether to commit them.
Updated by Tom Clegg 6 months ago
23195-restrict @ 8bc693f2aff4a556bb8f90a50ce60ff962704594
Updated by Brett Smith 6 months ago
Tom Clegg wrote:
It's certainly better for the postgresql server to be up-to-date when installing, but I'm not sure it should be a hard requirement.
As a practical matter I feel like the requirement is we need to support the oldest PostgreSQL in the distributions we support. Right now that's 14.17. See Distribution_dependency_versions.
Might be worth a quick check to see whether Ubuntu has maybe backported this change to their PostgreSQL packages, the versions are close enough and this being security-related at least makes it plausible. If they have, then I think we don't need to worry about this. Otherwise I do think it's an active concern until we drop support for that distribution (hopefully next year).
Updated by Tom Clegg 6 months ago
docker run --rm -it ubuntu:22.04 apt update apt install postgresql-server ... Get:29 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 postgresql-14 amd64 14.19-0ubuntu0.22.04.1 [16.2 MB] ...
debian:12 is at 15.14 which introduces \restrict
rockylinux:9 is at 13.22 which introduces \restrict
So I think this means we're good?
Updated by Brett Smith 6 months ago
Tom Clegg wrote in #note-3:
rockylinux:9 is at 13.22 which introduces
\restrict
For RHEL as long as the version we need is available from AppStreams (which it is, both 8 and 9 have access to PostgreSQL 15) then we're good, we don't even need to worry about the "stock" version.
So I think this means we're good?
I think so too.
Updated by Brett Smith 6 months ago
Okay, I misunderstood what was going on here and how this relates to the PostgreSQL release cycle. Here's the big picture:
On 2025-08-25, all supported releases of PostgreSQL—which is 13 through 18—got a point release that adds this security feature. 13.22, 14.19, 15.14, etc.
So the question is not whether our supported distros have access to this feature; they all do. The question is whether the system installing arvados-api-server (and therefore loading structure.sql) has installed the security update that adds \restrict. (Note because this feature is implemented entirely in psql, only the client version matters, not the server.)
2025-08-25 is juuust recent enough to make me uneasy. I propose we do the following:
- For 3.2.0, just manually make sure we don't add
\restricttostructure.sql. - After 3.2.0, update source:services/api/fpm-info.sh to add a version requirement for the corresponding point release of the PostgreSQL client to each distribution so we don't have to worry about this going forward.
This ticket becomes "do the second bullet point." That change can go whatever the next release is, whether it's 3.2.1 or 3.3.0 or 4.0.0.