By digoal
In addition to encrypting data, SSL authentication can identify the authenticity of a target, preventing network man-in-the-middle attacks from being disguised. This article mainly analyzes why an SSL certificate is needed.
For example, at an ATM, criminals stick a card copy device in front, copy the contents of the card, and call to defraud your password.
It's a matter of disguise. The concept of certificates was introduced into SSL to solve these problems. It helps determine identity.
How can you do it?
Let’s take PostgreSQL 12 as an example.
The different values for the sslmode parameter provide different levels of protection. SSL can provide protection against three types of attacks:
If a third party can examine the network traffic between the client and the server, it can read the connection information (including the user name and password) and the data that is passed. SSL uses encryption to prevent this.
If a third party can modify the data while passing between the client and server, it can pretend to be the server and see and modify data even if it is encrypted. Then, the third party can forward the connection information and data to the original server, making it impossible to detect this attack. Common vectors to do this include DNS poisoning and address hijacking, whereby the client is directed to a different server than intended. Several other attack methods can accomplish this. SSL uses certificate verification to prevent this by authenticating the server to the client.
If a third party can pretend to be an authorized client, it can simply access data it should not be able to access. Typically, this can happen through insecure password management. SSL uses client certificates to prevent this, making sure only valid certificate holders can access the server.
Environment:
CentOS 7.8 x64
server a, root server 1, for issuing certificates to clients
server b, Root server 2, which issues a signature to authorization authority 3.
server c, authority 3 of root server 2, for issuing certificates to databases
Server d, database server
server e, clientDeployment:
yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y centos-release-scl-rh
yum install -y postgresql12*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=root-ca1"The decomposition steps are listed below:
openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key
find / -name openssl.cnf
/etc/pki/tls/openssl.cnf
openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca -signkey root.key -out root.crtSignature ok
subject=/CN=root.yourdomain.com
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
ce:c5:6e:31:31:dc:11:f0
Signature Algorithm: NULL
Issuer: CN=root.yourdomain.com
....
# less root.csr
Certificate Request:
Data:
Version: 0 (0x0)
Subject: CN=root.yourdomain.com
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
00:b2:1a:72:8b:46:9b:36:ae:9a:49:d8:4a:87:
...... 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=root-ca2"# intermediate
openssl req -new -nodes -text -out intermediate.csr -keyout intermediate.key -subj "/CN=intermediate.yourdomain.com"
chmod og-rwx intermediate.keySend intermediate.csr to root server 2 to stamp (only send the content)
The following operations are performed on root server 2:
openssl x509 -req -in intermediate.csr -text -days 1825 -extensions v3_ca -CA certs/ca2.crt -CAkey keys/ca2.key -CAcreateserial -out intermediate.crtSignature ok
subject=/CN=intermediate.yourdomain.com
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
d9:80:47:67:e3:6d:7a:ac
Signature Algorithm: NULL
.... The intermediate.crt stamped certificate is sent to server c (authority authorized by root server 2).
Generate the key and certificate signature application files:
# leaf
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=dbhost.domain.name.or.ipaddress"
chmod og-rwx server.keySince the key and pub are one-to-many, you can continue to apply after the certificate is revoked.
openssl req -new -key server.key -out server123.csr -subj "/CN=dbhost.domain.name.or.ipaddress"Send the server.csr to the authorized institution server 3 to stamp. (You can send the content in the past.)
The following operated in the authorization authority server 3:
openssl x509 -req -in server.csr -text -days 365 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial -out server.crtSignature ok
subject=/CN=dbhost.domain.name.or.ipaddress
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
f2:8b:d8:17:17:5b:ed:0f
Signature Algorithm: NULL
Issuer: CN=dbhost.domain.name.or.ipaddress
.... The stamped certificate server.crt is issued to server d (database server).
Put server.crt and server.key in the $PGDATA/directory and set the permission of 600
Generate the key and certificate signature application files
Specify CN = dbuser1. If the pg_hba.conf database is configured to require the verification of the client verify-full, the client can use this certificate only to log on with the dbbuser1 database user.
# leaf
openssl req -new -nodes -text -out client.csr -keyout client.key -subj "/CN=dbuser1"
chmod og-rwx client.keySince the key and pub are one-to-many, you can continue applying for certificate revocation in the future:
openssl req -new -key client.key -out client123.csr -subj "/CN=dbuser1"Send server.csr to root server 1 to stamp. (You can send the content in the past.)
The following operated in the root server 1:
openssl x509 -req -in client.csr -text -days 365 -CA certs/ca1.crt -CAkey keys/ca1.key -CAcreateserial -out client.crtSignature ok
subject=/CN=dbuser1
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
c8:05:4c:f8:bf:a3:a3:ea
Signature Algorithm: NULL
Issuer: CN=dbuser1
Validity
Not Before: Jun 19 06:42:37 2020 GMT
Not After : Jun 19 06:42:37 2021 GMT
Subject: CN=dbuser1
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
.... The stamped certificate client.crt is sent to server e (client server).
Put client.crt and client.key in the client home/.postgresql directory and set permissions to 600.
[root@iZbp1bc0dctm6mkipmqz9eZ ~]# cp client.crt client.key /var/lib/pgsql/.postgresql/
[root@iZbp1bc0dctm6mkipmqz9eZ ~]# chown postgres:postgres /var/lib/pgsql/.postgresql /*
[root@iZbp1bc0dctm6mkipmqz9eZ ~]# chmod 600 /var/lib/pgsql/.postgresql /*If it is not enough with a certificate, how does the other party check the authenticity of the certificate? It is also necessary to distribute the public keys of the accreditation agencies so they can identify the authenticity.
You need to verify the authenticity of the client certificate, so the public key of the authority issues the certificate to the client.
server a : certs/ca1.crt content writing server d:$PGDATA/ca1.crt
ll -rth
total 140K
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_twophase
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_tblspc
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_snapshots
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_serial
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_replslot
drwx------ 4 postgres postgres 4.0K Jun 19 15:19 pg_multixact
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_dynshmem
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_commit_ts
-rw------- 1 postgres postgres 3 Jun 19 15:19 PG_VERSION
-rw------- 1 postgres postgres 88 Jun 19 15:19 postgresql.auto.conf
-rw------- 1 postgres postgres 1.6K Jun 19 15:19 pg_ident.conf
-rw------- 1 postgres postgres 4.5K Jun 19 15:19 pg_hba.conf
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_xact
drwx------ 3 postgres postgres 4.0K Jun 19 15:19 pg_wal
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 pg_subtrans
drwx------ 2 postgres postgres 4.0K Jun 19 15:19 global
drwx------ 5 postgres postgres 4.0K Jun 19 15:19 base
drwx------ 2 postgres postgres 4.0K Jun 19 15:21 pg_notify
-rw------- 1 postgres postgres 27 Jun 19 15:21 postmaster.opts
drwx------ 2 postgres postgres 4.0K Jun 19 15:21 log
-rw------- 1 postgres postgres 30 Jun 19 15:21 current_logfiles
-rw------- 1 postgres postgres 27K Jun 19 15:21 postgresql.conf
drwx------ 4 postgres postgres 4.0K Jun 19 15:21 pg_logical
drwx------ 2 postgres postgres 4.0K Jun 19 15:21 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Jun 19 15:21 pg_stat
-rw------- 1 postgres postgres 1.7K Jun 19 17:14 server.key
-rw------- 1 postgres postgres 1.1K Jun 19 17:14 server.crt
-rw------- 1 postgres postgres 1.1K Jun 19 17:15 ca1.crtConfigure the database parameters and restart the database:
postgresql.conf
# - SSL-
ssl = on
ssl_ca_file = 'CA1. crt'
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_key_file = 'server.key'postgres=# create user digoal login superuser encrypted password 'digoal123 ';
CREATE ROLE
postgres=# create user dbuser1 login superuser encrypted password 'digoal123 ';
CREATE ROLEThe database server certificate needs to be verified, so the public key of the authority issues the database server:
server b : certs/ca2.crt 第一
server c : intermediate.crt 第二 ca2.crt and intermediate.crt files written after cat server e: ~/.postgresql/ca2_int.crt
chmod 600 ~/.postgresql/ca2_int.crt1. Check whether the database is forged (for example, the attacker hijacks and forges the database by DNS) to check the authenticity of the database certificate.
vi pg_hba.conf
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5
pg_ctl reloadpostgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLMODE="verify-ca"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLROOTCERT="/var/lib/pgsql/.postgresql/ca2_int.crt"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h 192.168.0.245 -p 1921 -U digoal postgres
Password for user digoal:
psql (12.3)
SSL connection (protocol:TLSv1.2, cipher:ECDHE-RSA-AES256-GCM-SHA384, bits:256, compression: off)
Type "help" for help.
postgres=# \q2. Check whether the CN of the database certificate is consistent with the target of the connection (cn == psql -h hostname).
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLMODE="verify-full"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLROOTCERT="/var/lib/pgsql/.postgresql/ca2_int.crt"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h 192.168.0.245 -p 1921 -U digoal postgres
psql: error: could not connect to server: server certificate for "dbhost.domain.name.or.ipaddress" does not match host name "192.168.0.245"Since CN = dbhost.domain.name.or. ipaddress in the database certificate, the customer is connected to 192.168.0.245.
How can you solve it?
Method 1: You can configure DNS to parse dbhost.domain.name.or. ipaddress to 192.168.0.245.
Method 2: Configure the client hosts
# vi /etc/hosts
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.0.244 iZbp1bc0dctm6mkipmqz9eZ iZbp1bc0dctm6mkipmqz9eZ
192.168.0.245 dbhost.domain.name.or.ipaddress As shown below:
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
Password for user digoal:
psql (12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# Use the following method to add multiple Hostnames to server. crt
References:
The following is the test on MacOS. If it is CentOS, please copy it. /etc/pki/tls/openssl.cnf
cd ~
cp /System/Library/OpenSSL/openssl.cnf ./
vi ./openssl.cnf
# 此文件的格式是类似 ini 的配置文件格式，找到 [ req ] 段落，加上下面的配置：
req_extensions = v3_req
# 这段配置表示在生成 CSR 文件时读取名叫 v3_req 的段落的配置信息，
# 因此我们再在此配置文件中加入一段名为 v3_req 的配置：
[ v3_req ]
# Extensions to add to a certificate request
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
subjectAltName = @alt_names
# 这段配置中最重要的是在最后导入名为 alt_names 的配置段，
# 因此我们还需要添加一个名为 [ alt_names ] 的配置段：
[ alt_names ]
DNS.1 = pgm-bp116zpg87qr8rx0.pg.rds.aliyuncs.com
DNS.2 = pgm-bp116zpg87qr8rx0no.pg.rds.aliyuncs.com Then, use this temporary configuration to generate the certificate:
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/C=CN" -config ./openssl.cnf
# 一路全部使用默认, 特别注意: CN 一定要设置为空. View the contents of the certificate request file, which already contains two hostnames:
openssl req -text -noout -in server.csr
Requested Extensions:
X509v3 Basic Constraints:
CA:FALSE
X509v3 Key Usage:
Digital Signature, Non Repudiation, Key Encipherment
X509v3 Subject Alternative Name:
DNS:pgm-bp116zpg87qr8rx0.pg.rds.aliyuncs.com, DNS:pgm-bp116zpg87qr8rx0no.pg.rds.aliyuncs.com Then, use this temporary configuration and the root server certificate to generate a signature:
openssl x509 -req -in server.csr -text -days 365 -CA certs/ca2.crt -CAkey keys/ca2.key -CAcreateserial -out server.crt -extensions v3_req -extfile ./openssl.cnfThen, you can copy server key, server. crt to the database server for use.
1. Verify whether the client is forged and the client's certificate is authentic.
Database:
vi pg_hba.conf
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5 clientcert=verify-ca
pg_ctl reloadOn the client:
export PGSSLMODE="require" # 不检查数据库真伪
export PGSSLCERT="/var/lib/pgsql/.postgresql/client.crt"
export PGSSLKEY="/var/lib/pgsql/.postgresql/client.key"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
Password for user digoal:
psql (12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \q Change a variable to make it report an error, which means the database has asked the client for a certificate. The database is checking whether the client is forged.
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLKEY="/var/lib/pgsql/.postgresql/client.ke"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
psql: error: could not connect to server: certificate present, but not private key file "/var/lib/pgsql/.postgresql/client.ke"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLCERT="/var/lib/pgsql/.postgresql/client.crt1"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
psql: error: could not connect to server: FATAL: connection requires a valid client certificate 2. Verify whether the client is forged and the client's certificate is authentic. It is also required to check whether the CN of the client certificate has the same name as the user connecting to the database. Different names are not allowed to log in.
Note: pg_hba.conf configuration clientcert = verify-full. This feature is new to PG version 12.
The client certificate issued by the signature, CN=dbuser1
Database:
vi pg_hba.conf
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5 clientcert=verify-full
pg_ctl reloadOn the client:
export PGSSLMODE="require" # 不检查数据库真伪
export PGSSLCERT="/var/lib/pgsql/.postgresql/client.crt"
export PGSSLKEY="/var/lib/pgsql/.postgresql/client.key" If you log in with a database user with a different name than the certificate CN, it will fail.
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
Password for user digoal:
psql: error: could not connect to server: FATAL: password authentication failed for user "digoal" If you log in to a database user with the same name as the certificate CN, it will succeed.
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
Password for user reduser:
psql (12.3)
SSL connection (protocol:TLSv1.2, cipher:ECDHE-RSA-AES256-GCM-SHA384, bits:256, compression: off)
Type "help" for help.
postgres=#As long as the server recognizes your certificate during certificate authentication, you can log in. How can it recognize the certificate? Your certificate is in the database – ssl_ca_file = 'CA1. crt'. This parameter is issued by the authority configured in this parameter.
The default value is clientcert=verify-full. It cannot be changed, so the database user that requires the client to log in must be the user corresponding to CN in the client certificate.
Use the cert authentication method:
vi pg_hba.conf
hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 cert
pg_ctl reloadLog in without a password:
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
psql: error: could not connect to server:FATAL:certificate authentication failed for user "digoal"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
psql (12.3)
SSL connection (protocol:TLSv1.2, cipher:ECDHE-RSA-AES256-GCM-SHA384, bits:256, compression: off)
Type "help" for help.
postgres=#If you want a divorce certificate, you need to bring the marriage certificate to the divorce proceeding to receive it. Revocation also requires a certificate to be given to the issuing authority to generate a revocation certificate.
A few operations need to be performed at the issuing authority first. Otherwise, an error will be reported.
# touch /etc/pki/CA/index.txt
# echo 1000 > /etc/pki/CA/crlnumberIf you do not perform the two steps above, an error will be reported.
Using configuration from /etc/pki/tls/openssl.cnf
/etc/pki/CA/index.txt: No such file or directory
unable to open '/etc/pki/CA/index.txt'
140161271125904:error:02001002:system library:fopen:No such file or directory:bss_file.c:402:fopen('/etc/pki/CA/index.txt','r')
140161271125904:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:404:
Using configuration from /etc/pki/tls/openssl.cnf
/etc/pki/CA/crlnumber: No such file or directory
error while loading CRL number
140051823699856:error:02001002:system library:fopen:No such file or directory:bss_file.c:402:fopen('/etc/pki/CA/crlnumber','r')
140051823699856:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:404: The next thing is a revoked certificate for the client. A crt certificate is generated at the issuing authority.
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl ca -revoke ./client.crt -cert ./certs/ca1.crt -keyfile ./keys/ca1.key
Using configuration from /etc/pki/tls/openssl.cnf
Adding Entry with serial number CB28CD10139DAA75 to DB for /CN=dbuser1
Revoking Certificate CB28CD10139DAA75.
Data Base UpdatedGenerate revocation certificate:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl ca -gencrl -out client.crl -cert certs/ca1.crt -keyfile keys/ca1.key
Using configuration from /etc/pki/tls/openssl.cnfCheck revocation certificate:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl crl -in client.crl -text -noout
Certificate Revocation List (CRL):
Version 2 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: /CN=root-ca1
Last Update: Jun 19 16:15:48 2020 GMT
Next Update: Jul 19 16:15:48 2020 GMT
CRL extensions:
X509v3 CRL Number:
4098
Revoked Certificates:
Serial Number: CB28CD10139DAA75
Revocation Date: Jun 19 16:12:07 2020 GMT
Signature Algorithm: sha256WithRSAEncryption
23:ec:f6:04:01:f9:d8:da:5c:f1:c7:46:60:08:b5:e6:d9:a4:
3d:e2:27:f7:b7:e8:3c:6b:e9:a3:15:92:7f:45:90:8f:0e:56:
95:08:e1:13:5c:0a:bc:b0:c8:26:47:40:9b:5e:f7:de:5b:6c:
f8:cf:ed:fd:6c:66:cb:4e:4d:94:b4:7c:33:e0:27:94:8f:03:
19:0a:39:00:7e:49:09:c1:16:b9:59:4a:cc:68:50:d5:3a:d2:
04:6a:77:68:a6:47:44:67:d6:bd:3d:ac:fe:bd:17:a1:38:c8:
f7:3d:6e:21:25:77:e9:65:2c:68:28:22:63:f7:cc:f3:95:0c:
3e:9f:6d:f7:2f:56:48:d2:b5:8d:db:b2:c5:44:43:fb:e3:a7:
86:b7:fc:8d:76:53:b6:67:49:ed:e3:68:aa:9a:40:1a:15:26:
9a:64:4c:53:88:3a:ae:c8:3b:17:f9:fc:89:0c:3e:6b:d1:1c:
37:82:d8:8c:c3:33:37:14:c7:29:b4:76:49:f2:12:8c:07:ae:
3c:e8:d1:b6:55:aa:91:cc:03:66:20:29:9b:b0:4e:95:4b:1e:
4f:46:78:11:0a:da:15:64:22:83:d5:21:29:26:89:4e:07:16:
0d:12:eb:3e:bc:0b:9c:9d:0e:b0:00:ee:11:2b:56:05:e1:b5:
44:4b:6c:02 [root@iZbp135pwcjjoxqgfpw9k1Z ~]# cat /etc/pki/CA/index.txt
R 210619064237Z 200619161207Z CB28CD10139DAA75 unknown /CN=dbuser1Revoke the contents of the certificate:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# cat client.crl
-----BEGIN X509 CRL -----
MIIBijB0AgEBMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMMCHJvb3QtY2ExFw0y
MDA2MTkxNjE1NDhaFw0yMDA3MTkxNjE1NDhaMBwwGgIJAMsozRATnap1Fw0yMDA2
MTkxNjEyMDdaoA8wDTALBgNVHRQEBAICEAIwDQYJKoZIhvcNAQELBQADggEBACPs
9gQB + djaXPHHRmAItebZpD3iJ/e36Dxr6aMVkn9FkI8OVpUI4RNcCrywyCZHQJte
995bbPjP7f1sZstOTZS0fDPgJ5SPAxkKOQB + SQnBFrlZSsxoUNU60gRqd2imR0Rn
1r09rP69F6E4yPc9biEld + llLGgoImP3zPOVDD6fbfcvVkjStY3bssVEQ/vjp4a3
/I12U7ZnSe3jaKqaQBoVJppkTFOIOq7IOxf5/IkMPmvRHDeC2IzDMzcUxym0dkny
EowHrjzo0bZVqpHMA2YgKZuwTpVLHk9GeBEK2hVkIoPVISkmiU4HFg0S6z68C5yd
DrAA7hErVgXhtURLbAI =
-----END X509 CRL -----Copy the content of client.crl to black.crl of the database server
Configure on the database side ssl_crl_file parameter. The content of the client certificate indicates the certificate has been revoked. If the database is configured to verify the client certificate verify-ca or verify-full, clients using this certificate are not allowed to be connected.
Put the client's client.crl and enter the content to the database ssl_crl_file = 'black.crl ', $PGDATA/black.crl:
vi $PGDATA/black.crl
-----BEGIN X509 CRL-----
MIIBijB0AgEBMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMMCHJvb3QtY2ExFw0y
MDA2MTkxNjE1NDhaFw0yMDA3MTkxNjE1NDhaMBwwGgIJAMsozRATnap1Fw0yMDA2
MTkxNjEyMDdaoA8wDTALBgNVHRQEBAICEAIwDQYJKoZIhvcNAQELBQADggEBACPs
9gQB+djaXPHHRmAItebZpD3iJ/e36Dxr6aMVkn9FkI8OVpUI4RNcCrywyCZHQJte
995bbPjP7f1sZstOTZS0fDPgJ5SPAxkKOQB+SQnBFrlZSsxoUNU60gRqd2imR0Rn
1r09rP69F6E4yPc9biEld+llLGgoImP3zPOVDD6fbfcvVkjStY3bssVEQ/vjp4a3
/I12U7ZnSe3jaKqaQBoVJppkTFOIOq7IOxf5/IkMPmvRHDeC2IzDMzcUxym0dkny
EowHrjzo0bZVqpHMA2YgKZuwTpVLHk9GeBEK2hVkIoPVISkmiU4HFg0S6z68C5yd
DrAA7hErVgXhtURLbAI=
-----END X509 CRL-----
chmod 600 $PGDATA/black.crl vi postgresql.conf
ssl_crl_file = 'black.crl'
pg_ctl reloadThe client is not allowed again:
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLMODE="require"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLCERT="/var/lib/pgsql/.postgresql/client.crt"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLKEY="/var/lib/pgsql/.postgresql/client.key"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLROOTCERT="/var/lib/pgsql/.postgresql/ca2_int.crt"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
psql: error: could not connect to server:SSL error: sslv3 alert certificate revoked
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
psql: error: could not connect to server:SSL error: sslv3 alert certificate revokedYou can re-initiate a certificate application on the client, sign and seal it at an authority, and log on to the database with the new certificate:
Use the client:
openssl req -new -key client.key -out client123.csr -subj "/CN=dbuser1"Root server:
openssl x509 -req -in client123.csr -text -days 365 -CA certs/ca1.crt -CAkey keys/ca1.key -CAcreateserial -out client123.crtCopy client123.crt to the client:
Use the new certificate client123.crt to connect to the database:
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLCERT="/var/lib/pgsql/.postgresql/client123.crt"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
Password for user reduser:
psql (12.3)
SSL connection (protocol:TLSv1.2, cipher:ECDHE-RSA-AES256-GCM-SHA384, bits:256, compression: off)
Type "help" for help.
postgres=# \q
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
Password for user digoal:
psql (12.3)
SSL connection (protocol:TLSv1.2, cipher:ECDHE-RSA-AES256-GCM-SHA384, bits:256, compression: off)
Type "help" for help.
postgres=# \qSimilar to revoking the client certificate, the database certificate issuing authority (in this case, the authorized authority) revoke the database certificate server. CRT produces the CRL file of the revoked certificate.
However, unlike the client certificate, the database certificate in our example is issued by a secondary authority. If you want to revoke this certificate, it must be revoked on the server of the entire authorization chain.
The revoked certificate of the server.crt certificate is generated at the secondary certification authority.
# touch /etc/pki/CA/index.txt
# echo 1000 > /etc/pki/CA/crlnumber
# openssl ca -revoke ./server.crt -cert ./intermediate.crt -keyfile ./intermediate.key
Using configuration from /etc/pki/tls/openssl.cnf
Adding Entry with serial number A8B633D16587B002 to DB for /CN=dbhost.domain.name.or.ipaddress
Revoking Certificate A8B633D16587B002.
Data Base UpdatedGenerate a revocation certificate:
[root@iZbp1bc0dctm6mkipmqz9gZ ~]# openssl ca -gencrl -out server.crl -cert intermediate.crt -keyfile intermediate.key
Using configuration from /etc/pki/tls/openssl.cnfCheck the revocation certificate:
[root@iZbp1bc0dctm6mkipmqz9gZ ~]# openssl crl -in server.crl -text -noout
Certificate Revocation List (CRL):
Version 2 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: /CN=intermediate.yourdomain.com
Last Update: Jun 19 16:20:43 2020 GMT
Next Update: Jul 19 16:20:43 2020 GMT
CRL extensions:
X509v3 CRL Number:
4097
Revoked Certificates:
Serial Number: A8B633D16587B002
Revocation Date: Jun 19 16:19:32 2020 GMT
Signature Algorithm: sha256WithRSAEncryption
20:75:8d:e7:dc:fb:13:25:bf:3d:11:5d:52:41:44:a0:7a:e1:
4d:0a:39:69:cb:d6:50:8f:66:37:e3:ab:6b:9e:33:79:bf:80:
2d:1c:97:7e:1c:62:97:93:c7:ea:57:b4:48:ff:10:fc:05:06:
45:61:7c:1a:fa:19:bc:df:1d:eb:52:96:5d:e4:bb:b0:b4:31:
c1:65:a2:e1:d9:8e:e8:df:4f:e8:3e:4f:5e:5f:7a:58:ff:c3:
ed:3a:c5:ee:df:b3:b4:95:da:68:a9:c5:5a:5c:2e:39:99:95:
87:8c:6e:19:a5:a0:15:71:8b:ef:a2:f7:47:dc:aa:72:73:cf:
9f:d4:2b:27:37:81:d9:d7:4d:c6:a8:2d:bd:9b:94:14:fa:48:
75:a8:cd:2e:af:80:c2:ae:74:4b:41:cc:fe:bf:70:e2:9d:08:
99:eb:4b:7c:f1:df:02:78:e6:16:dd:f0:d3:06:3b:20:bb:1e:
a8:09:e0:ea:ca:1e:60:ba:8b:3b:b5:f6:1d:cb:6c:50:b1:20:
49:f8:ff:30:c2:8a:96:67:ca:fb:35:7f:96:3b:e6:52:08:91:
1a:75:b3:c8:a6:36:cf:30:4b:0b:d5:8c:bb:cf:a0:3e:f6:51:
69:d6:c2:22:8d:89:22:4f:68:24:01:14:40:6c:da:a9:4a:20:
70:68:aa:38 [root@iZbp1bc0dctm6mkipmqz9gZ ~]# cat /etc/pki/CA/index.txt
R 210619063904Z 200619161932Z A8B633D16587B002 unknown /CN=dbhost.domain.name.or.ipaddress Revoke the contents of the certificate:
[root@iZbp1bc0dctm6mkipmqz9gZ ~]# cat server.crl
-----BEGIN X509 CRL -----
MIIBnjCBhwIBATANBgkqhkiG9w0BAQsFADAmMSQwIgYDVQQDDBtpbnRlcm1lZGlh
dGUueW91cmRvbWFpbi5jb20XDTIwMDYxOTE2MjA0M1oXDTIwMDcxOTE2MjA0M1ow
HDAaAgkAqLYz0WWHsAIXDTIwMDYxOTE2MTkzMlqgDzANMAsGA1UdFAQEAgIQATAN
BgkqhkiG9w0BAQsFAAOCAQEAIHWN59z7EyW/PRFdUkFEoHrhTQo5acvWUI9mN+Or
a54zeb + ALRyXfhxil5PH6le0SP8Q/AUGRWF8GvoZvN8d61KWXeS7sLQxwWWi4dmO
6 N9P6D5PXl96WP/D7TrF7t + ztJXaaKnFWlwuOZmVh4xuGaWgFXGL76L3R9yqcnPP
n9QrJzeB2ddNxqgtvZuUFPpIdajNLq + Awq50S0HM/r9w4p0ImetLfPHfAnjmFt3w
0wY7ILseqAng6soeYLqLO7X2HctsULEgSfj/MMKKlmfK+zV/ljvmUgiRGnWzyKY2
zzBLC9WMu8 + gPvZRadbCIo2JIk9oJAEUQGzaqUogcGiqOA = =
-----END X509 CRL-----The revoked certificate of the server.crt certificate is generated at the root certification authority.
# touch /etc/pki/CA/index.txt
# echo 1000 > /etc/pki/CA/crlnumber
[root@iZbp1bc0dctm6mkipmqz9fZ ~]# openssl ca -revoke ./server.crt -cert certs/ca2.crt -keyfile keys/ca2.key
Using configuration from /etc/pki/tls/openssl.cnf
Adding Entry with serial number A8B633D16587B002 to DB for /CN=dbhost.domain.name.or.ipaddress
Revoking Certificate A8B633D16587B002.
Data Base UpdatedGenerate a revocation certificate:
[root@iZbp1bc0dctm6mkipmqz9fZ ~]# openssl ca -gencrl -out server.crl -cert certs/ca2.crt -keyfile keys/ca2.key
Using configuration from /etc/pki/tls/openssl.cnf Check the revocation certificate:
[root@iZbp1bc0dctm6mkipmqz9fZ ~]# openssl crl -in server.crl -text -noout
Certificate Revocation List (CRL):
Version 2 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: /CN=root-ca2
Last Update: Jun 19 16:36:35 2020 GMT
Next Update: Jul 19 16:36:35 2020 GMT
CRL extensions:
X509v3 CRL Number:
4096
Revoked Certificates:
Serial Number: A8B633D16587B002
Revocation Date: Jun 19 16:36:05 2020 GMT
Signature Algorithm: sha256WithRSAEncryption
53:4e:72:d1:c9:7f:e3:c6:22:51:30:0b:86:f6:a9:c2:84:bb:
70:fb:ec:eb:d9:97:76:cb:60:39:e6:de:b9:87:6b:6d:31:c0:
d9:04:dc:a6:8c:ab:fa:83:01:47:75:4b:7a:e8:c4:a7:9a:90:
02:8f:db:56:ba:7e:e4:6b:8f:c7:af:18:5e:79:f1:14:18:3c:
7a:6f:3c:3a:7e:71:bc:db:d4:6e:6c:5b:82:25:8e:a3:16:78:
e1:5a:e8:6e:26:53:8c:37:3a:63:fb:61:9a:2a:a9:66:42:6c:
a9:34:21:38:46:29:62:08:2c:13:5b:79:26:f3:fb:b6:7b:af:
a5:c5:6b:a7:1d:c3:d7:b6:09:44:20:36:2a:69:25:6c:d8:23:
df:ea:da:12:73:b5:34:eb:f8:bb:59:29:7d:a4:11:f1:a1:97:
c8:4a:30:c4:05:b7:ff:e6:19:e6:f6:3a:c5:5a:80:f7:b5:a5:
1a:d9:05:ef:68:d2:25:6b:77:22:50:b9:f9:70:8e:c6:45:77:
7e:5a:99:d7:e7:d6:aa:ed:1c:87:83:a7:c2:55:b5:b0:67:be:
66:82:9f:a9:05:b6:fc:32:a8:cd:a9:3f:74:e6:6c:6b:67:c2:
20:7a:59:88:38:b4:68:73:81:8e:2a:f8:9f:82:07:c6:e4:e7:
bd:a4:ca:e2 [root@iZbp1bc0dctm6mkipmqz9fZ ~]# cat /etc/pki/CA/index.txt
R 210619063904Z 200619163605Z A8B633D16587B002 unknown /CN=dbhost.domain.name.or.ipaddress Revoke the contents of the certificate:
[root@iZbp1bc0dctm6mkipmqz9fZ ~]# cat server.crl
-----BEGIN X509 CRL -----
MIIBijB0AgEBMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMMCHJvb3QtY2EyFw0y
MDA2MTkxNjM2MzVaFw0yMDA3MTkxNjM2MzVaMBwwGgIJAKi2M9Flh7ACFw0yMDA2
MTkxNjM2MDVaoA8wDTALBgNVHRQEBAICEAAwDQYJKoZIhvcNAQELBQADggEBAFNO
ctHJf+PGIlEwC4b2qcKEu3D77OvZl3bLYDnm3rmHa20xwNkE3KaMq/qDAUd1S3ro
xKeakAKP21a6fuRrj8evGF558RQYPHpvPDp + cbzb1G5sW4IljqMWeOFa6G4mU4w3
OmP7YZoqqWZCbKk0IThGKWIILBNbeSbz+7Z7r6XFa6cdw9e2CUQgNippJWzYI9/q
2hJztTTr + LtZKX2kEfGhl8hKMMQFt // mGeb2OsVagPe1pRrZBe9o0iVrdyJQuflw
jsZFd35amdfn1qrtHIeDp8JVtbBnvmaCn6kFtvwyqM2pP3TmbGtnwiB6WYg4tGhz
gY4q+J+CB8bk572kyuI=
-----END X509 CRL -----Copy the content of server.crl to. postgresql/black.crl on the client.
It must be stored in the order of the authorization link. First, fill in the revocation certificate issued by the secondary authority and then fill in the revocation certificate issued by the root authority.
vi ~/.postgresql/balck.crl
-----BEGIN X509 CRL-----
MIIBnjCBhwIBATANBgkqhkiG9w0BAQsFADAmMSQwIgYDVQQDDBtpbnRlcm1lZGlh
dGUueW91cmRvbWFpbi5jb20XDTIwMDYxOTE2MjA0M1oXDTIwMDcxOTE2MjA0M1ow
HDAaAgkAqLYz0WWHsAIXDTIwMDYxOTE2MTkzMlqgDzANMAsGA1UdFAQEAgIQATAN
BgkqhkiG9w0BAQsFAAOCAQEAIHWN59z7EyW/PRFdUkFEoHrhTQo5acvWUI9mN+Or
a54zeb+ALRyXfhxil5PH6le0SP8Q/AUGRWF8GvoZvN8d61KWXeS7sLQxwWWi4dmO
6N9P6D5PXl96WP/D7TrF7t+ztJXaaKnFWlwuOZmVh4xuGaWgFXGL76L3R9yqcnPP
n9QrJzeB2ddNxqgtvZuUFPpIdajNLq+Awq50S0HM/r9w4p0ImetLfPHfAnjmFt3w
0wY7ILseqAng6soeYLqLO7X2HctsULEgSfj/MMKKlmfK+zV/ljvmUgiRGnWzyKY2
zzBLC9WMu8+gPvZRadbCIo2JIk9oJAEUQGzaqUogcGiqOA==
-----END X509 CRL-----
-----BEGIN X509 CRL-----
MIIBijB0AgEBMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMMCHJvb3QtY2EyFw0y
MDA2MTkxNjM2MzVaFw0yMDA3MTkxNjM2MzVaMBwwGgIJAKi2M9Flh7ACFw0yMDA2
MTkxNjM2MDVaoA8wDTALBgNVHRQEBAICEAAwDQYJKoZIhvcNAQELBQADggEBAFNO
ctHJf+PGIlEwC4b2qcKEu3D77OvZl3bLYDnm3rmHa20xwNkE3KaMq/qDAUd1S3ro
xKeakAKP21a6fuRrj8evGF558RQYPHpvPDp+cbzb1G5sW4IljqMWeOFa6G4mU4w3
OmP7YZoqqWZCbKk0IThGKWIILBNbeSbz+7Z7r6XFa6cdw9e2CUQgNippJWzYI9/q
2hJztTTr+LtZKX2kEfGhl8hKMMQFt//mGeb2OsVagPe1pRrZBe9o0iVrdyJQuflw
jsZFd35amdfn1qrtHIeDp8JVtbBnvmaCn6kFtvwyqM2pP3TmbGtnwiB6WYg4tGhz
gY4q+J+CB8bk572kyuI=
-----END X509 CRL-----
chmod 600 ~/.postgresql/black.crl export PGSSLCRL="/var/lib/pgsql/.postgresql/black.crl"
export PGSSLMODE="verify-ca"
export PGSSLCERT="/var/lib/pgsql/.postgresql/client123.crt"
export PGSSLKEY="/var/lib/pgsql/.postgresql/client.key"
export PGSSLROOTCERT="/var/lib/pgsql/.postgresql/ca2_int.crt"
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
psql: error: could not connect to server: SSL error: certificate verify failed
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
psql: error: could not connect to server: SSL error: certificate verify failed After the revocation certificate takes effect, you can regenerate a new database certificate to connect to it and perform revocation operations periodically to prevent certificate leakage. (It will also let you change periodically Just like the key of our bank.)
Database:
openssl req -new -key server.key -out server123.csr -subj "/CN=dbhost.domain.name.or.ipaddress" Authorized institutions:
openssl x509 -req -in server123.csr -text -days 365 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial -out server123.crt
server123.crt 拷贝到数据库服务器 $PGDATA
chmod 600 server123.crt Database:
修改数据库参数 ssl_cert_file = 'server123.crt'
pg_ctl reload The database has used a new certificate to establish a connection.
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U digoal postgres
Password for user digoal:
psql (12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \q
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
Password for user dbuser1:
psql (12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \q If you don't want to check whether the database is using a revoked certificate, PGSSLCRL, the environment variable is set to empty.
postgres@iZbp1bc0dctm6mkipmqz9eZ-> export PGSSLCRL=""
postgres@iZbp1bc0dctm6mkipmqz9eZ-> psql -h dbhost.domain.name.or.ipaddress -p 1921 -U dbuser1 postgres
psql (12.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \q 1. User A has a certificate stamped by the authority ca1.
2. A person claiming to be user A wants to communicate with user B. User B only recognizes the ca1 certificate because the ca1 certificate is authoritative. Then, user B wants to confirm the identity of user A. First, it asks user A for a certificate. User B uses the authentication method given by ca1 (ca1 to B's file to check whether the certificate is a Cai-issued certificate) and determines whether the certificate given by user A is true or false. If it is a Cai-issued certificate the CN value is in the certificate.
3. For a certificate issued by an authorizing authority, a chain is needed to verify its authenticity, such as root-authorizing authority 1-sub-authorized authority 2. The certificate issued by authorizing authority 2 needs to be verified by multiple parties by Root, authorization authority 1, and sub-authorization authority 2. Therefore, all three institutions are required to give verification methods. (The public certificate of the issuing authority is pasted in one document.)
4. Asymmetric Encryption Technology
Key, pub encrypts pub, and decrypt key. The certificate contains pub. After user A delivers the certificate to user B, user B can decrypt the data through pub in the certificate and transfer the encrypted data to user A. User A can decrypt it with the key.
Each certificate has a corresponding key, and one key can create many certificates.
5. If user C finds a ca1 certificate, can user C communicate with user B? There are several situations:
User B determines the certificate is issued by ca1 and looks at the contents of the certificate, such as the CN value in the certificate and psql -h hostname. If the CN is different from the hostname, it does not communicate.
User A reports the loss of its certificate to the authority, and the authority issues an announcement. User A's certificate has been revoked, and user B stores the revocation certificate in its revocation certificate list, so there will be no communication.
User A's certificate expires, so there will be no communication.
6. The client can judge the authenticity of the database through the certificate signed by the authority.
7. The database can judge the authenticity of the client through the certificate signed by the authority.
8. The database can use the cert authentication method. (pg_hba.conf is configured as the cert authentication method.) The database does not require the user to provide a password but only needs to provide a certificate to log in to the database.
SSL (boolean)
This enables SSL connections. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.
ssl_key_file (string) Database Certificate Private Key
This specifies the name of the file containing the SSL server private key. The relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is server.key.
ssl_cert_file (string) database certificate public key after the authority stamped the document
This specifies the name of the file containing the SSL server certificate. Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is server.crt.
ssl_ca_file the public key of the client certificate issuing authority to detect the authenticity of the client certificate
This specifies the name of the file containing the SSL server certificate authority (CA). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is empty, meaning no CA file is loaded, and client certificate verification is not performed.
ssl_crl_file (string) revocation of certificates (For example, some client certificates are missing, which indicates that these certificates are invalid. When pg_hb.conf is configured with verify-ca or verify-full, the client with the revoked certificate is not allowed to log on.)
This specifies the name of the file containing the SSL server certificate revocation list (CRL). Relative paths are relative to the data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is empty, meaning no CRL file is loaded.
PGSSLMODE behaves the same as the sslmode connection parameter.
PGSSLCERT behaves the same as the sslcert connection parameter.
PGSSLKEY behaves the same as the sslkey connection parameter.
PGSSLROOTCERT behaves the same as the sslrootcert connection parameter.
PGSSLCRL behaves the same as the sslcrl connection parameter.1. What security problems have been solved after the client logs in to DB and adds the ID card verification of both parties before password authentication?
2. What is the cloud database service? Who will issue the certificate? It is also divided into database certificates and client certificates.
The user uploads the server by themselves. key, server.crt, root.crt, x.crl (It is controlled by the user and the certificate can be signed by the user or by a third party. root.crt is used in combination with pg_hba.conf verify-ca verify-full to verify the client certificate. x.crl is used in conjunction with pg_hba.conf verify-ca verify-full to filter revoked client certificates.)
The contents of the certificate are listed below:
DB:
Client:
A password-protected key:
less server.key
-----BEGIN RSA PRIVATE KEY-----
Proc-Type:4,ENCRYPTED
DEK-Info:DES-EDE3-CBC,80D0DC2C02ACD35A
C92A388DpZxlKAzfb5gDKSizpBQXPzN1tkWNWLR3HoTZlfZn + 6TT8tKsVhHxWbUP
x2olORr5+I1WtMVIROPsjlCCQfyGenhKxOK80fy/HTb+62F9Og4gJMnXaqeUvCAG
7gCu6xBObOgsbd67dBAspy0OyGpyQjmRBj7TF24jVL9HOrpKgxE1GIwkC0+Dwif+
h0eNnhbQ7tDqT5jfGN7tFKHQznxx9/w4WFcLDMnoS/R5SScaFfcK7UJByMfMrSii
al9OpmtWe7wrLZnswDpZaow77N6J8/C+H0Ar2SwvFID3lsTOljHUgRnx+kAxunr5
HHjjbSCPG225OhbVcXpo28NPL8/kZkkkgPFxj9ymzMZi6PWQ5vqtkHWkTQ0+KlIh
LpfqZKr2jhjcfn9MfUGkQm3HTsdMMe0YvKBE9i82Y16EFgmo12qSpwX/qBEOUywU
2 mOZy3MjRBoquEUSOW19JQ1suRPEQna/cQtmf98R6mMmdBzNVLcKmseRC5WgybMD
N4aW1mDOsAy9Er0XoCIQIpHWvnl10J48E2BuOwondjTAHh17A/lglHMfYqb43X7p
UfK7OdsJmvqxBtHIGJ0Of3cMOTjksIPGTnUtTeK7jO26MMHBOB0oOs8SuCU90y0x
mD1HGP6ZCn75Gf28dxDD7W27amCaxMSEVTKFNBZ0R4n72icvXzbbkKe9uVrhvimI
I /UwbXu6KvU5y0uT6YUXguOXuJ/oLYhg1g/27AN06Xx/45SGqAVbSu+ytaV77ulK
utSdzXVG786hkvYp/TSO1wNePsSNS/UGSJZcqQxZ4JsKT + G4P9o5iw = =
-----END RSA PRIVATE KEY-----A key without password protection:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# less client.key
-----BEGIN PRIVATE KEY-----
MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDQbfsXBKOMEiTC
Mhfkr6c/cCjR4UhMHSlFnCmraMePidWsU94yleeZmmqWrAx9NbvetfwSBgMdI+Rz
GIT77HlphtiV11S50s93tGzLxawZ2S/TuNe+OHK/gp6/JcY4IDuLx+A51W033QI0
TZBFL68k26W7J8jRT0lt+6remhtegheYQYG/NIBno+xYuzRtzhgw28ThpOiSU3u6
buSfSK0yq + 6zhjyxcEDdYhx + H2IfZJgy4ICEWFlpjTxW6uKgPpfrij1mPCJA639h
t5XDGEpBxBeK + JqjbAT0nGEO4U32HpEyfona6Yvt9awQzN7ACZwec0qQOESDnBUX
al2YuS4tAgMBAAECggEAFJBlT5YTlMRNgCN1HAkAlU +5 l0uE2I2f/tAzhEfoOI8 +
16vGpnMH0K3dilQbP1dH2 + dUoWqacAuUhI6MwFZKfFDjmZUPNO5JrePNQCWWn05A
W8FG9 + uV1rNKek19yxfTCC2GP + 6Awhw6FXL5ovnba + WH6nk4ZP5PnlxQNXo67Q33
I3fq8tl6h+CYPV3yDRYhZITS+qsM/hV05sr1gLMMAhqwsNKNiwdYpOgtERjM31QK
VD7oIz0u1K/Yq/mXTvaeMD2VKc7Y7Cv0KTIjJaPzmDyxDfoOj7qNELr1qWyIIOTq
yovdvV6QCiNJjEGVMfyGziPhRTmeg5NGZ + hFigVhOQKBgQD0FhnFcQfPOTmjaYvp
w5ZN4wXuhW9ECNdVrr96zVLvR4mB5gXhrSNBG7tJQVzQ5nzBM6ilDyyNvdXpnACk
IAyiOHkbgZohT1m3ouHiPQ3sMCha + iI1cPl5focqK5bi6u1Y0tUrzzyyUD + x7mlW
/7OkLJq8olgK40pxgaJyqPikTwKBgQDamlfxFyAMHAAEIBzdbhxLbS7wRfUzaw+M
lAtprOFdHFvhdY79cEWRmi+Tcb0vG+NrS9GdxJ5zTqYWKXHVo9Si1vIx/dAPPEiu
SDTJd1kbJr0uFp0LkeYKvMrswcnXjDhHrWTIf5Z27QAGqdOs/A0WpDgSrRGRE55K
D54NNhwawwKBgQCpneoOwHsyz4t0OVZW/emm+koW9HbbimFJU4QQbDpjuQyfTh9s
Mgq7pe/ B +j/rkx/Ciol5GlBVt0O9cQEpDHR7Dk9qg+d9mky4ReI+ezr/lH+WqRe4
l2dlvD1lWqEh0ytYfDpSlVKSfx2kIg7lvA8z2SM2ToJsHoKO2gzvLOiMFQKBgG8Z
2nAxrJv67xcpqwHvIHpFN4HuTDyhwPlVlKdUsgU8kIX51X6BPRpPHrb1K41xwxeZ
JSPl9hHsewTwc2Z9KWjJjbZSpRfZL6dEE3ABNExUV/nAjHiA4kW6xfemVgsyvLfR
LFOqZLJ0qXq5fVeBkrVbKRR02WS + b8h8P3u/cHE5AoGAeIRhKHvmAZa4k7MqsGn3
JbdfiMCsGLUSsXhS34VVSM76Q8ffVJIHB1ItfAflJtjeGbrzmTb/Stn3/6huhJ1E
h8RU00et/PwaMsubZGOcxofvOmDuOT358hzAFA4tDnM4sscY0I5bL85hVHJJVkuJ
VeBUAhUTLkYCAuKzgsMXzFw=
-----END PRIVATE KEY-----The time when the server certificate expires:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl x509 -in certs/ca1.crt -dates
notBefore=Jun 19 06:24:31 2020 GMT
notAfter=Jun 17 06:24:31 2030 GMT
-----BEGIN CERTIFICATE-----
MIIC+TCCAeGgAwIBAgIJALX+zZaaXqmKMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNV
BAMMCHJvb3QtY2ExMB4XDTIwMDYxOTA2MjQzMVoXDTMwMDYxNzA2MjQzMVowEzER
MA8GA1UEAwwIcm9vdC1jYTEwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIB
AQDIqfTdRECTe0KplSqDZV4cmkpTOKA1pWIUHLKBjkSvKpQ1lOgnfhwkwteo/6E D
wFnsoKSmScqHtfH2tNRj49rdz8aCZjX3lORnk2oufKoBSG1mW0ny0cvgWcekrkEn
CRWoZpc1wSlHUnH9NVTSaUhdO+ujNRXbYLdBOjiR/ydtjD/Bozk7TJTO+RnSbDUl
SdusNS1cmwlPpI3kYkvsd3mExfKooC8GTKXNOfgdFIxCHnb19tG8jJ7te/E1ooP2
HDAiPgmuiw6 +6jRC4ucuv3mwdCUGyx3 + 8e6SJFEFTwrqyhvblwopEbCtrv3EbZni
n9MV/vm3tMTpxjEfR5SfUqmXAgMBAAGjUDBOMB0GA1UdDgQWBBQ35T9WMJAx + g4Y
po6+TO/07AxeTzAfBgNVHSMEGDAWgBQ35T9WMJAx+g4Ypo6+TO/07AxeTzAMBgNV
HRMEBTADAQH/MA0GCSqGSIb3DQEBCwUAA4IBAQBBDnfCR5gIPTLrnDk7JGj1EBHD
/FNSe36guBTQZlkOoqmbK8dpRwJpSkiqC2spLiaMd4K2UHHvrQvngmBRQpkVc5+m
y6AQ7br65uBZHOmrPKmhdCAnSdOl5fP3w6g64lFLdoog8AmydherHhbbD78ecy3A
Pz05Xh + LrpuB0eO + gRf9iN8A + EU8FDyHIz0VKbRf3QZndwfchVYHJD1gBGuPZl7k
C1s88MjIcrczp + Wy76LhQfDCz5rduksK7XM5G0bTG0DlNVOg84jEd6UykMoT6j1k
7aYQ0Y0wMgIWVxDdViP14xDNgZnsjsFKljh9XM3x7gYH4GNI5D0jJ4KGM5Dn
-----END CERTIFICATE-----
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl x509 -in certs/ca1.crt -dates -noout
notBefore=Jun 19 06:24:31 2020 GMT
notAfter=Jun 17 06:24:31 2030 GMTOr:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl x509 -in client123.crt -text
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
cb:28:cd:10:13:9d:aa:76
Signature Algorithm: sha256WithRSAEncryption
Issuer: CN=root-ca1
Validity
Not Before: Jun 19 10:30:19 2020 GMT
Not After : Jun 19 10:30:19 2021 GMT
Subject: CN=dbuser1
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
00:d0:6d:fb:17:04:a3:8c:12:24:c2:32:17:e4:af:
a7:3f:70:28:d1:e1:48:4c:1d:29:45:9c:29:ab:68:
c7:8f:89:d5:ac:53:de:32:95:e7:99:9a:6a:96:ac:
0c:7d:35:bb:de:b5:fc:12:06:03:1d:23:e4:73:18:
84:fb:ec:79:69:86:d8:95:d7:54:b9:d2:cf:77:b4:
6c:cb:c5:ac:19:d9:2f:d3:b8:d7:be:38:72:bf:82:
9e:bf:25:c6:38:20:3b:8b:c7:e0:39:d5:6d:37:dd:
02:34:4d:90:45:2f:af:24:db:a5:bb:27:c8:d1:4f:
49:6d:fb:aa:de:9a:1b:5e:82:17:98:41:81:bf:34:
80:67:a3:ec:58:bb:34:6d:ce:18:30:db:c4:e1:a4:
e8:92:53:7b:ba:6e:e4:9f:48:ad:32:ab:ee:b3:86:
3c:b1:70:40:dd:62:1c:7e:1f:62:1f:64:98:32:e0:
80:84:58:59:69:8d:3c:56:ea:e2:a0:3e:97:eb:8a:
3d:66:3c:22:40:eb:7f:61:b7:95:c3:18:4a:41:c4:
17:8a:f8:9a:a3:6c:04:f4:9c:61:0e:e1:4d:f6:1e:
91:32:7e:89:da:e9:8b:ed:f5:ac:10:cc:de:c0:09:
9c:1e:73:4a:90:38:44:83:9c:15:17:6a:5d:98:b9:
2e:2d
Exponent: 65537 (0x10001)
Signature Algorithm: sha256WithRSAEncryption
bd:5f:86:ee:d2:d7:b5:95:da:dc:0c:2f:18:fc:a9:f9:99:c6:
07:e8:81:d3:1f:1a:d9:04:e6:fc:4e:e0:42:2c:6a:4b:a3:9b:
8c:47:0d:b4:61:0f:fd:0e:12:ba:ec:c9:60:c9:8e:b5:2a:ef:
33:ee:78:3c:20:95:e3:39:24:3b:77:e3:fa:4a:dd:f3:15:b2:
d7:bf:a9:0e:4b:7c:22:ae:f4:16:e6:18:11:a3:99:58:84:58:
b2:9e:b4:60:de:f2:24:3a:8c:e2:93:4c:36:b9:00:d5:74:af:
a6:d3:94:8c:42:d2:11:54:11:37:e2:54:c7:4e:8c:5e:31:a4:
29:71:0a:72:c2:59:78:e3:40:a6:53:75:9c:51:a1:73:2c:46:
68:22:81:ee:0b:0d:c1:82:c4:25:0d:8c:47:88:a1:02:88:20:
d0:8e:43:b2:96:91:c8:a1:a7:42:ed:67:32:c6:20:5c:18:51:
73:e5:cd:4d:e1:7e:68:72:88:61:8c:a0:20:61:c4:8f:eb:71:
10:ac:8d:f1:0f:26:b3:b1:7a:8e:17:ec:e2:a8:3c:9d:39:5e:
37:3a:ba:54:4d:d3:77:84:e5:50:d2:27:95:58:f5:3c:30:05:
3e:5a:4d:a7:93:36:35:eb:fe:0c:b0:b4:b4:e9:1d:d2:6d:f7:
a8:39:48:76
-----BEGIN CERTIFICATE-----
MIICoTCCAYkCCQDLKM0QE52qdjANBgkqhkiG9w0BAQsFADATMREwDwYDVQQDDAhy
b290LWNhMTAeFw0yMDA2MTkxMDMwMTlaFw0yMTA2MTkxMDMwMTlaMBIxEDAOBgNV
BAMMB2RidXNlcjEwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDQbfsX
BKOMEiTCMhfkr6c/cCjR4UhMHSlFnCmraMePidWsU94yleeZmmqWrAx9NbvetfwS
BgMdI+RzGIT77HlphtiV11S50s93tGzLxawZ2S/TuNe+OHK/gp6/JcY4IDuLx+A5
1W033QI0TZBFL68k26W7J8jRT0lt+6remhtegheYQYG/NIBno+xYuzRtzhgw28Th
pOiSU3u6buSfSK0yq+6zhjyxcEDdYhx+H2IfZJgy4ICEWFlpjTxW6uKgPpfrij1m
PCJA639ht5XDGEpBxBeK+JqjbAT0nGEO4U32HpEyfona6Yvt9awQzN7ACZwec0qQ
OESDnBUXal2YuS4tAgMBAAEwDQYJKoZIhvcNAQELBQADggEBAL1fhu7S17WV2twM
Lxj8qfmZxgfogdMfGtkE5vxO4EIsakujm4xHDbRhD/0OErrsyWDJjrUq7zPueDwg
leM5JDt34/pK3fMVste/qQ5LfCKu9BbmGBGjmViEWLKetGDe8iQ6jOKTTDa5ANV0
r6bTlIxC0hFUETfiVMdOjF4xpClxCnLCWXjjQKZTdZxRoXMsRmgige4LDcGCxCUN
jEeIoQKIINCOQ7KWkcihp0LtZzLGIFwYUXPlzU3hfmhyiGGMoCBhxI/rcRCsjfEP
JrOxeo4X7OKoPJ05Xjc6ulRN03eE5VDSJ5VY9TwwBT5aTaeTNjXr/gywtLTpHdJt
96g5SHY=
-----END CERTIFICATE-----Check whether the private key has been tampered with:
[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl rsa -check -in ./client.key
RSA key ok
writing RSA key
-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEA0G37FwSjjBIkwjIX5K+nP3Ao0eFITB0pRZwpq2jHj4nVrFPe
MpXnmZpqlqwMfTW73rX8EgYDHSPkcxiE++x5aYbYlddUudLPd7Rsy8WsGdkv07jX
vjhyv4KevyXGOCA7i8fgOdVtN90CNE2QRS+vJNuluyfI0U9Jbfuq3pobXoIXmEGB
vzSAZ6PsWLs0bc4YMNvE4aToklN7um7kn0itMqvus4Y8sXBA3WIcfh9iH2SYMuCA
hFhZaY08VurioD6X64o9ZjwiQOt/YbeVwxhKQcQXiviao2wE9JxhDuFN9h6RMn6J
2umL7fWsEMzewAmcHnNKkDhEg5wVF2pdmLkuLQIDAQABAoIBABSQZU+WE5TETYAj
dRwJAJVPuZdLhNiNn/7QM4RH6DiPPterxqZzB9Ct3YpUGz9XR9vnVKFqmnALlISO
jMBWSnxQ45mVDzTuSa3jzUAllp9OQFvBRvfrldazSnpNfcsX0wgthj/ugMIcOhVy
+aL522vlh+p5OGT+T55cUDV6Ou0N9yN36vLZeofgmD1d8g0WIWSE0vqrDP4VdObK
9YCzDAIasLDSjYsHWKToLREYzN9UClQ+6CM9LtSv2Kv5l072njA9lSnO2Owr9Cky
IyWj85g8sQ36Do+6jRC69alsiCDk6sqL3b1ekAojSYxBlTH8hs4j4UU5noOTRmfo
RYoFYTkCgYEA9BYZxXEHzzk5o2mL6cOWTeMF7oVvRAjXVa6/es1S70eJgeYF4a0j
QRu7SUFc0OZ8wTOopQ8sjb3V6ZwApCAMojh5G4GaIU9Zt6Lh4j0N7DAoWvoiNXD5
eX6HKiuW4urtWNLVK888slA/se5pVv+zpCyavKJYCuNKcYGicqj4pE8CgYEA2ppX
8RcgDBwABCAc3W4cS20u8EX1M2sPjJQLaazhXRxb4XWO/XBFkZovk3G9Lxvja0vR
ncSec06mFilx1aPUotbyMf3QDzxIrkg0yXdZGya9LhadC5HmCrzK7MHJ14w4R61k
yH+Wdu0ABqnTrPwNFqQ4Eq0RkROeSg+eDTYcGsMCgYEAqZ3qDsB7Ms+LdDlWVv3p
pvpKFvR224phSVOEEGw6Y7kMn04fbDIKu6Xvwfo/65MfwoqJeRpQVbdDvXEBKQx0
ew5PaoPnfZpMuEXiPns6/5R/lqkXuJdnZbw9ZVqhIdMrWHw6UpVSkn8dpCIO5bwP
M9kjNk6CbB6CjtoM7yzojBUCgYBvGdpwMayb+u8XKasB7yB6RTeB7kw8ocD5VZSn
VLIFPJCF+dV+gT0aTx629SuNccMXmSUj5fYR7HsE8HNmfSloyY22UqUX2S+nRBNw
ATRMVFf5wIx4gOJFusX3plYLMry30SxTqmSydKl6uX1XgZK1WykUdNlkvm/IfD97
v3BxOQKBgHiEYSh75gGWuJOzKrBp9yW3X4jArBi1ErF4Ut+FVUjO+kPH31SSBwdS
LXwH5SbY3hm685k2/0rZ9/+oboSdRIfEVNNHrfz8GjLLm2RjnMaH7zpg7jk9+fIc
wBQOLQ5zOLLHGNCOWy/OYVRySVZLiVXgVAIVEy5GAgLis4LDF8xc
-----END RSA PRIVATE KEY-----How to Convert a PostgreSQL Time Interval to a Numeric Value
liptanbiswas - July 15, 2018
Alibaba Clouder - May 11, 2018
digoal - December 20, 2021
Alibaba Clouder - August 16, 2019
Alibaba Clouder - July 15, 2019
Alibaba Clouder - November 27, 2018
Certificate Management Service
You can use Certificate Management Service to issue, deploy, and manage public and private SSL/TLS certificates.
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by digoal
