Re: Security issues concerning pgsql replication - Mailing list pgsql-general

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?