【DB宝97】PG配置SSL安全连接

SSL简介

什么是SSL？

SSL的全名叫做secure socket layer(安全套接字层)，最开始是由一家叫网景的互联网公司开发出来，主要是防止信息在互联网上传输的时候不被窃听或者篡改，后来网景公司提交SSL给ISOC组织做标准化，改名为TLS。

什么是openssl?

openssl 是目前最流行的 SSL 密码库工具，其提供了一个通用、健壮、功能完备的工具套件，用以支持SSL/TLS 协议的实现。

TLS与SSL在传输层对网络连接进行加密

构成部分:

密码算法库
密钥和证书封装管理功能
SSL通信API接口

SSL双向认证和SSL单向认证的区别?

双向认证 SSL 协议要求服务器和用户双方都有证书。

单向认证 SSL 协议不需要客户拥有CA证书，服务器端不会验证客户证书，以及在协商对称密码方案，对称通话密钥时，服务器发送给客户的是没有加过密的(这并不影响 SSL 过程的安全性)密码方案。

一般Web应用都是采用SSL单向认证的，无需在通讯层对用户身份进行验证，一般都在应用逻辑层来保证用户的合法登入。但如果是企业应用对接，可能会要求对客户端(相对而言)做身份验证。这时就需要做SSL双向认证。

单向认证和双向认证的区别仅在于创建连接阶段，数据的传输均为加密的，因此客户端与PG服务端的连接采取SSL单向认证即可，即仅在PG Server端配置SSL证书。

查看postgresql是否使用openssl选项编译安装，没有则需重新编译：

代码语言：javascript复制

[pg12@lhrpgcm1 ~]$ pg_config|grep CONFIGURE
CONFIGURE = '--prefix=/home/pg12/soft/' '--with-openssl'

在启用了--with-openssl这个编译选项的情况下，ssl_library的参数值是OpenSSL。

代码语言：javascript复制

postgres=# show ssl_library ;
 ssl_library 
-------------
 OpenSSL
(1 row)

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

SSL证书使用

想要我们的pg数据库支持SSL连接，首先需要确保服务器有安装openssl：

代码语言：javascript复制

yum -y install openssl openssl-devel

接着在编译安装时指定开启ssl

代码语言：javascript复制

./configure --prefix=/home/pg12/soft --with-openssl

默认情况下PostgreSQL 读取openssl的配置文件openssl.cnf, 在openssl version -d返回的目录中。

代码语言：javascript复制

[pg12@lhrpgcm1 openssl]$ openssl version -d
OPENSSLDIR: "/etc/pki/tls"

目前不使用第三方权威机构的CA来认证，自己充当CA的角色。也即自签名证书。虽然可以使用自签名证书进行测试，但是应该在生产环境中使用由证书颁发机构(CA)(通常是企业范围的根CA)签名的证书。

PostgreSQL配置单向SSL认证连接

首先要为服务器创建一个有效期为365天的简单自签名证书，创建服务端证书和私钥文件：

代码语言：javascript复制

mkdir ~/openssl
openssl req -new -x509 -days 365 -nodes -text -subj '/CN=postgres' -out ~/openssl/server.crt -keyout ~/openssl/server.key
chmod 600 ~/openssl/server.key

接着修改postgreql.conf配置文件：

代码语言：javascript复制

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

ssl: 支持SSL连接。默认是关闭的。这个参数只能在服务器启动时设置。SSL通信只能通过TCP/IP连接进行。 ssl_cert_file:指定包含SSL服务器证书的文件的名称。默认是server.crt，相对路径相对于数据目录$PGDATA。此参数只能在服务器启动时设置。 ssl_key_file:指定包含SSL服务器私钥的文件的名称。默认是server.key，相对路径相对于数据目录。此参数只能在服务器启动时设置。

要在SSL模式下启动，必须存在包含服务器证书和私钥的文件。默认情况下，这些文件将被命名为server.crt和server.key。但是可以使用配置参数ssl_cert_file和ssl_key_file指定其他名称和位置。

在linux系统中，server.key必须禁止其他用户的访问权限。我们需要通过chown命令将server.key的访问权限设置成600。

SSL打开后，此时服务器将侦听同一TCP端口上的正常连接和SSL连接，并与任何连接客户机协商是否使用SSL。

重启数据库，然后使用-h选项进行连接数据库，并且创建扩展create extension sslinfo;：

代码语言：javascript复制

