PostgreSQL+SSL链路测试

SSL一个各种证书在此就不详细介绍了，PostgreSQL要支持SSL的前提需要打开openssl选项，包括客户端和服务器端。

测试过程。

1. 生成私钥

root用户：

mkdir -p /opt/ssl/private
mkdir -p /opt/ssl/share/ca-certificateschmod 755 -R /opt/ssl
chown -R postgres134:postgres134 /opt/ssl/shareopenssl genrsa -des3 -out /opt/ssl/private/trustly-ca.key 2048#需两次输入密码，测试时输入postgres，生成文件trustly-ca.keychmod 444 /opt/ssl/private/trustly-ca.key#查看私钥内容file /opt/ssl/private/trustly-ca.key
/opt/ssl/private/trustly-ca.key: PEM RSA private key

 2. 生成公钥证书

openssl req -new -x509 -days 3650 -subj '/C=CN/ST=Beijing/L=Chaoyang/O=YZR/CN=trustly' -key /opt/ssl/private/trustly-ca.key -out /opt/ssl/share/ca-certificates/trustly-ca.crt
Enter pass phrase for /opt/ssl/private/trustly-ca.key:
#输入私钥的密码
#查看公钥的文件信息
file /opt/ssl/share/ca-certificates/trustly-ca.crt
/opt/ssl/share/ca-certificates/trustly-ca.crt: PEM certificate

 3. 配置PG服务器部分，我的PG服务器是在postgres134用户下，因此需要

 su - postgres134

 在PG的PGDATA目录中需要生成三个文件

server.key
server.crt
root.crt #containing the CA for the server certificate, plus your client certificate (postgresql.crt)

 生成server.key

#生成server.keyopenssl genrsa -des3 -out $PGDATA/server.key 2048
#输入两次密码，我们都用postgres#移除密码, 为了方便做自启动脚本openssl rsa -in $PGDATA/server.key -out $PGDATA/server.key
#输入私钥的密码#修改文件权限
chmod 400 $PGDATA/server.key#查看文件属性
file $PGDATA/server.key
/home/postgres123/pgdata/server.key: PEM RSA private key

 生成server.csr(服务器签名)

openssl req -new -nodes -key $PGDATA/server.key  -out $PGDATA/server.csr -subj '/C=CN/ST=Beijing/L=Chaoyang/O=YZR/CN=geoscene.yzr.local'#查看文件属性
file $PGDATA/server.csr
/home/postgres123/pgdata/server.csr: PEM certificate request

生成server.crt

#使用CA生成server.crtopenssl req -x509 -key /opt/ssl/private/trustly-ca.key -in $PGDATA/server.csr -out $PGDATA/server.crt
Enter pass phrase for /opt/ssl/private/trustly-ca.key:
//输入私钥的密码

生成root.crt

cp $PGDATA/server.crt $PGDATA/root.crt
#将公钥证书内容添加到root.crt后面
cat /opt/ssl/share/ca-certificates/trustly-ca.crt>>$PGDATA/root.crt

4 在postgresql.conf中配置：

ssl = onssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

5. 设置pg_hba.conf

hostssl    all             all             192.168.100.0/24     md5

6. 启动数据库

7.实际上这时候就可以用psql 连接，只不过是ssl的单向认证，也就是客户端对服务器端的认证

