Ohohohoh!!! I can set up the replication using SSL That's Cool.
Let's Do it then what we are waiting for ??????
Ohohohoh!!! I can set up the replication using SSL That's Cool.
Let's Do it then what we are waiting for ??????
What is SSL and why it is used?
SSL stands for Secure Sockets Layer and, in short, it's the standard technology for keeping a connection secure and protecting any sensitive data that is being sent between two servers, preventing criminals from reading and modifying any information transferred.
The two systems can be a server and a client or server to server.
Installed PostgreSQL-9.6 from the pgdg repo on Centos7.
First, create the certificates on the Master.
Create Directory for SSL
mkdir /var/lib/pgsql/9.6/data/ssl
cd /var/lib/pgsql/9.6/data/ssl
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key
chown -R postgres /var/lib/pgsql/9.6/data/ssl
Always it is recommended to create replication user for replication.
psql -c "create role replica with login replication password 'replication';"
Now set parameters in postgresql.conf and add replication entry in pg_hba.conf on Master.
postgresql.conf :
ssl = on
ssl_cert_file = '/var/lib/pgsql/9.6/data/ssl/server.crt'
ssl_key_file = '/var/lib/pgsql/9.6/data/ssl/server.key'
wal_level = hot_standby
archive_mode='on'
archive_command='cp %p /var/lib/pgsql/9.6/archive/%f'
max_wal_senders = 5
wal_keep_segments = 150
hot_standby_feedback = on
hot_standby = on #this parameter will not effect on Master. It is for standby server.
pg_hba.conf
hostssl replication replica 192.168.56.102/32 md5
Now restart the Master and take pg_basebackup to setup the replication.
systemctl restart postgresql-9.6
Backup :
pg_basebackup -D /var/lib/pgsql/9.6/replica -U replica -v -h 192.168.56.102 -R -xP
Start the replica cluster.
pg_ctl -D /var/lib/pgsql/9.6/replica/ start
recovery.conf of replica cluster
standby_mode = 'on'
primary_conninfo = 'user=replica password=replication host=192.168.56.102 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
restore_command='cp /var/lib/pgsql/9.6/archive/%f %p'
Here is the status of replicaion :
-[ RECORD 1 ]----+------------------------------
pid | 8054
usesysid | 16449
usename | replica
application_name | walreceiver
client_addr | 192.168.56.102
client_hostname |
client_port | 59200
backend_start | 2018-02-01 00:30:16.148848-05
backend_xmin |
state | streaming
sent_location | 0/5013A98
write_location | 0/5013A98
flush_location | 0/5013A98
replay_location | 0/5013A98
sync_priority | 0
sync_state | async
Now let's try to connect to Master using replica user and check if certificates are been used or not?
Create extension sslinfo;
postgres=# create extension sslinfo ;
CREATE EXTENSION
postgres=# \dx+ sslinfo
Objects in extension "sslinfo"
Object Description
------------------------------------
function ssl_cipher()
function ssl_client_cert_present()
function ssl_client_dn()
function ssl_client_dn_field(text)
function ssl_client_serial()
function ssl_extension_info()
function ssl_issuer_dn()
function ssl_issuer_field(text)
function ssl_is_used()
function ssl_version()
(10 rows)
Now connect using replica user over SSL.
psql (9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> select ssl_is_used();
ssl_is_used
-------------
t
(1 row)
postgres=> \q
WOW!!!! Great Replicaion setup is ready and it is Secure .....!!