[pg12@lhrpgcm1 data]$ pg_ctl restart
waiting for server to shut down....2022-02-26 12:14:43.338 CST [21540] LOG:  received fast shutdown request
2022-02-26 12:14:43.380 CST [21540] LOG:  aborting any active transactions
2022-02-26 12:14:43.382 CST [21540] LOG:  background worker "logical replication launcher" (PID 21548) exited with exit code 1
2022-02-26 12:14:43.382 CST [21543] LOG:  shutting down
2022-02-26 12:14:43.598 CST [21540] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2022-02-26 12:14:43.684 CST [21636] LOG:  starting PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-02-26 12:14:43.684 CST [21636] LOG:  listening on IPv4 address "0.0.0.0", port 5666
2022-02-26 12:14:43.684 CST [21636] LOG:  listening on IPv6 address "::", port 5666
2022-02-26 12:14:43.764 CST [21636] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5666"
2022-02-26 12:14:43.863 CST [21637] LOG:  database system was shut down at 2022-02-26 12:14:43 CST
2022-02-26 12:14:43.894 CST [21636] LOG:  database system is ready to accept connections
 done
server started
[pg12@lhrpgcm1 ~]$ psql -h localhost -d postgres -U postgres -p 5666
psql (12.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# select ssl_is_used();    
 ssl_is_used 
-------------
 t
(1 row)

[pg12@lhrpgcm1 ~]$ psql 'host=localhost user=postgres dbname=postgres password=1qaz@WSX sslmode=require'
psql (12.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# create extension sslinfo;
CREATE EXTENSION
postgres=# 
postgres=# select ssl_is_used();
 ssl_is_used 
-------------
 t
(1 row)

-- 连接的时候需要加上-h参数，否则不是以ssl连接的
[pg12@lhrpgcm1 data]$ psql
psql (12.9)
Type "help" for help.

postgres=# select ssl_is_used();
 ssl_is_used 
-------------
 f
(1 row)

postgres=# exit
[pg12@lhrpgcm1 data]$ psql -h localhost
psql (12.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# select ssl_is_used(); 
 ssl_is_used 
-------------
 t
(1 row)

postgres=# select ssl_version();
 ssl_version 
-------------
 TLSv1.2
(1 row)

PostgreSQL配置双向SSL认证连接

双向SSL认证配置我们需要根服务器来为客户端、数据库颁发证书。

服务器端证书配置

服务器端需生成三个文件: root.crt(根证书)、server.crt(服务器证书)、server.key(服务器私钥)

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

服务器私钥生成后，需移除密码，否则数据库重启时会出现异常。

代码语言：javascript复制

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

目录根据实际情况，openssl 就是 openssl.org，genrsa 是 generate an RSA private key，des3 是加密算法。意思是生成一个 RSA 密钥，再将密钥用 des3 算法加密。

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

代码语言：javascript复制

openssl req -new -key server.key -days 3650 -out server.crt -x509 
  -subj '/C=CN/ST=Shaanxi/L=Xian/O=xmmup.com/CN=xmmup/emailAddress=lhr@xmmup.com'

Country Name (2 letter code) [AU]:CN 国家 State or Province Name (full name) [Some-State]: 省份 Locality Name (eg, city) []: 城市 Organization Name (eg, company) [Internet Widgits Pty Ltd]: 组织机构或公司 Organizational Unit Name (eg, section) []:localhost 部门 Common Name (e.g. server FQDN or YOUR name) []:localhost 域名或用户名 Email Address []:

3、根证书root.crt

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

代码语言：javascript复制

cp server.crt root.crt

服务器端数据库配置

需要配置postgresql.conf和pg_hba.conf文件！

1、postgresql.conf

postgresql的SSL配置默认是关闭的，需更改配置文件进行开启

代码语言：javascript复制

cat >> postgresql.conf <<"EOF"
ssl=on
ssl_ca_file='root.crt'
ssl_key_file='server.key'
ssl_cert_file='server.crt'
EOF

2、pg_hba.conf

还需要更改服务器的pg_hba.conf文件禁止用户以非SSL连接数据库。

另外pgsql的客户机身份验证由一个配置文件控制，该配置文件通常名为pg_hba.conf，存储在数据库的数据目录中。(HBA代表基于主机的身份验证。)，当initdb初始化数据目录时，将安装一个默认的pg_hba.conf文件。pg_hba.conf文件的一般格式是一组记录，每行一个。其中每个记录指定连接类型、客户机IP地址范围(连接类型相关)、数据库名、用户名和用于匹配这些参数的连接的身份验证方法。具有匹配的连接类型、客户端地址、请求的数据库和用户名的第一条记录用于执行身份验证。不存在“穿透”或“备份”, 如果选择了一条记录，身份验证失败，则不考虑后续记录。如果没有记录匹配，则拒绝访问。

pg_hba.conf与ssl相关的配置有两个。

hostssl: 此记录匹配使用TCP/IP进行的连接尝试，但仅在使用SSL加密进行连接时才匹配。要使用此选项，必须使用SSL支持构建服务器。此外，必须通过设置SSL配置参数在服务器启动时启用SSL。
hostnossl：此记录类型具有与hostssl相反的行为;它只匹配不使用SSL的TCP/IP上的连接尝试。

使用使用host的话优先使用ssl认证，hostssl表示强制使用ssl, hostnossl 强制不使用ssl。

代码语言：javascript复制

cat >> pg_hba.conf <<"EOF"
hostssl all all 0.0.0.0/0 cert
EOF

然后重启postgresql.

代码语言：javascript复制

[pg12@lhrpgcm1 data]$ pg_ctl restart
waiting for server to shut down....2022-03-01 10:47:58.450 CST [4378] LOG:  received fast shutdown request
2022-03-01 10:47:58.481 CST [4378] LOG:  aborting any active transactions
2022-03-01 10:47:58.482 CST [4378] LOG:  background worker "logical replication launcher" (PID 4385) exited with exit code 1
2022-03-01 10:47:58.483 CST [4380] LOG:  shutting down
2022-03-01 10:47:58.714 CST [4378] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2022-03-01 10:47:58.803 CST [4607] LOG:  starting PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-03-01 10:47:58.803 CST [4607] LOG:  listening on IPv4 address "0.0.0.0", port 5666
2022-03-01 10:47:58.804 CST [4607] LOG:  listening on IPv6 address "::", port 5666
2022-03-01 10:47:58.885 CST [4607] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5666"
2022-03-01 10:47:58.984 CST [4608] LOG:  database system was shut down at 2022-03-01 10:47:58 CST
2022-03-01 10:47:59.017 CST [4607] LOG:  database system is ready to accept connections
 done
server started

[pg12@lhrpgcm1 data]$ psql -h lhrpgcm1
2022-03-01 10:51:03.264 CST [4798] FATAL:  connection requires a valid client certificate
2022-03-01 10:51:03.267 CST [4799] FATAL:  no pg_hba.conf entry for host "192.168.0.11", user "postgres", database "postgres", SSL off
psql: error: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host "192.168.0.11", user "postgres", database "postgres", SSL off

普通连接会报错：

代码语言：javascript复制

C:Userslhrxxt>psql -U postgres -h 192.168.66.35 -p 5666
psql: error: connection to server at "192.168.66.35", port 5666 failed: FATAL:  connection requires a valid client certificate
connection to server at "192.168.66.35", port 5666 failed: FATAL:  no pg_hba.conf entry for host "192.168.66.64", user "postgres", database "postgres", SSL off

客户端配置SSL证书

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

root.crt (trusted root certificate) ：root.crt(根证书)
postgresql.crt (client certificate) ：postgresql.crt(客户端证书)
postgresql.key (private key) ：postgresql.key(客户端私钥)

postgresql.key

代码语言：javascript复制

cd $PGDATA
openssl genrsa -des3 -out postgresql.key 2048
openssl rsa -in postgresql.key -out  postgresql.key

postgresql.crt

代码语言：javascript复制

openssl req -new -key postgresql.key -out postgresql.csr 
-subj '/C=SN/ST=Shaanxi/L=Xian/O=wireless_center.cn/CN=postgres/emailAddress=lhr@xmmup.com'

openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial

Common Name (e.g. server FQDN or YOUR name) []:postgres该项必须设置为要连接postgresql数据库的用户名，否则会默认使用当前计算机的用户名，导致证书使用时，认证失败。

测试连接

psql客户端使用ssl连接

使用ssl连接命令：

代码语言：javascript复制

-- 1、在Linux环境中，需要将证书放在当前用户的.postgresql目录下
psql 'host=localhost user=postgres dbname=postgres port=5666 sslmode=require'
psql 'host=192.168.66.35 user=postgres dbname=postgres password=1qaz@WSX sslmode=require'
psql "postgresql://postgres@192.168.66.35:5666/postgres?sslmode=require"
psql "postgresql://postgres@192.168.66.35:5666/postgres?ssl=true"
psql postgresql://postgres@192.168.66.35:5666/postgres?ssl=true

-- 证书不在当前用户的.postgresql目录下，例如在/tmp目录下
psql postgresql://postgres@192.168.66.35:5666/postgres?ssl=true&sslrootcert=/tmp/root.crt&sslkey=/tmp/postgresql.key&sslcert=/tmp/postgresql.crt


-- 2、在Windows环境中，需要将证书放在%APPDATA%postgresql下

复制客户端证书到当前用户的.postgresql目录下

代码语言：javascript复制

mkdir ~/.postgresql
cp $PGDATA/root.crt ~/.postgresql/
cp $PGDATA/postgresql.crt ~/.postgresql/
cp $PGDATA/postgresql.key ~/.postgresql/
chmod 0400 ~/.postgresql/root.crt
chmod 0400 ~/.postgresql/postgresql.crt
chmod 0400 ~/.postgresql/postgresql.key

测试：

代码语言：javascript复制

[root@docker35 ~]# psql "postgresql://postgres@192.168.66.35:5666/postgres?ssl=true"
psql.bin: FATAL:  connection requires a valid client certificate

-- 添加证书之后再测试
[root@docker35 ~]# psql "postgresql://postgres@192.168.66.35:5666/postgres?ssl=true"
psql.bin (10.18, server 12.9)
WARNING: psql.bin major version 10, server major version 12.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit
postgres-# q
[root@docker35 ~]# rm -rf .postgresql/
[root@docker35 ~]# 
[root@docker35 ~]# psql 'host=localhost user=postgres dbname=postgres port=5666 sslmode=require'
psql.bin: FATAL:  connection requires a valid client certificate
[root@docker35 ~]# psql postgresql://postgres@192.168.66.35:5666/postgres?ssl=true
psql.bin: FATAL:  connection requires a valid client certificate
[root@docker35 ~]# psql postgresql://postgres@192.168.66.35:5666/postgres?ssl=true&sslrootcert=/tmp/root.crt&sslkey=/tmp/postgresql.key&sslcert=/tmp/postgresql.crt

psql.bin (10.18, server 12.9)
WARNING: psql.bin major version 10, server major version 12.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 
postgres=#

在Windows环境中，

image-20220301140120966

然后进行连接：

代码语言：javascript复制

C:Userslhrxxt>psql postgresql://postgres@192.168.66.35:5666/postgres?ssl=true
psql (14.0, server 12.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Navicat等图形界面使用ssl连接

将root.crt、postgresql.crt和postgresql.key拷贝到客户端主机上，然后使用navicat验证连接：

使用普通连接方式将无法连接：

需要使用SSL连接：

若使用pgAdmin登陆：

数据库连接SSL选项sslmode

安全等级由低到高： disable: 只尝试非SSL连接 allow：首先尝试非SSL连接，若失败再尝试SSL连接 prefer (default)：首先尝试SSL连接，若失败再尝试非SSL连接 require：只尝试SSL连接，若有根证书存在，等同于verify-ca verify-ca：只尝试SSL连接，并用根证书验证服务器证书是不是根CA签发的 verify-full：只尝试SSL连接，并用根证书验证服务器证书是不是根CA签发的，且主题必须匹配连接域名或IP地址

如 psql -Upostgres "host=xxx.xxx.xxx.xxx dbname=xxx sslmode=verify-full" 大部分客户端接口如ODBCLIBPQ都可以设置sslmode参数。

所有SSL选项都带来了加密和密钥交换的负荷，因此必须在性能和安全性之间做出平衡。下表不同sslmode值所保护的风险，以及它们是怎样看待安全性和负荷的。

表 SSL 模式描述

sslmode	窃听保护	MITM保护	声明
disable	No	No	我不关心安全性，并且我不想为加密增加负荷。
allow	可能	No	我不关心安全性，但如果服务器坚持，我将承担加密带来的负荷。
prefer	可能	No	我不关心安全性，但如果服务器支持，我希望承担加密带来的负荷。
require	Yes	No	我想要对数据加密，并且我接受因此带来的负荷。我信任该网络会保证我总是连接到想要连接的服务器。
verify-ca	Yes	Depends on CA policy	我想要对数据加密，并且我接受因此带来的负荷。我想要确保我连接到的是我信任的服务器。
verify-full	Yes	Yes	我想要对数据加密，并且我接受因此带来的负荷。我想要确保我连接到的是我信任的服务器，并且就是我指定的那一个。

verify-ca和verify-full之间的区别取决于根CA的策略。如果使用了一个公共CA，verify-ca允许连接到那些可能已经被*其他人*注册到该CA的服务器。在这种情况下，总是应该使用verify-full。如果使用了一个本地CA或者甚至是一个自签名的证书，使用verify-ca常常就可以提供足够的保护。

sslmode的默认值是prefer。如表中所示，这在安全性的角度来说没有意义，并且它只承诺可能的性能负荷。提供它作为默认值只是为了向后兼容，并且我们不推荐在安全部署中使用它。

PGCM中的SSL

PGCM考试第1题就要求我们编译安装PG 12.9，并且配置SSL，那么考试需要这么麻烦吗？

当然不是，一切尽在麦老师的PGCM课堂中，请参考：https://www.xmmup.com/pgcmkaozheng.html/2

参考

https://blog.csdn.net/baidu_38981831/article/details/104995027

http://www.postgres.cn/docs/13/preventing-server-spoofing.html

http://www.postgres.cn/docs/13/auth-pg-hba-conf.html

http://www.postgres.cn/docs/13/libpq-ssl.html

https://mp.weixin.qq.com/s/ZjDuBxskDftuWl7w6VdKeQ

网络安全 https 数据库 sql SSL证书

0 人点赞