SSL Encrypted Transmission

Data transmission layer encryption is used to encrypt communication between the client and the database server, enhancing data transmission security. The pg_hba.conf configuration file allows administrators to specify how link encryption is between the client and the server.

An SSL connection can encrypt all data transmitted on the network, including: passwords, query statements, and returned results. Clients can force SSL to connect to the server.

Configuration instructions

Note! SSL connection encryption requires OpenSSL to be installed on both the client and the server side and this support is turned on at compile time.

The configuration example of pg_hba.conf is as follows:

hostssl  all             mxadmin         10.51.0.40/32           scram-sha-256

Use Example

Install openssl
```
sudo yum install openssl openssl-devel
```
Enter the cluster directory and use openssl to issue a certificate
```
cd $MASTER_DATA_DIRECTORY
```
Generate server request certificate
```
openssl req -new -text -out server.req
```
Note! PEM pass phrase cannot be empty, if input 123456, others can be empty
Generate password protected keys and unlock them
```
openssl rsa -in privkey.pem -out server.key
```
Note! Enter the value of PEM pass phrase entered above

Turn the certificate into a self-signed certificate.

openssl req -x509 -in server.req -text -key server.key -out server.crt

Increase key permissions
```
chmod og-rwx server.key
```
Configure the YMatrix server. Set ssl in postgresql.conf to on to get the YMatrix server to start with SSL support.
```
gpconfig -c ssl -v off -m on
gpstop -u
gpconfig -s ssl 
```

Use tcpdump to catch the package

tcpdump -i enp33s0f0 -nn port 5432 -w /tmp/http.tcpdump

Client Verification: The ssl connection is effective (using another machine as the client)
```
psql -h dev-150 -p 5432 -d postgres -U mxadmin
```

Create a table and insert data

create table abcd(a text);
insert into abcd values('123456789');

Use wireshark to open the packet capture file Find the inserted character in the '123456789' style in the package caught on wireshark (this field cannot be found)
```
sudo yum -y install wireshark 
sudo yum -y install wireshark-gnome
wireshark /tmp/http.tcpdump
```
Effect comparison

ssl = no
ssl = off

SSL Encrypted Transmission

Configuration instructions

Use Example

Effect comparison