Security issues concerning pgsql replication

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