环境说明

• PostgreSQL 9.4 docker容器

配置步骤

服务器端证书配置

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

生成服务器私钥

$ cd /var/lib/postgresql/data
$ openssl genrsa -des3 -out server.key 2048
Generating RSA private key, 2048 bit long modulus
...........................................................................+++++
.....+++++
e is 65537 (0x010001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:
# 此处需输入密码,并需要再次确认输入密码

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

$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key

生成服务器证书

$ openssl req -new -key server.key -days 2650 -out server.crt -x509
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
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 []:
# 此处可以一路enter，不用输入

生成根证书

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

$ cp server.crt root.crt

服务器端配置

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

$ vi /var/lib/postgresql/data/postgresql.conf
ssl=on
ssl_ca_file='root.crt'
ssl_key_file='server.key'
ssl_cert_file='server.crt'

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

$ vi /var/lib/postgresql/data/pg_hba.conf
# host all all all md5
hostssl all all 0.0.0.0/0 cert

然后重启postgresql

客户端证书配置

客户端需要三个文件: root.crt(根证书)、postgresql.crt(客户端证书)、postgresql.key(客户端私钥)

生成客户端私钥

$ cd /var/lib/postgresql/data
$ openssl genrsa -des3 -out postgresql.key 2048
Generating RSA private key, 2048 bit long modulus
...........................................................................+++++
.....+++++
e is 65537 (0x010001)
Enter pass phrase for postgresql.key:
Verifying - Enter pass phrase for postgresql.key:
# 此处需输入密码,并需要再次确认输入密码

客户端私钥生成后，可不移除密码，为简便操作，此处进行移除

$ openssl rsa -in postgresql.key -out postgresql.key
Enter pass phrase for postgresql.key:
writing RSA key

生成客户端csr文件

$ openssl req -new -key postgresql.key -out postgresql.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
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) []:
Common Name (e.g. server FQDN or YOUR name) []:postgres
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

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

生成客户端证书

$ openssl x509 -req -days 3650 -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial
Signature ok
subject=C = CN, ST = Some-State, O = Internet Widgits Pty Ltd, CN = postgres
Getting CA Private Key

测试连接

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