hapostgres / hapostgres/pg_auto_failover
Setting replication.password doesn't set the password in the primary's database instance
- 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
Assessment
This issue has not been assessed yet.