Security issues concerning pgsql replication

On Tue, Oct 27, 2020 at 9:52 AM xiebin (F) <xiebin18@huawei.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&gt; 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&gt; 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&gt; 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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

Perhaps you misunderstand me.
It is not user-database, but master-slave interaction that I am concerning.
The master-slave replication proceeds continually and requires no manual interference. Both master and slave’s private key are involved, but ssl_passphrase_command is only used to parse passphrase of master’s private key. Pgsql cannot get slave’s private key automatically, so replication failed. I’ve tried and proved it did not work.

I refered to the list of pgsql’s authenticate methods but did not find an appropriate one for replication.
https://www.postgresql.org/docs/12/client-authentication.html

Xie Bin

发件人: Magnus Hagander [mailto:magnus@hagander.net]
发送时间: 2020年10月27日 17:00
收件人: xiebin (F) <xiebin18@huawei.com>
抄送: pgsql-general@postgresql.org; zhubo (C) <zhubo31@huawei.com>; Zhuzheng (IT) <zhuzheng@huawei.com>; houxiaowei <brian.hou@huawei.com>; yangshaobo (A) <yangshaobo6@huawei.com>; mapinghu <mapinghu@huawei.com>; Songyunpeng <songyunpeng@huawei.com>; luoqi (F) <luoqi25@huawei.com>
主题: Re: Security issues concerning pgsql replication

On Tue, Oct 27, 2020 at 9:52 AM xiebin (F) <xiebin18@huawei.com<mailto:xiebin18@huawei.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&gt; 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&gt; 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&gt; 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/&lt;http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/&lt;http://www.redpill-linpro.com/&gt;

On Tue, Oct 27, 2020 at 12:34 PM xiebin (F) <xiebin18@huawei.com> wrote:

Perhaps you misunderstand me.

It is not user-database, but master-slave interaction that I am
concerning.

The master-slave replication proceeds continually and requires no manual
interference. Both master and slave’s private key are involved, but
ssl_passphrase_command is only used to parse passphrase of master’s private
key. Pgsql cannot get slave’s private key automatically, so replication
failed. I’ve tried and proved it did not work.

Ah yeah, you're right. For the client, there is an API for a callback, but
yeah, not a connection string one.

So your method around that would be to use an openssl engine for storage of
it, such as a smartcard (that one you can set in the sslkey parameter).

I refered to the list of pgsql’s authenticate methods but did not find an
appropriate one for replication.

https://www.postgresql.org/docs/12/client-authentication.html

Replication uses the *same* authentication methods as client connections.
There is no difference.

So you can use for example GSSAPI instead of clientcert, which would move
the responsibility over to your Kerberos system. Then you can set that one
up to require you to manually type in a password or equivalent to get t a
ticket, and configure expiry on that ticket.

//Magnus

*发件人:* Magnus Hagander [mailto:magnus@hagander.net]

I know when I set it up with a password protecting the private key it would prompt me for the password when I started up the service.

Susan Joseph
sandajoseph@verizon.net

-----Original Message-----
From: xiebin (F) <xiebin18@huawei.com>
To: Magnus Hagander <magnus@hagander.net>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; zhubo (C) <zhubo31@huawei.com>; Zhuzheng (IT) <zhuzheng@huawei.com>; houxiaowei <brian.hou@huawei.com>; yangshaobo (A) <yangshaobo6@huawei.com>; mapinghu <mapinghu@huawei.com>; Songyunpeng <songyunpeng@huawei.com>; luoqi (F) <luoqi25@huawei.com>
Sent: Tue, Oct 27, 2020 7:34 am
Subject: 答复: Security issues concerning pgsql replication

#yiv6531606513 #yiv6531606513 -- _filtered {} _filtered {} _filtered {} _filtered {} _filtered {} _filtered {} _filtered {}#yiv6531606513 #yiv6531606513 p.yiv6531606513MsoNormal, #yiv6531606513 li.yiv6531606513MsoNormal, #yiv6531606513 div.yiv6531606513MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:宋体;}#yiv6531606513 a:link, #yiv6531606513 span.yiv6531606513MsoHyperlink {color:blue;text-decoration:underline;}#yiv6531606513 a:visited, #yiv6531606513 span.yiv6531606513MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv6531606513 p.yiv6531606513gmail-m4735437323743741455msolistparagraph, #yiv6531606513 li.yiv6531606513gmail-m4735437323743741455msolistparagraph, #yiv6531606513 div.yiv6531606513gmail-m4735437323743741455msolistparagraph {margin-right:0cm;margin-left:0cm;font-size:12.0pt;font-family:宋体;}#yiv6531606513 span.yiv6531606513EmailStyle18 {font-family:sans-serif;color:#1F497D;}#yiv6531606513 .yiv6531606513MsoChpDefault {font-family:sans-serif;} _filtered {}#yiv6531606513 div.yiv6531606513WordSection1 {}#yiv6531606513 Perhaps you misunderstand me. It is not user-database, but master-slave interaction that I am concerning. The master-slave replication proceeds continually and requires no manual interference.  Both master and slave’s private key are involved, but ssl_passphrase_command is only used to parse passphrase of master’s private key. Pgsql cannot get slave’s private key automatically, so replication failed. I’ve tried and proved it did not work.   I refered to the list of pgsql’s authenticate methods but did not find an appropriate one for replication. https://www.postgresql.org/docs/12/client-authentication.html   Xie Bin   发件人: Magnus Hagander [mailto:magnus@hagander.net]
发送时间: 2020年10月27日 17:00
收件人: xiebin (F) <xiebin18@huawei.com>
抄送: pgsql-general@postgresql.org; zhubo (C) <zhubo31@huawei.com>; Zhuzheng (IT) <zhuzheng@huawei.com>; houxiaowei <brian.hou@huawei.com>; yangshaobo (A) <yangshaobo6@huawei.com>; mapinghu <mapinghu@huawei.com>; Songyunpeng <songyunpeng@huawei.com>; luoqi (F) <luoqi25@huawei.com>
主题: Re: Security issues concerning pgsql replication       On Tue, Oct 27, 2020 at 9:52 AM xiebin (F) <xiebin18@huawei.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   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.
    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   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 instructionshttps://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 configurationssl_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/
 Work: https://www.redpill-linpro.com/