Troubles to setup postgresql dump #550

New Issue

dpalic · 2022-06-23T15:12:01Z

dpalic commented

2022-06-23 15:12:01 +00:00

What I'm trying to do and why

want to backup all local databases of postgresql. But failing.
Not sure if ISO layer 8 problem or really a bug.
Hope someone can help?

Steps to reproduce

config.yml

postgresql_databases:
    - name: all
      username: postgres

created a

~/.pgpass

*:*:*:postgres:password

tried password also in config.yml without success

run

borgmatic -v 2

Actual behavior (if a bug)

pg_dumpall: error: could not connect to database "template1": FATAL:  Peer authentication failed for user "postgres"
something@mybackuphost:./backups/servers/{reverse-fqdn}: Error running actions for repository
pg_dumpall: error: could not connect to database "template1": FATAL:  Peer authentication failed for user "postgres"
Command 'pg_dumpall --no-password --clean --if-exists --username postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1.
/etc/borgmatic/config.yaml: Retrying... attempt 1/3
/etc/borgmatic/config.yaml: Sleeping 300s before next retry

Expected behavior (if a bug)

to process the backups of all databases

if executing

sudo su - postgres
pg_dumpall --no-password --clean --if-exists --username postgres

or

login postgres # with password prompt
pg_dumpall --no-password --clean --if-exists --username postgres

pg_dumpall creates the database dumps and finishes successfully

Other notes / implementation ideas

Environment

borgmatic version: [version here]

borgmatic --version
1.6.3

borgmatic installation method: [e.g., Debian package, Docker container, etc.]

pip3

Borg version: [version here]

borg --version
borg 1.2.1

Python version: [version here]

python --version
Python 2.7.18

Database version (if applicable): [version here]

psql --version
psql (PostgreSQL) 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)

but tried also with 10

operating system and version: [OS here]

ubuntu 20.04 x64

#### What I'm trying to do and why want to backup all local databases of postgresql. But failing. Not sure if ISO layer 8 problem or really a bug. Hope someone can help? #### Steps to reproduce config.yml ``` postgresql_databases: - name: all username: postgres ``` created a ~/.pgpass ``` *:*:*:postgres:password ``` tried password also in config.yml without success run ``` borgmatic -v 2 ``` #### Actual behavior (if a bug) ``` pg_dumpall: error: could not connect to database "template1": FATAL: Peer authentication failed for user "postgres" something@mybackuphost:./backups/servers/{reverse-fqdn}: Error running actions for repository pg_dumpall: error: could not connect to database "template1": FATAL: Peer authentication failed for user "postgres" Command 'pg_dumpall --no-password --clean --if-exists --username postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1. /etc/borgmatic/config.yaml: Retrying... attempt 1/3 /etc/borgmatic/config.yaml: Sleeping 300s before next retry ``` #### Expected behavior (if a bug) to process the backups of all databases if executing ``` sudo su - postgres pg_dumpall --no-password --clean --if-exists --username postgres or login postgres # with password prompt pg_dumpall --no-password --clean --if-exists --username postgres ``` pg_dumpall creates the database dumps and finishes successfully #### Other notes / implementation ideas #### Environment **borgmatic version:** [version here] ``` borgmatic --version 1.6.3 ``` **borgmatic installation method:** [e.g., Debian package, Docker container, etc.] pip3 **Borg version:** [version here] ``` borg --version borg 1.2.1 ``` **Python version:** [version here] ``` python --version Python 2.7.18 ``` **Database version (if applicable):** [version here] ``` psql --version psql (PostgreSQL) 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1) ``` but tried also with 10 **operating system and version:** [OS here] ubuntu 20.04 x64

witten commented

2022-06-23 17:01:11 +00:00

I'm really not sure what might be going on here, especially since the pg_dumpall invocations run by borgmatic and manually without borgmatic look pretty identical. Here are some ideas for things to try:

