hapostgres / hapostgres/pg_auto_failover

Setting replication.password doesn't set the password in the primary's database instance

Open
#382 6 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
1.4k
Forks
142
Avg merge
5h 8m
Merged PRs (30d)
1

Description

## Description
Following the steps in the documentation [here](https://github.com/citusdata/pg_auto_failover/blob/7ac18c5d0a07fb23738032bd74e5b8896a48cd81/docs/security.rst#authentication-with-passwords) to setup password based authentication is not enough without an added:
`psql -p ${PRIMARY_PORT} postgres -c "alter user pgautofailover_replicator password 'h4ckm3m0r3';`

This `ALTER` command needs to be run before the secondary is created and started:
```
$ export PGPASSWORD=h4ckm3m0r3
$ pg_autoctl create postgres \
--auth scram-sha-256 \
... \
--monitor postgres://autoctl_node:h4ckm3@monitor.host/pg_auto_failover

$ pg_autoctl config set replication.password h4ckm3m0r3
```

Log excerpt when we try to create the secondary (`pg_basebackup` fails in auth):
```
16:14:46 538293 INFO /usr/local/pgsql/bin/pg_basebackup -w -d application_name=pgautofailover_standby_5 host=127.0.0.1 port=8800 user=pgautofailover_replicator sslmode=prefer --pgdata /home/pivotal/failover/backup/node_5 -U pgautofailover_replicator --verbose --progress -
-max-rate 100M --wal-method=stream --slot pgautofailover_standby_5
16:14:46 538293 INFO pg_basebackup: error: could not connect to server: FATAL: password authentication failed for user "pgautofailover_replicator"
16:14:46 538293 ERROR Failed to run pg_basebackup: exit code 1
16:14:46 538293 ERROR Failed initialize standby server, see above for details
16:14:46 538293 ERROR Failed to transition from state "wait_standby" to state "catchingup", see above.

```

## RCA:

If we look at `pg_shadow` after `pg_autoctl config set replication.password h4ckm3m0r3`:

```
$ psql postgres -p ${PRIMARY_PORT} -c "select usename, passwd from pg_shadow where usename='pgautofailover_replicator';"
usename | passwd
---------------------------+--------
pgautofailover_replicator |
(1 row)
```
We see that `pgautofailover_replicator` is created w/ a blank password.
In the code ~~keeper_cli_create_replication_user~~ `primary_create_replication_user` creates the `pgautofailover_replicator` user with the password that it reads from the config during the course of a `create postgres`. At that time `replication.password` is undefined in the config.

## Conclusions and questions:

1. I think we have to either add `psql -p ${PRIMARY_PORT} postgres -c "alter user pgautofailover_replicator password 'h4ckm3m0r3';` to the documentation or have `pg_autoctl config set replication.password` do the ALTER for us.

2. Is there a way to specify the `replication.password` **before** doing `create postgres`? That way the replication user will be created with the right password during `create postgres`. It errors out if I do this before a `create postgres`:
```
$ PGDATA=~/failover/primary pg_autoctl config set replication.password 'h4ckm3m0r3'
16:30:10 557345 FATAL Expected configuration file does not exists: "/home/pivotal/.config/pg_autoctl/home/pivotal/failover/primary/pg_autoctl.cfg"
16:30:10 557345 WARN HINT: Check your PGDATA setting: "/home/pivotal/failover/primary"
```

3. `pg_autoctl do adduser replica` after a `pg_autoctl set replication.password` currently fails as:
```
$ PG_AUTOCTL_DEBUG=1 pg_autoctl do primary adduser replica --pgdata ~/failover/primary
16:22:09 547693 INFO The user "pgautofailover_replicator" already exists, skipping.
```

4. Currently, It seems that `pg_autoctl set replication.password` has no effect apart from writing the password to the config file.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.