From data encryption to PostgreSQL SSL authentication connection

In order to ensure the security performance of the database connection, PostgreSQL allows the use of SSL certificates to connect to the database, which undoubtedly greatly enhances the security performance of data access.
Before explaining the use of SSL in PostgreSQL, let's first understand what an SSL certificate is.

1. Data encryption

First of all, let's talk about what is data encryption.

1.1, symmetric encryption

Data encryption is also closely related to our lives. Maybe you don’t know what encryption algorithm is, but you may have had this experience: when you are studying, there will always be people trying to cheat every time you take an exam. Of course I I don’t think anyone will be so stupid as to talk under the eyelids of the invigilator during the exam. Choose A for the first question and B for the second question. This stupid method is what we often call "plain text".

What is the way you often see it? For example, before the exam, A and B agree to each other's "password"-"Touching your head means choosing A, touching your chin means choosing B, etc.". Then this secret code is the "key". Through this key, we transform the message "ABCD" we want to transmit into "touching the head and touching the chin". With this key, we can cheat openly.

This encryption method is symmetric encryption, that is, A encrypts the data with a key, and B decrypts the data with the same key.

1.2, asymmetric encryption

Through the method just now, we can already encrypt the data and transmit it, but there is a problem. In case student A and student B secretly agree on the "secret code" before the exam, they accidentally be heard by the monitoring teacher, then wait until the exam. When they were "touching their heads and chins" for data transmission, the monitoring teacher could tell at a glance that they were cheating.

The previous method of encryption through the "public key" is obviously very low-security. As long as the key is known by a third party, the data is equivalent to "plaintext" transmission.

Since this method is insufficient in security, people think of another way to encrypt data through "public key" and "private key". That is, everyone will generate a pair of their own "public key" and "private key", the "private key" is kept by themselves, and the "public key" is used to send to others.

E.g:

A说:”我的公钥是sdsfj$&!sds,请用这个公钥进行加密”
B说:”好的,加密后的信息为hsdb!*&sds,请查收“
A说:”收到“

So for others, it is obvious that they don’t understand what A and B are talking about. Although A sends out the public key, everyone can see it, but the information encrypted with A’s public key can only pass A’s own The private key can be decrypted, so the security of the data is ensured in this way.

This is asymmetric encryption. In this way, as long as your private key is secured, the information will not be cracked by others. The common SSH, HTTPS, etc. must be encrypted in this way.

1.3, electronic certificate

The asymmetric encryption we mentioned above seems that the information will not be cracked, but there are still loopholes. If the conversation between A and B is seen by a third person C, then C can do this:

C:“hi,B兄,我换了新的公钥hsdb$%wew,请用这个公钥将数据加密发给我”
B:“好的,兄弟,我要说的是sdh!**iop”
C:“收到”

It can be seen that the problem with asymmetric encryption is that for user B, he does not know whether the person talking with him is A, others can pretend to be A, and then send his public key to B, and then B uses If the public key is encrypted and then the information is sent out, the information will be leaked.

At this time, you might think this is easy to handle. Since A and B know each other, wouldn't they think of a "secret sign"? So there is the following dialogue:

A:“hi,B兄,这是我的公钥ljbd%^&sqwe”
B:“好的,歪比歪比?”
A:“外比巴卜”
B:“好兄弟,你确实是A,我要传给你的信息是iubs!*Fsw”
A:“好的,收到”

Through the above method, we can see that A and B have agreed on a secret code, and B confirms A's identity through the secret code before sending the message. But in this way, C sees it and thinks "When I am a fool? I also know your secret code." Then C can also talk to B through this secret code.

C:“hi,B兄,这是我的公钥mnbd%^&ooqwe”
B:“好的,歪比歪比?”
C:“外比巴卜”
B:“好兄弟,你确实是A,我要传给你的信息是iuops!*Fsw”
C:“好的,收到”

Then B was deceived again, so B learned from it, and thought of a way. When C talks to B again:

C:“hi,B兄,这是我的公钥mnbd%^&ooqwe”
B:“好的,把你的证书拿出来我看下”
C:“啊?啥证书?”
B:“你这个死骗子!”

