Streaming Replication Over SSL

On Thu, Dec 1, 2011 at 13:48, Samba <saasira@gmail.com> wrote:

Hi all,
I searched a lot to find if some one has written about this but could not
find any successful attempt, hence thought of posting it here.

setting the sslmode='require' in the 'primary_conninfo' parameter in the
recovery.conf file on standby server would make the standby server make an
SSL connection with the master server for streaming replication.

If we want to authenticate the master server before we fetch data from it,
then copy the CA certificate from the postgres server on master to
$PG_DATA/.postgresql directory as 'root.crt' and set the above mentioned
parameter to sslmode='verify-ca'.

complete string:
primary_conninfo='host=master port=5432 sslmode=require' or

primary_conninfo='host=master port=5432 sslmode=verify-ca'

However, I'm not sure how to confirm if the standby server is really making
the connection to master and fetching the XLOG contents over SSL. I tried
intercepting the traffic using wireshark but could not find any hint to that
effect; all it says is that the traffic is over tcp.

Can someone suggest any way to confirm that this setting would really make
streaming replication work of SSL?

It's still going to be TCP of course - just encrypted data over TCP.
You should see it being encrypted if you look inside the packages
themselves.

That said, you should ensure that it's encrypted from the server side
as well. Make sure the replication line in pg_hba.conf uses "hostssl"
and not "host". If it does, then you can be sure the connection is
encrypted, or else the user would not be able to log in.

(setting it on the standby should certainly be enough - but it's good
practice to set it on both ends)

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