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
[email protected]
-----Original Message-----
From: xiebin (F) <[email protected]>
To: Magnus Hagander <[email protected]>
Cc: [email protected] <[email protected]>; zhubo (C)
<[email protected]>; Zhuzheng (IT) <[email protected]>; houxiaowei
<[email protected]>; yangshaobo (A) <[email protected]>; mapinghu
<[email protected]>; Songyunpeng <[email protected]>; luoqi (F)
<[email protected]>
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:[email protected]]
发送时间: 2020年10月27日 17:00
收件人: xiebin (F) <[email protected]>
抄送: [email protected]; zhubo (C) <[email protected]>; Zhuzheng (IT)
<[email protected]>; houxiaowei <[email protected]>; yangshaobo (A)
<[email protected]>; mapinghu <[email protected]>; Songyunpeng
<[email protected]>; luoqi (F) <[email protected]>
主题: Re: Security issues concerning pgsql replication On Tue, Oct 27,
2020 at 9:52 AM xiebin (F) <[email protected]> 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/
