DB restore fails due to file permissions of `postgres.auto.conf`

[bmironov]

Hello,

Whole process of restoring DB from backup can fail on last step of recovering postgres.auto.conf due to restrictive file permissions:

{"level":"info","ts":"2025-12-25T09:03:10.30929347+07:00","logger":"pgbackrest restore","msg":"WARN: unknown user in backup manifest mapped to current user","pipe":"stderr","logging_pod":"db-is-adv-dev-16-1-full-recovery"}
{"level":"info","ts":"2025-12-25T09:03:10.309331306+07:00","logger":"pgbackrest restore","msg":"WARN: unknown group in backup manifest mapped to current group","pipe":"stderr","logging_pod":"db-is-adv-dev-16-1-full-recovery"}
{"level":"info","ts":"2025-12-25T09:03:12.798950572+07:00","logger":"pgbackrest restore","msg":"ERROR: [041]: unable to open file '/var/lib/postgresql/data/pgdata/postgresql.auto.conf' for write: [13] Permission denied","pipe":"stderr","logging_pod":"db-is-adv-dev-16-1-full-recovery"}

Above error happens on PostgreSQL 16 vanilla image supplied with cnpg. The file has read only (400) permissions.
This is not happening with PostgreSQL 17, which comes with read/write permissions (600). PostgreSQL 18 has even more relaxed permissions of 660.

I'm not sure in root cause of the issue, but looks like sidecar is running under different from postgres:tape system ID. Hence above WARN messages,

Corresponding issue is opened for cnpg itself in case of issue on CNPG-I API level.

Best regards

[Agalin]

It's a "known issue" to me from the beginning but I believe it wasn't documented anywhere. 🙁

CNPG provides its own protection mechanism preventing ALTER SYSTEM for Postgresql versions older than 17. You don't encounter the problem with 17+ as those have native support for blocking those calls.

This protection is implemented in such a way that the postgres.auto.conf is always created (or recreated if removed) and set to have read-only permissions. This breaks pgbackrest as it needs to override the file.

Luckily, there is a solution. 🙂 You just need to enable ALTER SYSTEM.

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
spec:
  postgresql:
    enableAlterSystem: true

You can disable it again after restore.

I'll leave the issue open until docs are updated.

[bmironov]

Hi @Agalin ,

Thank you for your response. It feels like quite cumbersome solution for simple task of DB restore.

I would like to drive your attention to first two lines from presented above log:

WARN: unknown user in backup manifest mapped to current user
WARN: unknown group in backup manifest mapped to current group

My guess here is that sidecar pgBackrest runs under some ID that is different from postgres:tape. I tested myself PostgreSQL DB restore via pgBackrest tool running on virtual machine with postgres.auto.conf having 400 permissions and everything was fine as long as pgBackrest was running by user whose ownership was set to above file.

Therefore this issue seems to be just a question of which user is running the pgBackrest utility. In my humble opinion it could be either CNPG-I issue that plugin inherited as Barman Cloud clone or some setting(s) of cnpg-plugin-backrest itself.

[melancholictheory]

Confirming this still reproduces on plugin v0.6.0 + CNPG 1.27.1 (PostgreSQL 16): the recovery bootstrap fails with

ERROR: [041]: unable to open file ".../pgdata/postgresql.auto.conf" for write: [13] Permission denied

(pgBackRest exit 41), and the *-full-recovery job CrashLoops so the cluster is stuck Setting up primary.

Thanks @Agalin — spec.postgresql.enableAlterSystem: true is a clean fix. For anyone who would rather keep ALTER SYSTEM disabled (the CNPG default on PG < 17) and not toggle it per-restore, here is an alternative that also works, driven entirely from the Archive CR:

• Backup Archive: configuration.data.additionalCommandArgs: ["--exclude=postgresql.auto.conf"] — keep the CNPG-managed (and near-empty) postgresql.auto.conf out of the repository, so on restore there is no read-only file to overwrite and pgBackRest creates a fresh writable one.
• Recovery Archive: configuration.restore.additionalCommandArgs: ["--type=none"] — let CNPG (not pgBackRest) write the recovery configuration.

Notes from testing:

• --type=none alone is not enough — pgBackRest still rewrites postgresql.auto.conf and hits the same exit 41.
• --exclude is only valid for backup; passing it to restore fails with ERROR: [031]: option 'exclude' not valid for command 'restore'.
• With both options a full restore (and a separate-WAL-volume cluster) comes up healthy with data intact on PG 16.

[ardentperf]

collecting a few thoughts on this ~

• issue goes away in a few years on its own, as old versions of pg are eventually deprecated
• CNPG does allow users to config their DB to enable alter system. if we fully disable backups of this file, some users may not realize its not getting backed up.
• not sure we want to disable file backup across the board for a problem that doesnt exist in pg17+
• the whole CNPG feature around using filesystem privs to block alter system on older systems is a bit problematic in some cases, not everyone feels like this is a great design direction in the first place (but again, it goes away in a few years on its own)

at a minimum though, this seems worth documenting in the plugin docs