It turns out that B thought of a way to find another person D, and D said to him, "I will make a certificate and send it to A, so that you know it is A when you see the certificate."
But B has obviously become smarter after being deceived so many times, and said to D, "Now that there are so many fake certificates, how do I know whether the certificate is true or false?"
D told him, "This is easy to handle, I will give you something , You can use this to know if the certificate is true or false. "
B is busy asking" What baby, is the money detector???"
D said "My own public key!"

It turns out that D also has its own public key and private key. D uses his private key to make a signature on the certificate of A, and then gives the public key to B, so that B knows the information on the certificate through the public key given by D Is it correct?

Obviously D's public key is a very important thing here. This is what we often call "root certificate"!

You can think about it, when we visit some high-security websites, a dialog box will pop up on the webpage and you can download the certificate first. The webpage here is B, and our own client is A. We want to go to B to transmit information to us. B will first ask us for a certificate, so we will only allow B to transmit information with us after downloading the certificate. .

2. Use of SSL certificate

2.1 Introduction to SSL certificate

This is also true when we use SSL certificates to access the database, the database will require our client to provide a certificate to allow access.
The SSL certificate authentication is also divided into one-way and two-way authentication methods: two-way authentication SSL protocol requires both the server and the user to have certificates. The one-way authentication SSL protocol does not require the client to have a CA certificate, the server does not verify the client certificate, and when negotiating a symmetric cryptographic scheme and a symmetric call key, the server sends the client to the client without over-encryption (this does not affect the SSL Security of the process) password scheme.

2.2, PostgreSQL configuration one-way SSL authentication connection

If we want our pg database to support SSL connections, we first need to ensure that the server has openssl installed:

yum -y install openssl openssl-devel

Then specify to turn on ssl when compiling and installing

./configure --prefix=/home/pg14/pgsql14.1 --with-openssl 

First, let's demonstrate how to configure one-way SSL authentication. First, create a simple self-signed certificate with a validity period of 365 days for the server:

openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=cnndwpmesp1T"

Then execute:

chmod og-rwx server.key

Then modify the postgreql.conf configuration file:

ssl=on
ssl_cert_file='/home/pg10/openssl/server.crt'
ssl_key_file='/home/pg10/openssl/server.key'

Restart the database, and then connect:

psql "host=localhost user=postgres dbname=postgres [email protected] sslmode=require"
psql (12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 

2.3, PostgreSQL configuration two-way SSL authentication connection

For two-way SSL authentication configuration, we need a root server to issue certificates for clients and databases.
Here we use 5 servers to demonstrate:

server a, 根服务器1, 用于给客户端颁发证书    
server b, 根服务器2, 给授权机构3签名发证    
server c, 根服务器2的授权机构3, 用于给数据库颁发证书    
server d, 数据库服务器    
server e, 客户端    

server c is used to simulate an authority. If you don’t know the meaning of an authority, it can be understood that the authority server a needs to be a global server certificate. It is very busy, so it needs to grant its authority to the following authority. Somewhat similar to the concept of agents.

–Server a, root server 1:
production public key ca1.crt, private key ca1.key

mkdir keys certs    
chmod og-rwx keys certs
openssl req -new -x509 -days 3650 -nodes -out certs/ca1.crt -keyout keys/ca1.key -subj "/CN=172.23.11.142" 

–Server b, root server 2:

mkdir keys certs    
chmod og-rwx keys certs
openssl req -new -x509 -days 3650 -nodes -out certs/ca2.crt -keyout keys/ca2.key -subj "/CN=172.23.11.143"   

–Server c, root server 3:

openssl req -new -nodes -text -out intermediate.csr -keyout intermediate.key -subj "/CN=172.23.11.144"    
chmod og-rwx intermediate.key   

Then send intermediate.csr to server b for stamping.

Execute on server b:
openssl x509 -req -in intermediate.csr -text -days 1825 -extensions v3_ca -CA certs/ca2.crt -CAkey keys/ca2.key -CAcreateserial -out intermediate.crt

Signature ok
subject=/CN=172.23.11.144
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number:
            87:05:b6:07:e6:3e:5b:b4
    Signature Algorithm: NULL
        Issuer: CN=172.23.11.144
        Validity
            Not Before: May 31 05:46:30 2021 GMT
            Not After : May 30 05:46:30 2026 GMT
        Subject: CN=172.23.11.144
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:c9:46:74:61:50:27:20:d6:73:44:7d:30:14:b0:
                    91:69:60:9c:2e:1c:b8:96:f1:34:c5:79:10:10:0e:
                    8f:b4:0a:20:d1:b8:2b:42:53:da:55:6b:91:f9:d4:
                    fb:83:ea:66:42:85:28:d8:f1:2e:15:e6:77:fc:09:
                    a9:bf:e0:d7:d7:45:e5:6d:4c:90:40:9b:b0:92:92:
                    5f:69:77:84:97:a8:33:fa:b1:75:40:af:23:e1:90:
                    27:e3:eb:93:82:d4:7d:41:0b:98:bc:46:a3:93:65:
                    7a:d1:15:13:53:60:04:32:74:cb:f2:a0:db:55:c9:
                    2b:ae:a3:34:d8:8e:65:a8:da:3d:7b:30:f8:ee:7f:
                    35:37:0d:fd:52:e9:94:ff:30:f6:43:e7:ff:2c:5c:
                    fd:68:31:0b:84:8a:09:ed:33:c3:47:a3:6d:53:7d:
                    99:a9:ad:b3:a6:e4:67:90:42:73:7f:35:6f:0c:85:
                    fd:bd:52:1b:57:e8:1d:5a:75:a5:7b:02:70:42:01:
                    49:f7:ee:e6:bf:58:2d:de:9b:7c:c9:c1:c7:f9:f5:
                    c2:d0:4c:c7:73:c6:f3:05:d5:de:7b:03:b5:ea:fe:
                    f8:27:52:98:43:ab:75:be:eb:83:42:f3:43:89:5a:
                    5f:47:76:4c:d6:fa:98:aa:af:e8:d5:3a:a1:8b:71:
                    63:fd
                Exponent: 65537 (0x10001)
    Signature Algorithm: NULL
Getting CA Private Key

Then send the stamped file back to server c

–Server d, database server:
generate key, certificate signing application file:

openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=172.23.10.37"    
chmod og-rwx server.key   

Then send server.csr to server c for stamping:
openssl x509 -req -in server.csr -text -days 365 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial -out server.crt

Signature ok
subject=/CN=172.23.10.37
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number:
            e3:ae:d1:f8:35:55:1b:17
    Signature Algorithm: NULL
        Issuer: CN=172.23.10.37
        Validity
            Not Before: May 31 05:48:52 2021 GMT
            Not After : May 31 05:48:52 2022 GMT
        Subject: CN=172.23.10.37
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:c7:ce:bb:99:b8:b0:45:ea:2e:10:84:f2:79:68:
                    2b:6b:07:0a:52:e4:d4:cb:2b:74:fe:5e:48:f4:17:
                    7e:17:92:d4:1a:8f:0c:75:cd:ac:1c:7e:7a:a4:c7:
                    1d:94:7c:95:1f:b2:73:93:f0:00:b0:3a:0a:e4:7a:
                    a6:39:91:eb:0b:66:95:37:36:8f:bc:3a:93:99:ac:
                    42:ac:99:e6:a3:bc:26:0f:17:84:e7:00:07:77:cb:
                    bd:ea:53:c5:58:65:08:e3:e6:32:18:b4:e9:64:4f:
                    10:d6:cc:22:2d:e7:ca:fc:b8:d5:84:f7:1f:61:11:
                    a0:eb:14:8c:d7:42:b3:08:8e:9c:ab:61:35:9b:45:
                    c7:23:96:50:16:d1:e7:ab:9f:b4:31:bf:bd:18:02:
                    47:50:cb:1e:c7:0c:c0:48:2e:04:bc:e2:0b:0c:2d:
                    d0:81:3b:90:ad:24:37:56:8b:a1:0a:db:55:d6:88:
                    37:24:1a:86:5e:71:b7:6c:b5:27:40:63:2c:49:c2:
                    8a:40:14:9e:71:1f:02:e8:f8:d3:0e:fe:72:6b:2d:
                    bb:ef:f0:a1:eb:61:4e:ef:5c:71:1a:e3:fd:7c:1e:
                    ff:6b:a3:d2:de:4d:1b:e1:a8:2a:86:43:e0:ae:a6:
                    53:75:54:5e:d3:23:44:cb:a8:96:d7:89:67:3f:17:
                    ae:2f
                Exponent: 65537 (0x10001)
    Signature Algorithm: NULL
Getting CA Private Key

The stamped certificate server.crt is sent to server d. Here we put server.crt and server.key in the $PGDATA directory and set it to 600 permissions

--Server e, client:
generate key and certificate signing application file.

openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=172.23.10.37"    
chmod og-rwx client.key   

Send server.csr to root server 1 to stamp
openssl x509 -req -in client.csr -text -days 365 -CA certs/ca1.crt -CAkey keys/ca1.key -CAcreateserial -out client.crt

Signature ok
subject=/CN=172.23.10.37
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number:
            a3:6b:8b:8e:b1:c7:f3:39
    Signature Algorithm: NULL
        Issuer: CN=172.23.10.37
        Validity
            Not Before: May 31 05:50:50 2021 GMT
            Not After : May 31 05:50:50 2022 GMT
        Subject: CN=172.23.10.37
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:cd:98:6c:0b:60:91:82:b4:03:31:9d:12:b5:24:
                    7b:7f:7b:90:de:e0:f1:57:04:24:d9:c5:57:cc:e3:
                    f0:6a:b3:2e:c4:3d:98:c6:9e:d7:03:4f:85:1f:3c:
                    bd:9f:be:94:5c:f3:21:77:5a:00:59:e5:76:c8:b4:
                    83:91:a5:90:d1:68:c2:5f:41:d7:eb:a0:c3:4d:c8:
                    8e:21:73:ff:5a:e4:6b:0b:6e:1b:c3:ec:31:85:e7:
                    6b:6c:c2:6f:54:b4:51:f4:42:47:05:6e:35:32:78:
                    f8:96:49:32:dc:71:26:26:05:3d:fe:b5:9a:24:9c:
                    8a:23:07:48:93:14:ea:01:ce:64:c3:20:f5:19:b2:
                    9c:f1:19:65:e9:64:23:e9:d6:a8:ab:bb:12:e7:02:
                    73:33:64:85:fe:4e:68:64:49:b0:ae:24:cc:9c:80:
                    89:af:e5:e9:3a:b2:79:82:7d:ce:16:ac:c6:44:12:
                    77:ef:16:79:22:16:e1:82:55:a0:5a:8d:5c:1b:70:
                    fe:08:36:bc:37:53:1a:a5:cf:c2:43:44:4e:0b:3d:
                    f5:8d:b3:f6:e8:9c:48:d8:4b:d3:21:cc:97:96:94:
                    70:fb:8c:f7:fd:b9:a8:4d:56:fd:8a:c4:d4:c9:8a:
                    52:85:23:19:e4:48:dd:f1:df:bc:82:73:df:45:09:
                    3e:9d
                Exponent: 65537 (0x10001)
    Signature Algorithm: NULL
Getting CA Private Key

The stamped certificate client.crt is sent back to server e. We put both client.crt and client.key in the $PGHOME directory of the client, and set the permissions to 600

In addition, we also need to distribute the public key of the certification agency so that they can distinguish the authenticity of the certificate.

-Server d, database server:
write the content of the ca1.crt file in server a into the $PGDATA/ca1.crt file in server d.

Then configure the database parameters:

ssl = on    
ssl_ca_file = 'ca1.crt'    
ssl_cert_file = 'server.crt'    
ssl_crl_file = ''    
ssl_key_file = 'server.key'  

Restart the database to take effect.

–Server e, client server: the
authenticity of the database server certificate needs to be verified, so the public key of the organization issuing the certificate to the database server is required.
We will copy the contents of the ca2.crt file on server b and the intermediate.crt file on server c to the ~/.postgresql/ca2_int.crt file on the client server.

Authorization:

chmod 600 ~/.postgresql/ca2_int.crt     

Connection test:

[email protected]>  export PGSSLMODE="verify-ca"

[email protected]> export PGSSLROOTCERT="/home/pg14/pgdata/ca2_int.crt"

[email protected]> psql -h localhost -p 2021 -U bill postgres
psql (14beta1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 

Reference link:
http://postgres.cn/docs/12/ssl-tcp.html
https://segmentfault.com/a/1190000004461428