Hi,
I was setting up a master/slave pgsql(version 12.4) cluster using stream replication. I found 3 ways to authenticate, but all of them has some security issue.
1. Disable authentication.
cat pg_hba.conf
host all all 0/0 md5
host replication xie 192.168.1.31/32 trust
In this case, untrusted users on slave may use pg_basebackup to stole data.
2. Using password.
cat pg_hba.conf
host all all 0/0 md5
host replication xie 192.168.1.31/32 md5
cat /var/lib/pgsql/.pgpass (on slave)
192.168.1.30:5432:xie:mydb:xie
In this case, the password is stored unencrypted. File access control may help, but it’s not secure enough.
3. Using certificate.
cat pg_hba.conf
host all all 0/0 md5
hostssl replication xie 192.168.1.31/32 cert clientcert=1
cat postgresql.conf | grep ssl
ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_key_file = 'server.key'
cat recovery.conf
primary_conninfo = 'host=192.168.1.30 port=5432 user=xie application_name=stream_relication sslrootcert=/tmp/root.crt sslcert=/tmp/xie.crt sslkey=/tmp/xie.key'
restore_command = ''
recovery_target_timeline = 'latest'
primary_slot_name = 'rep_slot'
The certificates are created by official instructions https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CERTIFICATE-CREATION. But the private key is not encrypted.
I noticed in psql 11+ version, a new configuration ssl_passphrase_command is added, so that encrypted private key can be used.
But as far as I know, encrypted private key is not supported in stream replication.
I wonder if there is another way to authenticate in replication? Or does pgsql has any plan to support encrypted private key in replication?
Xie Bin
