PostgreSQL+SSL链路测试

博客介绍了PostgreSQL支持SSL的前提是打开openssl选项。详细阐述了测试过程,包括生成私钥、公钥证书,配置PG服务器,在相关配置文件中进行设置,启动数据库以实现单向认证。还说明了若要实现双向认证,需为客户端配置证书及相关操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

参考PostgreSQL加密连接SSL配置 - kahnyao - 博客园

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

测试过程。

1、生成服务器私钥server.key

cd $PGDATA
openssl genrsa -des3 -out server.key 2048
openssl rsa -in server.key -out server.key  #去掉证书的密码保护
chmod 400 server.key

2、生成服务器证书server.crt

openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CN/ST=Shaanxi/L=Xian/O=163.com/CN=geoscene.yzr.local/emailAddress=test@163.com'

3、根证书root.crt

由于没有公证机构提供,只能使用自签名证书,因此可以将服务器证书作为根证书

cp server.crt root.crt

4 在postgresql.conf中配置:

ssl = on

ssl_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. 配置客户端证书

客户端配置SSL证书

开启客户端SSL连接也需要三个文件

  • root.crt (trusted root certificate) :root.crt(根证书)
  • postgresql.crt (client certificate) :postgresql.crt(客户端证书)
  • postgresql.key (private key) :postgresql.key(客户端私钥)
cd $PGDATA
openssl genrsa -des3 -out postgresql.key 2048
openssl rsa -in postgresql.key -out postgresql.key  #去掉证书的密码保护


openssl req -new -key postgresql.key -out postgresql.csr -subj '/C=CN/ST=Shaanxi/L=Xian/O=163.com/CN=postgres/emailAddress=test@163.com'

说明:Common Name (e.g. server FQDN or YOUR name) []:postgres该项必须设置为要连接postgresql数据库的用户名,否则会默认使用当前计算机的用户名,导致证书使用时,认证失败。
openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out 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:db
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = CN, ST = Beijing, L = Chaoyang, O = YZR, CN = geoscene.yzr.local
        Validity
            Not Before: Aug 23 06:51:55 2023 GMT
            Not After : Sep 22 06:51:55 2023 GMT
        Subject: C = CN, ST = Beijing, L = Chaoyang, O = YZR, CN = geoscene.yzr.local
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA 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:43
                Exponent: 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:AE
            X509v3 Authority Key Identifier:
                keyid:55:E3:A3:6D:F2:90:6A:72:74:F1:F5:7F:B0:21:86:4E:20:BD:67:AE

            X509v3 Basic Constraints: critical
                CA:TRUE
    Signature Algorithm: sha256WithRSAEncryption
         a4: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:client1@geoscene.yzr.local: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=>

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值