I know you said this behavior happens when using ~/.pgpass as well, but I wonder if perhaps the database password in borgmatic's configuration file is incorrect. It's possible there's an issue with YAML misinterpreting a character in your password (say, a backslash) and passing a different password string to Postgres than intended.
It might be worth having a look at your database's pg_hba.conf. Are you perhaps using something like peer instead of md5 for the auth method for local connections?

I'm really not sure what might be going on here, especially since the `pg_dumpall` invocations run by borgmatic and manually without borgmatic look pretty identical. Here are some ideas for things to try: * I know you said this behavior happens when using `~/.pgpass` as well, but I wonder if perhaps the database password in borgmatic's configuration file is incorrect. It's possible there's an issue with YAML misinterpreting a character in your password (say, a backslash) and passing a different password string to Postgres than intended. * It might be worth having a look at your database's `pg_hba.conf`. Are you perhaps using something like `peer` instead of `md5` for the auth method for `local` connections?

witten added the

question / support

label 2022-06-23 17:01:21 +00:00

dpalic commented

2022-06-24 06:46:34 +00:00

Hi @witten,

thank you for your feedback

tried to change the password without special chars => no success on

.pgpass
config.yaml

checked pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

looks also good to me

is there a way to reproduce all steps? Parior to

pg_dumpall: error: could not connect to database "template1": FATAL:  Peer authentication failed for user "postgres"
Command 'pg_dumpall --no-password --clean --if-exists --username postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1.

for me it is not clear how the login context is switche

found one issue:
if I run

pg_dumpall --no-password --clean --if-exists --username postgres

as the same user as the borgmatic (in current case root) the command is failing with the above error message.
for both .pgpass and config.yaml is set

Any hints?

to exclude ISO layer 8 problems, I did

login postgres
passwd # old passwd => new passwd
exit
login postgres # new passwd
psql postgres postgres
\password postgres # => new passwd
exit
exit

changed to new password in .pgpass and in config.yaml

Hi @witten, thank you for your feedback tried to change the password without special chars => no success on - .pgpass - config.yaml checked pg_hba.conf ``` local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 ``` looks also good to me is there a way to reproduce all steps? Parior to ``` pg_dumpall: error: could not connect to database "template1": FATAL: Peer authentication failed for user "postgres" Command 'pg_dumpall --no-password --clean --if-exists --username postgres > /root/.borgmatic/postgresql_databases/localhost/all' returned non-zero exit status 1. ``` for me it is not clear how the login context is switche found one issue: if I run ``` pg_dumpall --no-password --clean --if-exists --username postgres ``` as the same user as the borgmatic (in current case root) the command is failing with the above error message. for both .pgpass and config.yaml is set Any hints? to exclude ISO layer 8 problems, I did ``` login postgres passwd # old passwd => new passwd exit login postgres # new passwd psql postgres postgres \password postgres # => new passwd exit exit ``` changed to new password in .pgpass and in config.yaml

dpalic commented

2022-06-24 08:17:14 +00:00

finally found the issue

needed to add hostname: localhost to the config and the process went through. Not obvious to me, but works now

finally found the issue needed to add hostname: localhost to the config and the process went through. Not obvious to me, but works now

dpalic closed this issue

2022-06-24 09:12:31 +00:00

witten commented

2022-06-24 16:46:32 +00:00

Glad to hear it's working now! I think the way the peer auth method works is that it checks the current Unix user. So if you're running borgmatic as root, yeah, that first line in pg_hba.conf won't work, since you need to auth to your database as user postgres.

When you added hostname to borgmatic's configuration, I think what that did was switch to using the third line of your pg_hba.conf since it's now a TCP connection rather than a local one.

Glad to hear it's working now! I think the way the `peer` auth method works is that it checks the current *Unix user*. So if you're running borgmatic as root, yeah, that first line in `pg_hba.conf` won't work, since you need to auth to your database as user `postgres`. When you added `hostname` to borgmatic's configuration, I think what that did was switch to using the *third* line of your `pg_hba.conf` since it's now a TCP connection rather than a local one.

Sign in to join this conversation.