[珍藏级] PostgreSQL ssl 证书配置 - 防止中间攻击者 - 以及如何使用证书无密码登录配置cert

digoal 2020-06-19

17216

作者

digoal

日期

2020-06-19

背景

视频回放

SSL认证除了加密数据，也可以识别目标端的真伪，防止网络中间人的伪装攻击。本文主要分析为什么需要ssl证书?

1、客户把钥匙丢了, 拿到钥匙的人就可以开这个钥匙对应的门.

2、有一扇假的门, 但是客户并不知道, 客户拿着钥匙去开门, 这扇门把你的钥匙复制了. 然后拿着复制的钥匙可以去开真的门.
例如ATM取款机, 不法分子在前面粘了一个card复制装置, 复制卡的内容, 然后打电话骗取你的密码.

实际上就是伪装的问题. 为了解决这两个问题, ssl里面引入了证书的概念, 用证书来判断身份.

1、客户把钥匙丢了, 别人拿着钥匙开不了门, 因为门会问怎么证明你是你.

2、客户也不需要担心有假的门, 因为客户先会问, 门啊门, 你是不是那扇门, 你不是我就不掏钥匙, 你也没发复制我的钥匙.

具体怎么做?

以PostgreSQL 12为例.

The different values for the sslmode parameter provide different levels of protection. SSL can provide protection against three types of attacks:

Eavesdropping
If a third party can examine the network traffic between the client and the server, it can read both connection information (including the user name and password) and the data that is passed. SSL uses encryption to prevent this.

Man in the middle (MITM)
If a third party can modify the data while passing between the client and server, it can pretend to be the server and therefore see and modify data even if it is encrypted. The third party can then 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. There are also several other attack methods that can accomplish this. SSL uses certificate verification to prevent this, by authenticating the server to the client.

Impersonation
If a third party can pretend to be an authorized client, it can simply access data it should not have access to. Typically this can happen through insecure password management. SSL uses client certificates to prevent this, by making sure that only holders of valid certificates can access the server.

例子

环境

```
CentOS 7.8 x64

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

部署

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*

server a, 根服务器1, 用于给客户端颁发证书

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"

或者分解步骤如下 :

```
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.crt
```

```
Signature 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:
......
```

ca1.key 私钥
ca1.crt 公钥

server b, 根服务器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=root-ca2"

server c, 根服务器2的授权机构3, 用于给数据库颁发证书

```

intermediate

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

把intermediate.csr发给根服务器2去盖章(内容发过去就可以)

以下在根服务器2操作

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=intermediate.yourdomain.com Certificate: Data: Version: 1 (0x0) Serial Number: d9:80:47:67:e3:6d:7a:ac Signature Algorithm: NULL ....

盖章后的证书intermediate.crt发给server c(根服务器2的授权机构3)

server d, 数据库服务器

生成key、证书签名申请文件.

```

leaf

openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=dbhost.domain.name.or.ipaddress"
chmod og-rwx server.key
```

因为key,pub是一对多的, 所以以后证书吊销了还可以继续申请.

openssl req -new -key server.key -out server123.csr -subj "/CN=dbhost.domain.name.or.ipaddress"

把server.csr发给授权机构服务器3去盖章(内容发过去就可以)

以下在授权机构服务器3操作

openssl x509 -req -in server.csr -text -days 365 -CA intermediate.crt -CAkey intermediate.key -CAcreateserial -out server.crt

Signature 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 ....

盖章后的证书server.crt发给server d(数据库服务器)

server.crt, server.key 都放到 $PGDATA/ 目录中, 设置为600权限

server e, 客户端

生成key、证书签名申请文件.

指定cn=dbuser1, 如果数据库pg_hba.conf中配置了要求验证客户端verify-full, 那么客户端使用这个证书只能使用dbuser1这个数据库用户登录.

```

leaf

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

因为key,pub是一对多的, 所以以后证书吊销了还可以继续申请.

openssl req -new -key client.key -out client123.csr -subj "/CN=dbuser1"

把server.csr发给根服务器1去盖章(内容发过去就可以)

以下在根服务器1操作

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=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 ....

盖章后的证书client.crt发给server e(客户端服务器)

client.crt和client.key 都放到客户端home/.postgresql目录中 , 设置为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/*

这样就够了吗? 还不够, 有证书, 对方怎么校验证书真伪呢? 还需要把办证机构的公钥分发下去, 让他们可以辨别真伪.

server d, 数据库服务器

需要校验客户端证书真伪, 所以需要给客户端颁发证书的机构的公钥

server a : certs/ca1.crt 内容写入 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.crt

配置数据库参数, 重启数据库.

```
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 ROLE
```

server e, 客户端

需要校验数据库服务器证书真伪, 所以需要给数据库服务器颁发证书的机构的公钥

server b : certs/ca2.crt 第一 server c : intermediate.crt 第二

ca2.crt 和 intermediate.crt 内容 cat 后写入 server e: ~/.postgresql/ca2_int.crt

chmod 600 ~/.postgresql/ca2_int.crt

连接测试1:

密码认证

1、校验数据库是不是伪造的(例如攻击者利用dns劫持, 伪造数据库), 即检查数据库证书真伪.

```
vi pg_hba.conf

hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5

pg_ctl reload
```

```
postgres@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=# \q
```

2、校验数据库证书的cn是否与连接的目标一致(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"

因为数据库证书里面的CN=dbhost.domain.name.or.ipaddress, 而客户连接的是192.168.0.245.

如何解决?

方法1: 用户有自己的dns, 可以配置dns解析dbhost.domain.name.or.ipaddress到192.168.0.245

方法2: 配置客户端的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
```

如下:

```
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=#
```

连接测试1.2

如果想在server.crt中加入多个Hostname, 需要采用如下方法.

参考:
https://my.oschina.net/sskxyz/blog/1554093
https://blog.csdn.net/u013066244/article/details/78725842
《PostgreSQL openssl - multi cn 给SSL证书绑定多个CN hostname - 公网 HOSTNAME + 私网 HSOTNAME》

下面是苹果机器的测试, 如果是centos请拷贝/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
```

接着使用这个临时配置生成证书：

```
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/C=CN" -config ./openssl.cnf

一路全部使用默认, 特别注意: CN 一定要设置为空.

```

查看证书请求文件的内容, 已经包含了2个hostname：

```
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

```

接着使用这个临时配置以及根服务器证书生成签名:

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.cnf

然后就可以把server.key, server.crt拷贝到数据库服务器使用了.

连接测试2:

密码认证

1、校验客户端是不是伪造的, 客户的证书真伪.

数据库:

```
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 reload
```

客户端:

```
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
```

随便改一个变量让它报错, 可以说明数据库已经问客户端要了证书. 即数据库在检查这个客户端是不是伪造的

```
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、校验客户端是不是伪造的, 客户的证书真伪. 并且要求检查客户端证书的CN是否与正在连接数据库的用户同名. 不同名也不允许登录.

注意pg_hba.conf里配置clientcert=verify-full这个功能是PG 12版本新增的: https://www.postgresql.org/docs/12/release-12.html

签名颁发的客户端证书, CN=dbuser1

数据库:

```
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 reload
```

客户端:

export PGSSLMODE="require" # 不检查数据库真伪 export PGSSLCERT="/var/lib/pgsql/.postgresql/client.crt" export PGSSLKEY="/var/lib/pgsql/.postgresql/client.key"

使用与证书CN不同名的数据库用户登录, 失败.

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"

使用与证书CN同名的数据库用户登录, 成功.

```
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=#
```

连接测试3:

证书认证, 只要服务器认你的证书, 你就能登录. 怎么认? 你的证书是在数据库ssl_ca_file = 'ca1.crt'这个参数里面配置的权威机构颁发的.

默认clientcert=verify-full并且不能改, 所以要求客户端登录的数据库用户必须是客户端证书中CN对应的用户

使用cert认证方法

```
vi pg_hba.conf

hostnossl all all 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 cert

pg_ctl reload
```

无密码登录

```
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=#
```

连接测试4, 吊销客户端证书测试:

类似于离婚需要离婚证一样, 你需要带着结婚证去离婚, 并且有离婚本本. 吊销也需要带着证书去颁证机构生成吊销证书.

需要在颁证机构先执行几个操作, 否则会报错.

```

touch /etc/pki/CA/index.txt

echo 1000 > /etc/pki/CA/crlnumber

没有执行以上两步会报错.

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:
```

接下来在颁证机构生成client.crt证书的吊销证书.

```
[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 Updated
```

生成吊销证书

[root@iZbp135pwcjjoxqgfpw9k1Z ~]# openssl ca -gencrl -out client.crl -cert certs/ca1.crt -keyfile keys/ca1.key Using configuration from /etc/pki/tls/openssl.cnf

检查吊销证书

