Re: Security issues concerning pgsql replication

On Tue, Oct 27, 2020 at 9:52 AM xiebin (F) <xiebin18(at)huawei(dot)com> wrote:

> 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
> <http://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
> <http://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.
>

Why not? The user who can read that file, can also read the entire database
on the standby node already.

> 3. Using certificate.
>
> *cat pg_hba.conf*
>
> * host all all 0/0 md5*
>
> * hostssl replication xie 192.168.1.31/32
> <http://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?
>
>
>

PostgreSQL replication supports all authentication methods that
PostgeSQL supports for regular connections, in general. While I haven't
tried it, ssl_passphrase_command should work for this as well as long as it
doesn't require manual user interaction. But it could for example read the
passphrase from a pipe where it's provided off,or from a hardware device.
Do keep in mind that replication might need multiple authentications (for
example if the network disconnects, it has to reconnect).

You can also use for example GSSAPI and Kerberos to do the login. You will
then of course have to figure out how to securely authenticate the postgres
OS user on the standby node to the Kerberos system, but that's doable.
(Though I believe most Kerberos implementations also rely on filesystem
security to protect the tickets, so if you don't trust your filesystem, you
may have a problem with that -- as well as indeed most other authentication
systems -- so you'd have to investigate that within the kerberos system).

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>