Peer authentication with postgresql #739

New Issue

ArchangeGabriel · 2023-08-08T22:46:06Z

ArchangeGabriel commented

2023-08-08 22:46:06 +00:00

What I'd like to do and why

I’m trying to setup borgmatic to backup my postgresql databases, that is all of them. Thus I’ve setup username to be postgres, but the backup fails with a peer authentication failure (and I’ve seen some other tickets with related failure — all of them being “fixed” by using something else than peer, which is not something I want to enable).

Indeed, I can see from the command line used by borgmatic that it tried --username postgres, but that is not supposed to work with peer authentication.

Instead, borgmatic should use the fact that it is running as root to setuid postgres for running pg_dump[all].

Other notes / implementation ideas

One would probably need to check how it works with NoNewPrivileges=yes, I’m not exactly sure whether this blocks setuid calls for the service (RestrictSUIDSGID is just about writing these bits to files, not making such calls).

For now I haven’t tried yet using the systemd script, but running manually with:

before_backup:
    - sudo -u postgres pg_dumpall --clean --if-exists > /var/lib/postgres/dump

and adding that file to the backups works. I’m enabling the timer now, we will see how it goes.

EDIT: If it does not work with NoNewPrivileges=yes, then I will just add a drop-in snippet to do the dump as ExecPre in the service.

### What I'd like to do and why I’m trying to setup borgmatic to backup my postgresql databases, that is all of them. Thus I’ve setup `username` to be `postgres`, but the backup fails with a `peer authentication failure` (and I’ve seen some other tickets with related failure — all of them being “fixed” by using something else than peer, which is not something I want to enable). Indeed, I can see from the command line used by borgmatic that it tried `--username postgres`, but that is not supposed to work with `peer` authentication. Instead, borgmatic should use the fact that it is running as root to setuid `postgres` for running `pg_dump[all]`. ### Other notes / implementation ideas One would probably need to check how it works with `NoNewPrivileges=yes`, I’m not exactly sure whether this blocks `setuid` calls for the service (`RestrictSUIDSGID` is just about writing these bits to files, not making such calls). For now I haven’t tried yet using the systemd script, but running manually with: ``` before_backup: - sudo -u postgres pg_dumpall --clean --if-exists > /var/lib/postgres/dump ``` and adding that file to the backups works. I’m enabling the timer now, we will see how it goes. EDIT: If it does not work with `NoNewPrivileges=yes`, then I will just add a drop-in snippet to do the dump as `ExecPre` in the service.

witten commented

2023-08-10 06:12:27 +00:00

Thanks for taking the time to file this! I'm generally following your proposed changes, but there's one bit I'm wondering about: How would borgmatic actually know to perform peer authentication? A new per-database configuration option? Perhaps authentication_method or similar?

Also, just so I understand your use case, can you describe your motivation for sticking with peer authentication as opposed to one of the other methods?

Thanks!

Thanks for taking the time to file this! I'm generally following your proposed changes, but there's one bit I'm wondering about: How would borgmatic actually know to perform peer authentication? A new per-database configuration option? Perhaps `authentication_method` or similar? Also, just so I understand your use case, can you describe your motivation for sticking with peer authentication as opposed to one of the other methods? Thanks!

ArchangeGabriel commented

2023-08-10 18:50:28 +00:00

I guess a new database option indeed. peer authentication is deemed more secure than other available methods. In general, I try to avoid any tcp listener if I can use a socket instead (e.g. https://serverfault.com/a/124518/421504).

Anyway, as I kind of expected, NoNewPrivileges=yes does forbid to setuid:

CRITICAL sudo: PERM_SUDOERS: setresuid(-1, 1, -1): Operation not permitted

So this idea won’t work (we would have to disable too much knob from the systemd service because a lot of them enforce NoNewPrivileges=yes), and instead I will use:

ExecStartPre=+/usr/bin/sudo -u postgres sh -c "/usr/bin/pg_dumpall --clean --if-exists > /var/lib/postgres/dump"

(I’ve tried /usr/bin/sudo -u /usr/bin/pg_dumpall --clean --if-exists > /var/lib/postgres/dump, but it failed with pg_dumpall: error : too much arguments on the command line (first one being « > »))

I’m thus closing this issue but I’m happy to provide a PR for documentation on this use case if you want and tell me where it should be.

I guess a new database option indeed. `peer` authentication is deemed more secure than other available methods. In general, I try to avoid any tcp listener if I can use a socket instead (e.g. https://serverfault.com/a/124518/421504). Anyway, as I kind of expected, `NoNewPrivileges=yes` does forbid to setuid: ``` CRITICAL sudo: PERM_SUDOERS: setresuid(-1, 1, -1): Operation not permitted ``` So this idea won’t work (we would have to disable too much knob from the systemd service because a lot of them enforce `NoNewPrivileges=yes`), and instead I will use: ``` ExecStartPre=+/usr/bin/sudo -u postgres sh -c "/usr/bin/pg_dumpall --clean --if-exists > /var/lib/postgres/dump" ``` (I’ve tried `/usr/bin/sudo -u /usr/bin/pg_dumpall --clean --if-exists > /var/lib/postgres/dump`, but it failed with `pg_dumpall: error : too much arguments on the command line (first one being « > »)`) I’m thus closing this issue but I’m happy to provide a PR for documentation on this use case if you want and tell me where it should be.

ArchangeGabriel closed this issue

2023-08-10 18:50:28 +00:00

witten commented

2023-09-11 23:10:33 +00:00

Thanks for the offer. I think for now it's fine leaving this use case officially unsupported, but I'm happy to change that stance if other users also have a need for peer authentication with Postgres and borgmatic.

Sign in to join this conversation.