[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=dbuser1

吊销证书的内容

[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-----

把 client.crl 的内容拷贝到数据库服务器的black.crl里面.

在数据库端配置ssl_crl_file参数, 放入客户端证书内容, 表示这个证书吊销了. 如果数据库配置了校验客户端证书verify-ca或verify-full, 那么使用这个证书的客户端不允许被连接.

把客户端的client.crl 内容输入到数据库端的 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 reload
```

客户端再连就不允许了.

```
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 revoked
```

可以在客户端重新发起证书申请, 到权威机构签名盖章, 然后使用新的证书登录数据库, 方法如下:

客户端

openssl req -new -key client.key -out client123.csr -subj "/CN=dbuser1"

根服务器

openssl x509 -req -in client123.csr -text -days 365 -CA certs/ca1.crt -CAkey keys/ca1.key -CAcreateserial -out client123.crt

将client123.crt拷贝到客户端

使用新的证书client123.crt可以连接数据库了.

```
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 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
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
```

连接测试5, 吊销数据库证书测试:

与吊销客户端证书类似, 到数据库证书的发证机构(本例为授权机构)去吊销数据库证书server.crt , 生产吊销证书crl文件.

但是与客户端证书不同, 我们这个例子的数据库证书是二级授权机构颁发的, 要吊销这个证书, 必须在整个授权链的服务器上都执行吊销.

在二级颁证机构生成server.crt证书的吊销证书.

```

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 Updated
```

生成吊销证书

[root@iZbp1bc0dctm6mkipmqz9gZ ~]# openssl ca -gencrl -out server.crl -cert intermediate.crt -keyfile intermediate.key Using configuration from /etc/pki/tls/openssl.cnf

检查吊销证书

[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

吊销证书的内容

[root@iZbp1bc0dctm6mkipmqz9gZ ~]# cat server.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-----

在根颁证机构生成server.crt证书的吊销证书.

```

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 Updated
```

生成吊销证书

[root@iZbp1bc0dctm6mkipmqz9fZ ~]# openssl ca -gencrl -out server.crl -cert certs/ca2.crt -keyfile keys/ca2.key Using configuration from /etc/pki/tls/openssl.cnf

检查吊销证书

[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

吊销证书的内容

[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-----

把 server.crl 的内容拷贝到客户端的.postgresql/black.crl里面.

必须按授权链路顺序来存放, 先填入二级授权机构颁发的吊销证书, 再填入根授权机构颁发的吊销证书.

```
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
```

吊销证书生效后, 可以重新生成一个新的数据库证书来连接, 周期性执行吊销操作, 防止证书泄露.(就像我们银行的KEY,也会让你周期性的去更换)

数据库:

openssl req -new -key server.key -out server123.csr -subj "/CN=dbhost.domain.name.or.ipaddress"

授权机构:

```
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
```

数据库:

```
修改数据库参数 ssl_cert_file = 'server123.crt'

pg_ctl reload
```

数据库已经使用新的证书, 可以建立连接.

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
```

如果不想检查数据库是否使用了吊销证书, PGSSLCRL环境变量设置为空即可.

```
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、权威机构
2、权威机构的授权机构 . 你可以假想为权威机构太忙或者太远, 需要应对全球的证书, 所以需要授权其他机构来颁证.
3、客户端证书, 向权威或权威授权机构申请认证(盖章)
4、db端证书, 向权威或权威授权机构申请认证(盖章)
5、吊销证书列表, 类似离婚证，办理吊销时, 需要带着证书到证书颁证机构去申请吊销(如果颁发机构是授权机构, 那么需要到授权机构以及对应的权威机构去办理吊销), 例如证书泄露的情况，可以去吊销.
6、权威机构或权威授权机构发的, 用于验证证书真伪的文件(验证证书真伪, 即是否为权威机构盖的章) . 实际上就是颁发机构的公共证书(如果颁发机构是授权机构, 那么检查证书真伪需要同时拥有权威机构和授权机构的公共证书).

场景

1、user a 有一本证书, 由权威机构ca1盖章.

2、有一个自称"user a"的人要跟user b通讯. 因为ca1比较权威, 所以user b只认ca1颁发的证书. 那么user b 要确认user a的身份, 首先问 user a要a的证书, user b用ca1给的验证方法(ca1给b的文件, 用于验证a的证书是否为ca1颁发的证书), 判断user a 给的证书的真假, 如果是ca1颁发的证书, 然后再看证书的内容, 例如证书中的cn值等信息, 根据业务逻辑进一步要做什么.

3、对于授权机构颁发的证书, 需要一个链来验证它的真伪, 例如根-授权机构1-子授权机构2. 由授权机构2颁发的证书, 需要受到根,授权机构1,子授权机构2的多方验证, 所以需要这三个机构都给出验证方法(颁证机构的公共证书黏贴在一个文件中).

4、非对称加密技术

key, pub对. pub加密, key解密. 证书中就包含了pub. 所以user a把证书给user b后, user b就可以解开证书里面的pub来进行数据加密, 并且把加密后的数据传输给user a, user a用key能解密.

每一本证书都有一个对应的key, 一个key可以创建出很多本证书.

5、如果 user c 捡到一本ca1颁发的证书, 能与user b通讯吗? 有几种情况:

user b认定证书是ca1颁发的, 然后再看证书的内容, 例如证书中的cn值等信息, psql -h hostname, 如果cn与hostname不同, 所以不会进行通信.

user a 向权威机构挂失它的证书, 权威机构发布公告, user a的证书已经吊销了, user b在它的吊销证书列表里面存储吊销证书. 所以不会进行通信.

user a 的证书到期. 所以不会进行通信.

6、客户端可以通过权威机构签名的证书判断数据库的真伪.

7、数据库可以通过权威机构签名的证书判断客户端的真伪.

8、数据库可以使用cert认证方法(pg_hba.conf配置为cert认证方法), 不需要用户提供密码, 只需要提供证书登录数据库.

校验模式

1、客户端 PGSSLMODE verify-ca : 校验数据库证书真伪.
2、客户端 PGSSLMODE verify-full : 校验数据库证书真伪, 以及证书的CN内容与客户端连接的数据库hostname一致.
3、数据库端 pg_hba.conf option clientcert=verify-ca : 校验客户端证书真伪.
4、数据库端 pg_hba.conf option clientcert=verify-full 或 cert认证方法 : 校验客户端的证书真伪, 以及证书的CN内容与连接的数据库用户名一致. (这样就可以为每个DBUSER办法一个证书, 分发给不同的应用程序, 提高应用级租户安全性.)

数据库相关文件和参数

https://www.postgresql.org/docs/devel/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SSL

ssl (boolean)
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) 数据库证书私钥
Specifies the name of the file containing the SSL server private key. 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) 数据库证书公钥, 经过权威机构盖章后的文件
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 (string) 客户端证书颁证机构的公钥, 用于检测客户端证书真伪
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) 吊销证书(例如有些客户端证书遗失了, 放在这里表示这些证书失效, pg_hba.conf配置了verify-ca或verify-full时，不允许已吊销证书的客户端登录)
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.

客户端相关文件和环境变量

https://www.postgresql.org/docs/devel/libpq-envars.html
https://www.postgresql.org/docs/devel/libpq-connect.html#LIBPQ-CONNECT-SSLCERT

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、客户端登录DB, 在密码认证之前加入双方的身份证书验证后, 解决了哪些安全问题?
- 客户密码泄露后, 拿到密码的攻击者可以伪装成客户登录.
- db和客户的网络之间有攻击者, 攻击者可以伪装成db服务器, 套取客户的密码.

2、云数据库服务? 谁来颁证? 同样也分数据库证书和客户端证书.
- 1、第一种是采用用户和云厂商都认可的第三方机构签名
- 2、云厂商自己的机构签名
- 3、用户自己上传server.key, server.crt, root.crt, x.crl (用户自己控制, 证书可以自己签名或第三方签名. root.crt 用于配合pg_hba.conf verify-ca verify-full验证客户端证书。x.crl用于配合pg_hba.conf verify-ca verify-full过滤吊销的客户端证书。)
- 注意如果要用户上传，千万不能使用密码保护server.key文件，否则数据库启动是需要输入key的密码，没有密码会导致启动数据库失败出现故障。
- 对于用户自己控制的方式, 最弹性, 但是要求用户可以上传证书, 可配置pg_hba.conf, 建议可配置postgresql.conf的ssl min tls version等参数(ssl_min_protocol_version ssl_max_protocol_version).
- 如何判断用户上传的私钥KEY文件没有passphase保护？ - pg_hba.conf 分两段，第一段内容固定，包括一些默认规则（云服务的acl规则优先匹配）。第二段append在第一段后面，用户可配置，用户配置不影响云管理服务的acl。
- 证书的过期时间监测。
- crl文件正确性，以及与ca.crt匹配，否则启动数据库将失败。
- 4、证书内容如下: - DB: - ssl_key_file = db.key - ssl_cert_file = db.crt - db_ca.key (给db颁证的机构自己保存, 生成db_ca.crt以及给db.csr签名生成db.crt时需要) - ssl_ca_file = client_ca.crt - ssl_crl_file = client_black.crl - Client: - PGSSLKEY = client.key - PGSSLCERT = client.crt - client_ca.key (给client颁证的机构自己保存, 生成client_ca.crt以及给client.csr签名生成client.crt时需要) - PGSSLROOTCERT = db_ca.crt - PGSSLCRL = db_black.crl

有密码保护的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 2mOZy3MjRBoquEUSOW19JQ1suRPEQna/cQtmf98R6mMmdBzNVLcKmseRC5WgybMD N4aW1mDOsAy9Er0XoCIQIpHWvnl10J48E2BuOwondjTAHh17A/lglHMfYqb43X7p UfK7OdsJmvqxBtHIGJ0Of3cMOTjksIPGTnUtTeK7jO26MMHBOB0oOs8SuCU90y0x mD1HGP6ZCn75Gf28dxDD7W27amCaxMSEVTKFNBZ0R4n72icvXzbbkKe9uVrhvimI i/UwbXu6KvU5y0uT6YUXguOXuJ/oLYhg1g/27AN06Xx/45SGqAVbSu+ytaV77ulK utSdzXVG786hkvYp/TSO1wNePsSNS/UGSJZcqQxZ4JsKT+G4P9o5iw== -----END RSA PRIVATE KEY----- ```

没有密码保护的key:
[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+5l0uE2I2f/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-----

证书过期时间:
``` [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/6eD 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 GMT 或者 [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----- ```

检查私钥有没有被篡改过:
[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-----

扩展思考

目前证书校验存在的缺陷? 封闭式环境? 信任第三方, 第三方可靠吗? 区块链?

参考

https://www.postgresql.org/docs/13/ssl-tcp.html#SSL-CLIENT-CERTIFICATES

https://www.postgresql.org/docs/13/libpq-ssl.html

https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-CONNECT-SSLPASSWORD

https://info.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers

https://www.postgresql.org/docs/13/auth-pg-hba-conf.html

https://www.jianshu.com/p/8aadab1c3185

https://www.jianshu.com/p/e5f46dcf4664

https://www.cnblogs.com/littleatp/p/5878763.html

https://blog.csdn.net/liuchunming033/article/details/48470575

https://www.postgresql.org/docs/devel/libpq-envars.html

https://www.postgresql.org/docs/devel/libpq-connect.html#LIBPQ-CONNECT-SSLCERT

https://www.postgresql.org/docs/devel/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SSL

https://www.postgresql.org/docs/13/auth-cert.html

https://blog.csdn.net/caomiao2006/article/details/23462929

https://blog.csdn.net/u010129119/article/details/53419581

文章转载自digoal，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[珍藏级] PostgreSQL ssl 证书配置 - 防止中间攻击者 - 以及如何使用证书无密码登录配置cert

作者

日期

标签

背景

例子

server a, 根服务器1, 用于给客户端颁发证书

less root.csr

server b, 根服务器2

server c, 根服务器2的授权机构3, 用于给数据库颁发证书

intermediate

server d, 数据库服务器

leaf

server e, 客户端

leaf

这样就够了吗? 还不够, 有证书, 对方怎么校验证书真伪呢? 还需要把办证机构的公钥分发下去, 让他们可以辨别真伪.

server d, 数据库服务器

- SSL -

server e, 客户端

连接测试1:

vi /etc/hosts

连接测试1.2

此文件的格式是类似 ini 的配置文件格式，找到 [ req ] 段落，加上下面的配置：

这段配置表示在生成 CSR 文件时读取名叫 v3_req 的段落的配置信息，

因此我们再在此配置文件中加入一段名为 v3_req 的配置：

Extensions to add to a certificate request

这段配置中最重要的是在最后导入名为 alt_names 的配置段，

因此我们还需要添加一个名为 [ alt_names ] 的配置段：

一路全部使用默认, 特别注意: CN 一定要设置为空.

连接测试2:

连接测试3:

连接测试4, 吊销客户端证书测试:

touch /etc/pki/CA/index.txt

echo 1000 > /etc/pki/CA/crlnumber

连接测试5, 吊销数据库证书测试:

touch /etc/pki/CA/index.txt

echo 1000 > /etc/pki/CA/crlnumber

openssl ca -revoke ./server.crt -cert ./intermediate.crt -keyfile ./intermediate.key

touch /etc/pki/CA/index.txt

echo 1000 > /etc/pki/CA/crlnumber

角色

场景

校验模式

数据库相关文件和参数

客户端相关文件和环境变量

小结

扩展思考

参考

PostgreSQL 许愿链接

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

评论