psql -h 192.168.100.51 -U postgres -d postgres -p 5433
Password for user postgres:
psql (13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.postgres=#

如果要实现双向认证，也就是服务器端对客户端的认证，还需要为客户端也配置证书，如下：

1. 客户端证书缺省引用地址（也可以通过环境变量引用到别的地址）

win：%APPDATA%postgresql/
*nix：~/.postgresql/

2. psql（libpq需要以下证书）

posgresql.crt
posgresql.csr
posgresql.key#linux 下生成证书的流程su - postgres134#创建默认存储路径~/.postgresqlmkdir ~/.postgresql
chmod 700 ~/.postgresql##生成postgresql.key
openssl genrsa -des3 -out ~/.postgresql/postgresql.key 1024##去掉密码
openssl rsa -in ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.keychmod 400 ~/.postgresql/postgresql.key##生成客户端签名postgresql.csropenssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr -subj '/C=CN/ST=Beijing/L=Chaoyang/O=YZR/CN=client1'##生成客户端证书
openssl x509 -req -in ~/.postgresql/postgresql.csr -CA /opt/ssl/share/ca-certificates/trustly-ca.crt -CAkey /opt/ssl/private/trustly-ca.key -out ~/.postgresql/postgresql.crt -CAcreateserial

 3. pg_hba.conf添加一行

hostssl    all             all             192.168.100.0/24     cert clientcert=1

5. 创建用户

首先需要创建数据库登录用户client1，因为之前postgresql.csr生成时指定的CN=client1,需要和登录用户匹配才能连接。
create role client1 login encrypted password 'client1';

4. psql链接

psql postgresql://client1:client1@192.168.100.51:5433/postgres
psql (13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help
postgres=>##验证客户端ca文件，由于没有把root.crt放到入相应目录中因此报错
psql postgresql://client1:client1@192.168.100.51:5433/postgres?sslmode=verify-ca
psql: error: root certificate file "/home/postgres134/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.cp $PGDATA/root.crt ~/.postgresql/psql postgresql://client1:client1@192.168.100.51:5433/postgres?sslmode=verify-ca
psql (13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>##使用verfify-full，除了验证证书文件还验证/CN项是否正确，/CN项为geoscene.yzr.local
psql postgresql://client1:client1@192.168.100.51:5433/postgres?sslmode=verify-full
psql: error: server certificate for "geoscene.yzr.local" does not match host name "192.168.100.51"##可以通过如下命令查看
openssl x509 -in root.crt -noout -text
Certificate:Data:Version: 3 (0x2)Serial Number:75:11:9b:20:22:d6:e1:04:a2:4d:01:87:d4:94:74:2d:b2:23:a0:dbSignature Algorithm: sha256WithRSAEncryptionIssuer: C = CN, ST = Beijing, L = Chaoyang, O = YZR, CN = geoscene.yzr.localValidityNot Before: Aug 23 06:51:55 2023 GMTNot After : Sep 22 06:51:55 2023 GMTSubject: C = CN, ST = Beijing, L = Chaoyang, O = YZR, CN = geoscene.yzr.localSubject Public Key Info:Public Key Algorithm: rsaEncryptionRSA Public-Key: (2048 bit)Modulus:00:dc:29:81:59:b2:a4:7e:60:79:6e:c4:9e:b7:b1:c4:6e:b6:92:d3:83:48:4d:f6:4a:d1:76:d2:d9:e4:29:ca:81:2f:29:de:7d:64:8a:23:ec:80:a2:0d:da:b2:7d:71:7f:ae:97:20:53:12:b1:0c:1b:1b:e3:38:b5:32:bb:d8:bc:d1:e9:cb:e1:87:c9:90:41:5d:c2:77:74:e5:36:78:35:69:bc:e0:ee:d1:51:0e:2c:44:bf:36:aa:81:5e:d4:93:76:d8:9a:55:60:27:49:48:ff:17:39:c7:f6:33:13:de:0b:65:29:7d:c2:1c:ff:28:ff:0a:59:2f:36:5a:92:98:2d:87:f6:af:b5:c1:16:fc:4b:1c:35:fa:85:6d:f0:81:f9:4f:13:f8:77:d6:da:41:dd:96:46:62:12:2c:93:75:ff:84:65:ae:61:7d:99:eb:fd:da:68:fb:aa:ad:23:9d:c8:af:60:94:e7:35:26:3d:92:29:f9:37:f3:30:1c:3c:ac:9b:81:2a:54:77:5b:ff:ec:c1:5f:7b:51:81:dd:d9:11:35:84:48:25:54:b1:d8:c5:6f:16:7d:85:4c:94:d8:6a:14:45:55:f7:f4:b5:56:d6:cb:17:aa:b1:55:ec:2d:eb:3c:e5:76:c1:cc:7f:aa:ef:f4:6b:55:77:24:da:43Exponent: 65537 (0x10001)X509v3 extensions:X509v3 Subject Key Identifier:55:E3:A3:6D:F2:90:6A:72:74:F1:F5:7F:B0:21:86:4E:20:BD:67:AEX509v3 Authority Key Identifier:keyid:55:E3:A3:6D:F2:90:6A:72:74:F1:F5:7F:B0:21:86:4E:20:BD:67:AEX509v3 Basic Constraints: criticalCA:TRUESignature Algorithm: sha256WithRSAEncryptiona4:a7:24:72:f7:f5:82:75:d1:e9:b3:9c:a1:46:e4:ca:18:85:64:d5:dd:aa:ff:b5:ca:b5:2a:ea:b0:df:77:ac:d6:bd:1f:e7:38:4c:e2:54:63:06:08:12:50:65:ad:8c:a7:1d:87:79:73:3a:a7:dc:45:35:46:12:dc:cf:65:a5:f1:9a:ad:62:65:40:3d:0c:c7:b1:7e:6c:26:3f:19:89:7f:81:d2:64:1e:b2:be:5c:d5:ff:1d:d9:e0:d8:82:b5:4e:54:81:fe:f1:98:f2:ec:80:2d:77:57:94:04:71:c6:65:3b:c2:91:45:8a:d8:d6:f5:d0:34:e5:fa:54:da:6f:46:23:18:4a:bf:05:20:e2:90:2a:dd:64:70:f1:4f:e8:60:78:4a:2f:6a:50:5a:3d:8a:46:03:2d:b4:ae:d5:d9:3d:06:83:0f:2d:82:32:fe:68:e9:68:cd:73:86:c1:e7:97:47:9c:ec:73:3e:78:59:d8:d2:23:a6:6e:f5:02:b2:2d:bd:57:98:b1:2e:e3:6d:49:8d:f8:0c:ca:bd:41:27:4c:59:54:9e:58:e4:c9:6a:61:03:bc:9d:ed:cc:8d:85:53:9a:3e:a4:d3:57:5d:9f:fe:94:fe:8d:43:ce:82:ac:49:9d:b0:7e:29:38:8f:6c:23:56:00:e9:5e:0d:1c:f9##换成正确的CN，通过
psql postgresql://client1:[e-mail]:5433/postgres?sslmode=verify-full
psql (13.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.postgres=>