PostgreSQL cert client auth method configed with hostssl and user mapping-阿里云开发者社区

前面介绍了PostgreSQL 服务器和客户端之间的数据传输加密方法.

前提条件是数据库和客户端服务器都必须有ssl lib库.

http://blog.163.com/digoal@126/blog/static/163877040201342233131835/

前面还介绍了md5和password客户端认证方法的区别,

http://blog.163.com/digoal@126/blog/static/1638770402013423102431541/

本文将要介绍的是客户端证书认证方法. 使用证书认证不需要客户端输入密码.

但是同样要求数据库和客户端服务器都必须有ssl lib库. 数据传输为加密传输.

19.3.10. Certificate Authentication
This authentication method uses SSL client certificates to perform authentication. It is therefore only available for SSL connections. When using this authentication method, the server will require that the client provide a valid certificate. No password prompt will be sent to the client. The cn (Common Name) attribute of the certificate will be compared to the requested database user name, and if they match the login will be allowed. User name mapping can be used to allow cn to be different from the database user name.
The following configuration options are supported for SSL certificate authentication:
map
Allows for mapping between system and database user names. See Section 19.2 for details.

[使用举例]

环境 :

CA server : 172.16.3.150 (随便一台服务器即可, 也可以是以下db server或者client server)

数据库 : 172.16.3.33
client 1 : 172.16.3.39
attacker : 172.16.3.40

1. 首先要创建CA 私钥

root@172-16-3-150:~# mkdir -p /etc/ssl/private
root@172-16-3-150:~# openssl genrsa -des3 -out /etc/ssl/private/trustly-ca.key 2048
Generating RSA private key, 2048 bit long modulus
..........................................................+++
...................................+++
e is 65537 (0x10001)
Enter pass phrase for /etc/ssl/private/trustly-ca.key: 输入digoal, 为了安全生产中使用最好输入复杂一点的.
Verifying - Enter pass phrase for /etc/ssl/private/trustly-ca.key: 再次输入digoal

# 确保私钥安全, 修改权限和所属

root@172-16-3-150:~# chown root:root /etc/ssl/private/trustly-ca.key
root@172-16-3-150:~# chmod 400 /etc/ssl/private/trustly-ca.key

# 查看一下私钥内容, 千万别泄露. 当然包括pass phrase也别泄露.

root@172-16-3-150:~# cat /etc/ssl/private/trustly-ca.key 
-----BEGIN RSA PRIVATE KEY-----
Proc-Type: 4,ENCRYPTED
DEK-Info: DES-EDE3-CBC,F48701D7A1B00B0F

zV96wfQ8/tYDb46Ft7ZotEYJ3YYwNvTc/FVkVsq+W0mKnJ5mhXvF0piQFtMkQtdq
gakxff9H+08qZxERAX+rXMA7chxZl6LKNxcfA6Rh82MFYrZAErJd5bW+m0geiefO
WVlS2dD4Mfa/vup4kcIVZ2MG/4AS3J3efWoTNl1RFx+NYx6c2Wra0h9JmnVWaDiV
7D8VAGUdJDxKh742v64+4Obf5+E/efxomMh9tQBv7Ms45O/plOeo5GOmHRqRIL+v
I35V40MHCxZKsUkY7qJxy6TPraUn4OxtDepi/dL2qKlP3+KkeFQ7RIXYnsaMqgCS
6yJrEUEx7zGnzyCczG5qXRleIA6+ww5HyTinkmSvSVYt1KPgGWe0cj89IrnSwjgi
RCtIAHG3/eYjIZz81kiK8ZnPcJLrjmTrphfIrrbYvW/s733NA9117zx3H3PaxvNE
S7hYGuUv+ahvrl1146Z0PJ6AvSxfHB0EjuvcQCyYWQg7WauLeozZywV0E5iieMqd
RE1RCu2PltCEsAi0hZik+nTWRCQ2xopltuj5EMH4GrKDOfjcxBHg5WkGCid58laE
3HO0mxnEu/ORVZqRNUu/F4LXnlwB1TFHY5Wu4OGx18VjL1MzkymWBuh1sahT+hLz
Vs0P9i45HgwCbus2J4gjHNgQ3WJstsuNoZCiyCdd9c4qCgGE1QpTDaBDC3C6jGg5
uRXpQahGgUHeWql+5m6FWDx0XD+Zv2qJM/V84c+5uRJrcFqOUnw7pNjLAy0ldJRE
N1bsExJ8GOF2pyVleWsedWAxOsIt03b4nWHRXOcda+S5X52EqCIk2GSFCiX6aUPZ
4zsmK6BGLWRbJBRfA3+L+f20uz66BGFguwErDM05XO78mpdMg6UgB8J5NJp86Rjl
C/9TstPR6zqf6ZYbnPJ9nM1YB45MJLmiptbpZ45+d7W/C8BFf0jdnQCM/bRQY8N9
5HAxOIMWoqqOHUa4fFv0skx438rcQ903SbinOtHocqFXnOzukYIBk83mfdLBaYAH
J4L7AezH6cBCl8MU78P6VnfHgZtTtAp5YzAwxtayDa89nMGoGs4f3njyT04xjX1S
IUF8jHQUnoHkBFtKnlrCyMXVH3sYoc8kjvNDahGCPDNVAt51vJyQ/EpLLcQcd+A9
zazpVh5pRYlXs9Y49aKAxcRuQDXcTmSJnPY2EdQERU9o81IJ+NBmrCqvmcVqmVXM
3JajGZWKaOi95J1WFS6fTeL3jHUlVzblnDwXeEWi9VbOTtQt4QljUqYq28+UGrkk
NVLtNVnQ+tzGMXVEmrOQ2eHRU/gxUO7Eq/dsE39ZDxQmh0aP0Xb6QcvFX+gKr73Y
aTCzvaugxDKYhhSvM3ixyxUcQoS2bV56djHBaAB3kfDOePIjEW97aOL1cRo7DAE7
7jTFydZnwvJXIW7tH/l7Uhj124Jm69ChRgb4J+5lDh5Y+oVnm9mkv9v4UGgGryxH
6mqniDL88aZ1UmeWVXeXr2U7iVtLxDEZagJHRcoX1igmWVP9lQepqtt8eiMAwH36
Go20l9jgFzDoMX4ElRuiISYFoNoBspRdtMflgN2SOkwegt+Y6qJ5iw==
-----END RSA PRIVATE KEY-----

# 文件属性

root@172-16-3-150:~# file /etc/ssl/private/trustly-ca.key 
/etc/ssl/private/trustly-ca.key: PEM RSA private key

2. 生成公共证书

root@172-16-3-150:~# mkdir -p /usr/local/share/ca-certificates
root@172-16-3-150:~# openssl req -new -x509 -days 3650 \
> -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=trustly' \
> -key /etc/ssl/private/trustly-ca.key \
> -out /usr/local/share/ca-certificates/trustly-ca.crt
Enter pass phrase for /etc/ssl/private/trustly-ca.key: 输入私钥的pass phrase: digoal

# 查看公共证书内容 :

root@172-16-3-150:~# cat /usr/local/share/ca-certificates/trustly-ca.crt 
-----BEGIN CERTIFICATE-----
MIIDgTCCAmmgAwIBAgIJAIKNqfyd2XnOMA0GCSqGSIb3DQEBBQUAMFcxCzAJBgNV
BAYTAkNOMREwDwYDVQQIDAhaaGVqaWFuZzERMA8GA1UEBwwISGFuZ3pob3UxEDAO
BgNVBAoMB1NreW1vYmkxEDAOBgNVBAMMB3RydXN0bHkwHhcNMTMwNTI0MDUyNDUy
WhcNMjMwNTIyMDUyNDUyWjBXMQswCQYDVQQGEwJDTjERMA8GA1UECAwIWmhlamlh
bmcxETAPBgNVBAcMCEhhbmd6aG91MRAwDgYDVQQKDAdTa3ltb2JpMRAwDgYDVQQD
DAd0cnVzdGx5MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsdCXygi7
jx/Vs0KQHiPNCZxGsTkPTfWTjuDvDft7BVkRIdow+7LtFmdCoz5+nqfRRWUZPOJF
6Eqc39DAqaWb0YzGYMWPlj3NJKwMwcw5FW5X7USr5SVWTmgf6IFxHsTuYptzWKDy
LDE8Jkjm9RHRppkHpRaBdgWphQ8m0B4YEEJx0HzpPdAsd4YLuzHeswpmHenSO9eF
wRaQRPG+cGMilaShSetVz904655t+WaJubzcur7AJPH3bcxCSlmApGcee4ydcm5j
JAHXbQ26pPCe481MLYPQCHFatY2220PP2gre4g/4wQWd03tc3OU9AbdL+3wUuK3X
eTwOfNcItj5KXQIDAQABo1AwTjAdBgNVHQ4EFgQUBK6vtbH8gu71m6rpW/iBnvZ2
MuMwHwYDVR0jBBgwFoAUBK6vtbH8gu71m6rpW/iBnvZ2MuMwDAYDVR0TBAUwAwEB
/zANBgkqhkiG9w0BAQUFAAOCAQEAEzMBp3Zn2TD3LW5S1lSoJ32G3FO8Auil71K8
1K4BhedCj0x9Q1a3EGm4ve/cSiLSiSsO54g/+Cq81d5mxf4HHlHB8vZeFTQ73bwF
xrNw1cCEHK166U7T+8A8VjesKGUGsjbFCcju/okLrjHBKxQBYkWSjOqpP39Ero9D
3DcaLcvidUyh4cEOb/QQ5lhKZmLX78uwryfyk/nT70r3HMf9LlKCebQtTRbybtRZ
rk2n+12o7IGEx5/ezV4ucAlfycuFVyGVqj7ZpyaStm7oOux1azuowJBSWoTH6PC9
YKG0dB1ymkSYXZU7GIGDLyKOUHhxnV1kVINtPXEhxnnWohgB4A==
-----END CERTIFICATE-----

# 文件属性

root@172-16-3-150:~# file /usr/local/share/ca-certificates/trustly-ca.crt
/usr/local/share/ca-certificates/trustly-ca.crt: PEM certificate

3. 配置PostgreSQL server

生成postgresql server 私钥

[root@db-172-16-3-33 ca-certificates]# su - pg93
pg93@db-172-16-3-33-> openssl genrsa -des3 -out $PGDATA/server.key 2048
Generating RSA private key, 2048 bit long modulus
....................................................................................+++
.....................+++
e is 65537 (0x10001)
Enter pass phrase for /pgdata1999/server.key: 输入server key pass phrase, 这里随便输入server, 你随意.
Verifying - Enter pass phrase for /pgdata1999/server.key: 再次输入server key pass phrase, 这里随便输入server, 你随意.

移除pass phrase, 为了方便做自启动脚本, 不然的话数据库启动时又要提示你输入pass phrase.

pg93@db-172-16-3-33-> openssl rsa -in $PGDATA/server.key -out $PGDATA/server.key
Enter pass phrase for /pgdata1999/server.key: 输入server
writing RSA key

修改文件权限 :

pg93@db-172-16-3-33-> chmod 400 $PGDATA/server.key

# 查看key的内容和属性

pg93@db-172-16-3-33-> cat server.key 
-----BEGIN RSA PRIVATE KEY-----
MIIEpQIBAAKCAQEA1022O0sclC+s/n3olmgew4FmtdAz0gBFwnLh+TGsna/4uHHA
ZX5APXjG8W+8WhfE2UNQsj7difJuAGrm+BQc11lqZD7lQdaFIdUVKXKsZl6VycBQ
aE3OK5q6IHjpewCYLBfiHA3t5l/9w7tPmzgwBdqQj2GEZgYsBoTJglNpkKFXXLBx
3D51LhA4tN5+wsfJ54RC0JX9/JT3L3amkE8t1XOY7vVkm3ZV6sg7MnckLYdvJnv1
9Lt/cPRx4HdJjLAm3PFzyk2DpuQhCZ/K0IP2fCJNXDxPUqiUzXjjqslUUsvIKsOu
wP2dWvj+P2MJd41sEFgtlcztVvnhFlZs44BaNQIDAQABAoIBAQC6ff7OJ0aO+Sjw
m6Eevvt+vxR3geuRCFlkj9w63JM3V1iqcyWDBDOiy34PXYiZxSLmSk+YxalhttMf
m3mLAujkg/gK8wvj1mwlHQwihcjdNyqpweIkJtjhnjmArRsYRzCIaPua71nVBeqq
YxIWUjoOp/41o/Np6Ai0cMqXD6dN9UwCeFx083eDKJh3CeyWnV+aiLRcUFonTq/V
iuJw0uCGA+2tdY7UR23dDa+/R1C3CNM27vcCWa7M0yxyYL6aY9ClOtwaiRpRen4+
B5nV4MAZKTWdOPDS2viXzNWrQ98bxRCErzEb+kaRjI22d21EnnjZl0GHCtOwzTMM
w7MZkr+BAoGBAPBI95Q33MZ5PzCKXwzD8JDjuMNKEsFXlyt5SQ0jyFVnfn7vXztH
L0dZFxxo/48K2RJriQ1RmIsHHrWPcAJWg40JH2XbVOIzH0mfvRdKGbBxH6tWl59S
8Qt3hou3mVUx+3wjDwXOoPYKXVKYCh+jIpr79nFFS6BVA15iZmyLrgGhAoGBAOVi
fHkEQLlHVcDB1NHDxfjmwFUrrQH+TSyXd/WOwhZZAOVNHZlM47sf6R4dfalfjfor
DcJwYbXkyC2nxoKvN1e+HP1vc1gAL+bwImfGK4tX6FxRmf/X6BS51XBjKhr0VF7D
rcLXJ0UuT8IZI5VU/k/nkZwRrMQhz4ea5FgvfzgVAoGBAJGATdNF5H2WzAnTsGzl
dZX3H1m6UBMdvB+KKQ843MXCjtnEj5EwsNNugk0k06PFuN6rmWkkQM/nNtRQkE4K
H0zW+llOcF8s/8QwY2tn5phuV/QD0nqa2fXMof+W5NWvF477F8y3a8axTgOGp5Ky
0XCyJHBAuuPStuB/i3AtQOghAoGAHm7losyspahQOUW+LaJyxqYeyG4GAyixJoRm
Fv88wuhGFSYZEjjAUhhWvncdL/aMiK9joPN2E0LqSBxlWvtSNWL0x68ct4U21cXw
WqJRLqiYHH97FhWYJf/N0J5nfLID65q8mAghnq0ZSeA591sSbpmDmRhDOrZdqVkk
iIqsr00CgYEArUcp6qV89nNFQG01Q0hMc/rY9g8Zi3/fiJmisg66aRR51Yq/4FVM
YoTHlz9bwyRlMoO1orWgNW1fsSiCtW1YI6e4IA4bnmtjU8Qt+XRrn7YRN/BAmrD7
IeCmUcqw/B7v9K44h/yE3Wu9gOCSYo50AKVCceovmZT1Op0TZUXQiCg=
-----END RSA PRIVATE KEY-----
pg93@db-172-16-3-33-> file server.key 
server.key: ASCII text

因为除了CA服务器是Ubuntu 12.04其他都是CentOS 5.x, 所以文件属性并没有显示为key文件. 不过没有关系, 不影响使用.

4. 请求签名

pg93@db-172-16-3-33-> openssl req -new -nodes -key $PGDATA/server.key -days 3650 -out /tmp/server.csr \
> -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=pgserver'

5. 使用CA 私钥进行签名

将 /tmp/server.csr 拷贝到CA服务器进行签名

root@172-16-3-150:~# cd /tmp
root@172-16-3-150:/tmp# rz

签名

root@172-16-3-150:/tmp# openssl req -x509 \
> -key /etc/ssl/private/trustly-ca.key \
> -in /tmp/server.csr \
> -out /tmp/server.crt
Enter pass phrase for /etc/ssl/private/trustly-ca.key: 输入CA私钥pass phrase

# 查看一下这两个文件的内容和属性

root@172-16-3-150:/tmp# cat server.csr 
-----BEGIN CERTIFICATE REQUEST-----
MIICnTCCAYUCAQAwWDELMAkGA1UEBhMCQ04xETAPBgNVBAgTCFpoZWppYW5nMREw
DwYDVQQHEwhIYW5nemhvdTEQMA4GA1UEChMHU2t5bW9iaTERMA8GA1UEAxMIcGdz
ZXJ2ZXIwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDXTbY7SxyUL6z+
feiWaB7DgWa10DPSAEXCcuH5Maydr/i4ccBlfkA9eMbxb7xaF8TZQ1CyPt2J8m4A
aub4FBzXWWpkPuVB1oUh1RUpcqxmXpXJwFBoTc4rmrogeOl7AJgsF+IcDe3mX/3D
u0+bODAF2pCPYYRmBiwGhMmCU2mQoVdcsHHcPnUuEDi03n7Cx8nnhELQlf38lPcv
dqaQTy3Vc5ju9WSbdlXqyDsydyQth28me/X0u39w9HHgd0mMsCbc8XPKTYOm5CEJ
n8rQg/Z8Ik1cPE9SqJTNeOOqyVRSy8gqw67A/Z1a+P4/Ywl3jWwQWC2VzO1W+eEW
VmzjgFo1AgMBAAGgADANBgkqhkiG9w0BAQUFAAOCAQEAXUZo2PQ1qEHwEFNDQhdO
zYKp0ex9x36bHlvt/JMRqIHAKMQBUwsR+kuxpipRiTzQTG/hnZyv5qIf8Jr/gA6K
qVlSE6Pwox8wLJ3kWAaqBmxCVi2MhubaIhaKZBx1U4v6sBZD7aP6jJsbHHqyYhHz
yaUa3NjYvWylpkFXgwJ58XyfdebUZxiYmgXSiZj8ZcC9a2sURxzUY+66DmHBIYKi
Lqx7mGx9aNvydV6S5cVnmbTxuMs1i36Y5235xh8ReMYo4+xJaydxFxgbRcvD1xxq
nIjvVBUfuXb0ByyFXzmMU6K4JnOTRtNYPWe9TdAMcqqPuoGd0Uuobn+UrfADiPbc
AQ==
-----END CERTIFICATE REQUEST-----
root@172-16-3-150:/tmp# cat server.crt
-----BEGIN CERTIFICATE-----
MIIDgzCCAmugAwIBAgIJAMDY6BIoiymHMA0GCSqGSIb3DQEBBQUAMFgxCzAJBgNV
BAYTAkNOMREwDwYDVQQIEwhaaGVqaWFuZzERMA8GA1UEBxMISGFuZ3pob3UxEDAO
BgNVBAoTB1NreW1vYmkxETAPBgNVBAMTCHBnc2VydmVyMB4XDTEzMDUyNDA1Mzcw
OFoXDTEzMDYyMzA1MzcwOFowWDELMAkGA1UEBhMCQ04xETAPBgNVBAgTCFpoZWpp
YW5nMREwDwYDVQQHEwhIYW5nemhvdTEQMA4GA1UEChMHU2t5bW9iaTERMA8GA1UE
AxMIcGdzZXJ2ZXIwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDXTbY7
SxyUL6z+feiWaB7DgWa10DPSAEXCcuH5Maydr/i4ccBlfkA9eMbxb7xaF8TZQ1Cy
Pt2J8m4Aaub4FBzXWWpkPuVB1oUh1RUpcqxmXpXJwFBoTc4rmrogeOl7AJgsF+Ic
De3mX/3Du0+bODAF2pCPYYRmBiwGhMmCU2mQoVdcsHHcPnUuEDi03n7Cx8nnhELQ
lf38lPcvdqaQTy3Vc5ju9WSbdlXqyDsydyQth28me/X0u39w9HHgd0mMsCbc8XPK
TYOm5CEJn8rQg/Z8Ik1cPE9SqJTNeOOqyVRSy8gqw67A/Z1a+P4/Ywl3jWwQWC2V
zO1W+eEWVmzjgFo1AgMBAAGjUDBOMB0GA1UdDgQWBBQTRK0bZjIFkFfP2m2U7E2R
qME3+TAfBgNVHSMEGDAWgBQTRK0bZjIFkFfP2m2U7E2RqME3+TAMBgNVHRMEBTAD
AQH/MA0GCSqGSIb3DQEBBQUAA4IBAQAOAZKm1vmMaR2M6ZHB9U7RJnJSoQdcJUXz
ckNHzgYhZwSYHaNvuvNILeoO0qsJxD2i9kDjBc+7MOFAhTaoFf5lfqQLKucKi+xh
J82gWX4kOs3t0ECE9IqeWgRx8AraE1pdlyxu1XJZjVOCT1m4TO0aRbD9nzXiBnob
x8oJmywRM1YRNtP6ETbxqoz2ntSRdpyp0jB2ApYYDqTdGewYgmQ8eT+lLR6XRC7l
wCTGXz6sf371xQeq+5Ble1S9In4Sf1mbEBUwevFNsmY7e7b+58ATZziXECBjr0Hz
baIjr5Clknf5+jb2Ab/zVaI2dRd5iNWn0xaUVjrtAHftIr5Qf5q+
-----END CERTIFICATE-----
root@172-16-3-150:/tmp# file server.crt 
server.crt: PEM certificate
root@172-16-3-150:/tmp# file server.csr 
server.csr: PEM certificate request

将签名后的server.crt拷贝到PostgreSQL 数据库$PGDATA目录.

root@172-16-3-150:/tmp# sz server.crt
pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> rz

修改server.crt属性

pg93@db-172-16-3-33-> chown pg93:pg93 $PGDATA/server.crt

6. 在PostgreSQL 服务器上创建根证书. 其实就是合成CA公用证书和签名后的pgserver证书 :

pg93@db-172-16-3-33-> cp server.crt root.crt

把CA服务器上的/usr/local/share/ca-certificates/trustly-ca.crt内容添加到root.crt

合成后的root.crt内容如下 :

pg93@db-172-16-3-33-> cat root.crt 
-----BEGIN CERTIFICATE-----
MIIDgzCCAmugAwIBAgIJAMDY6BIoiymHMA0GCSqGSIb3DQEBBQUAMFgxCzAJBgNV
BAYTAkNOMREwDwYDVQQIEwhaaGVqaWFuZzERMA8GA1UEBxMISGFuZ3pob3UxEDAO
BgNVBAoTB1NreW1vYmkxETAPBgNVBAMTCHBnc2VydmVyMB4XDTEzMDUyNDA1Mzcw
OFoXDTEzMDYyMzA1MzcwOFowWDELMAkGA1UEBhMCQ04xETAPBgNVBAgTCFpoZWpp
YW5nMREwDwYDVQQHEwhIYW5nemhvdTEQMA4GA1UEChMHU2t5bW9iaTERMA8GA1UE
AxMIcGdzZXJ2ZXIwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDXTbY7
SxyUL6z+feiWaB7DgWa10DPSAEXCcuH5Maydr/i4ccBlfkA9eMbxb7xaF8TZQ1Cy
Pt2J8m4Aaub4FBzXWWpkPuVB1oUh1RUpcqxmXpXJwFBoTc4rmrogeOl7AJgsF+Ic
De3mX/3Du0+bODAF2pCPYYRmBiwGhMmCU2mQoVdcsHHcPnUuEDi03n7Cx8nnhELQ
lf38lPcvdqaQTy3Vc5ju9WSbdlXqyDsydyQth28me/X0u39w9HHgd0mMsCbc8XPK
TYOm5CEJn8rQg/Z8Ik1cPE9SqJTNeOOqyVRSy8gqw67A/Z1a+P4/Ywl3jWwQWC2V
zO1W+eEWVmzjgFo1AgMBAAGjUDBOMB0GA1UdDgQWBBQTRK0bZjIFkFfP2m2U7E2R
qME3+TAfBgNVHSMEGDAWgBQTRK0bZjIFkFfP2m2U7E2RqME3+TAMBgNVHRMEBTAD
AQH/MA0GCSqGSIb3DQEBBQUAA4IBAQAOAZKm1vmMaR2M6ZHB9U7RJnJSoQdcJUXz
ckNHzgYhZwSYHaNvuvNILeoO0qsJxD2i9kDjBc+7MOFAhTaoFf5lfqQLKucKi+xh
J82gWX4kOs3t0ECE9IqeWgRx8AraE1pdlyxu1XJZjVOCT1m4TO0aRbD9nzXiBnob
x8oJmywRM1YRNtP6ETbxqoz2ntSRdpyp0jB2ApYYDqTdGewYgmQ8eT+lLR6XRC7l
wCTGXz6sf371xQeq+5Ble1S9In4Sf1mbEBUwevFNsmY7e7b+58ATZziXECBjr0Hz
baIjr5Clknf5+jb2Ab/zVaI2dRd5iNWn0xaUVjrtAHftIr5Qf5q+
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
MIIDgTCCAmmgAwIBAgIJAIKNqfyd2XnOMA0GCSqGSIb3DQEBBQUAMFcxCzAJBgNV
BAYTAkNOMREwDwYDVQQIDAhaaGVqaWFuZzERMA8GA1UEBwwISGFuZ3pob3UxEDAO
BgNVBAoMB1NreW1vYmkxEDAOBgNVBAMMB3RydXN0bHkwHhcNMTMwNTI0MDUyNDUy
WhcNMjMwNTIyMDUyNDUyWjBXMQswCQYDVQQGEwJDTjERMA8GA1UECAwIWmhlamlh
bmcxETAPBgNVBAcMCEhhbmd6aG91MRAwDgYDVQQKDAdTa3ltb2JpMRAwDgYDVQQD
DAd0cnVzdGx5MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsdCXygi7
jx/Vs0KQHiPNCZxGsTkPTfWTjuDvDft7BVkRIdow+7LtFmdCoz5+nqfRRWUZPOJF
6Eqc39DAqaWb0YzGYMWPlj3NJKwMwcw5FW5X7USr5SVWTmgf6IFxHsTuYptzWKDy
LDE8Jkjm9RHRppkHpRaBdgWphQ8m0B4YEEJx0HzpPdAsd4YLuzHeswpmHenSO9eF
wRaQRPG+cGMilaShSetVz904655t+WaJubzcur7AJPH3bcxCSlmApGcee4ydcm5j
JAHXbQ26pPCe481MLYPQCHFatY2220PP2gre4g/4wQWd03tc3OU9AbdL+3wUuK3X
eTwOfNcItj5KXQIDAQABo1AwTjAdBgNVHQ4EFgQUBK6vtbH8gu71m6rpW/iBnvZ2
MuMwHwYDVR0jBBgwFoAUBK6vtbH8gu71m6rpW/iBnvZ2MuMwDAYDVR0TBAUwAwEB
/zANBgkqhkiG9w0BAQUFAAOCAQEAEzMBp3Zn2TD3LW5S1lSoJ32G3FO8Auil71K8
1K4BhedCj0x9Q1a3EGm4ve/cSiLSiSsO54g/+Cq81d5mxf4HHlHB8vZeFTQ73bwF
xrNw1cCEHK166U7T+8A8VjesKGUGsjbFCcju/okLrjHBKxQBYkWSjOqpP39Ero9D
3DcaLcvidUyh4cEOb/QQ5lhKZmLX78uwryfyk/nT70r3HMf9LlKCebQtTRbybtRZ
rk2n+12o7IGEx5/ezV4ucAlfycuFVyGVqj7ZpyaStm7oOux1azuowJBSWoTH6PC9
YKG0dB1ymkSYXZU7GIGDLyKOUHhxnV1kVINtPXEhxnnWohgB4A==
-----END CERTIFICATE-----

7. 为了管理方便, 可以为cert认证的用户添加一个组, 需要cert认证的用户都添加到这个组里面.

pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# create group sslcertgroup;
CREATE ROLE
将用户digoal加入这个组
digoal=# alter group sslcertgroup add user digoal;
ALTER ROLE

8. 配置pg_hba.conf, 允许client 1连接.

pg93@db-172-16-3-33-> cd $PGDATA
pg93@db-172-16-3-33-> vi pg_hba.conf 
# 允许sslcertgroup这个组使用cert认证方式从172.16.3.39连接所有数据库.
hostssl all +sslcertgroup 172.16.3.39/32 cert clientcert=1

9. 配置postgresql.conf 打开ssl

pg93@db-172-16-3-33-> vi $PGDATA/postgresql.conf
ssl = on                                # (change requires restart)
ssl_ciphers = 'RC4-SHA:DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'        # allowed SSL ciphers
                                        # (change requires restart)
ssl_renegotiation_limit = 512MB # amount of data between renegotiations
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'root.crt'                        # (change requires restart)

重启数据库, 如果没有修改以上配置则不需要重启数据库, reload即可.

pg_ctl restart -m fast

10. 配置需要使用cert认证连接数据库的客户端, 需要用到固定格式的目录和文件如下 :

~/.postgresql
~/.postgresql/postgresql.key
~/.postgresql/postgresql.crt

具体操作如下 :

[root@db-172-16-3-39 ~]# su - pg92
pg92@db-172-16-3-39-> mkdir ~/.postgresql

为了确保安全, 建议修改~/.postgresql权限为700

pg92@db-172-16-3-39-> chmod 700 .postgresql

生成client key.

pg92@db-172-16-3-39-> openssl genrsa -des3 -out ~/.postgresql/postgresql.key 1024
Enter pass phrase for /home/pg92/.postgresql/postgresql.key: 输入Client-3.39
Verifying - Enter pass phrase for /home/pg92/.postgresql/postgresql.key: 再次输入Client-3.39

如果不想每次新建连接都提示输入pass phrase, 可以删除pass phrase, 但是你要知道这样降低了安全性 :

pg92@db-172-16-3-39-> openssl rsa -in ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.key
Enter pass phrase for /home/pg92/.postgresql/postgresql.key: 输入Client-3.39
writing RSA key

生成请求CA签名的文件

pg92@db-172-16-3-39-> openssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr \
> -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=client1'

11. 给客户端证书签名

将客户端的 ~/.postgresql/postgresql.csr拷贝到CA服务器进行签名.

pg92@db-172-16-3-39-> sz postgresql.csr
root@172-16-3-150:/tmp# cd /tmp
root@172-16-3-150:/tmp# rz
root@172-16-3-150:/tmp# openssl x509 -req -in /tmp/postgresql.csr \
> -CA /usr/local/share/ca-certificates/trustly-ca.crt \
> -CAkey /etc/ssl/private/trustly-ca.key \
> -out /tmp/postgresql.crt \
> -CAcreateserial
Signature ok
subject=/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=client1
Getting CA Private Key
Enter pass phrase for /etc/ssl/private/trustly-ca.key: 输入pass phrase : digoal

查看生成的证书 :

root@172-16-3-150:/tmp# file postgresql.crt 
postgresql.crt: PEM certificate
root@172-16-3-150:/tmp# cat postgresql.crt 
-----BEGIN CERTIFICATE-----
MIICpjCCAY4CCQC1fNg1fevU+zANBgkqhkiG9w0BAQUFADBXMQswCQYDVQQGEwJD
TjERMA8GA1UECAwIWmhlamlhbmcxETAPBgNVBAcMCEhhbmd6aG91MRAwDgYDVQQK
DAdTa3ltb2JpMRAwDgYDVQQDDAd0cnVzdGx5MB4XDTEzMDUyNDA2MDU1OFoXDTEz
MDYyMzA2MDU1OFowVzELMAkGA1UEBhMCQ04xETAPBgNVBAgTCFpoZWppYW5nMREw
DwYDVQQHEwhIYW5nemhvdTEQMA4GA1UEChMHU2t5bW9iaTEQMA4GA1UEAxMHY2xp
ZW50MTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAt9nljMDVCkGYFwc/dPTC
BSqEyUtPFnRIH/Ce4TPdp5/ch5jvQgQsi1dLpzmVAJLoFfhBZlMsdQKWhbZoIgVH
supSUiUCUbuaSf/A6XtezTvFNmCCKF7VGzjkW1LZhAmq4RrFq1q+0kcDJD9tw4mW
BKTr0qZczEPnq99QJCZLNZMCAwEAATANBgkqhkiG9w0BAQUFAAOCAQEAc9u3jnnj
vQTF2C+8jK7dGkuWPYGlg9Qned/aFfGBL43NM2E65Yr6IldaEfYTUK5ydz0IXY0B
Pk4qpR3bCww+CNKpX30/UCgHzW7CjHu5COR9ruhv1juUY9eQgFQnTC7ppCWIJt7d
c4QdciTrTR5zK7p+Tx8vFhk1FfP65IxK3Ag2CR6/KEze4Qf5KGTPvxOWnJaW9dyN
RZKMB0DnXuHGRp5mIQDBz7ZKVoC6FrslLxDrsU89P+9jiU+nW5RP+VMkDgx9ArID
F2QYtQ0VxShA8f9d3gLmW/XAkeQD39eqt2mayS8IAKrOtlS9hjljm7ipsZmPQ8gZ
jyLM9rdT7uWVqA==
-----END CERTIFICATE-----

将证书内容写入客户端的~/.postgresql/postgresql.crt即可

pg92@db-172-16-3-39-> vi ~/.postgresql/postgresql.crt

写入以上内容.

修改key文件权限 :

pg92@db-172-16-3-39-> chmod 400 postgresql.key

12. 连接数据库

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U digoal digoal
psql: FATAL:  certificate authentication failed for user "digoal"
FATAL:  no pg_hba.conf entry for host "172.16.3.39", user "digoal", database "digoal", SSL off

这是为什么呢?

digoal用户应该是可以连接的啊.

原因是创建的客户端证书, 注意11步生成请求CA签名的文件.

pg92@db-172-16-3-39-> openssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr \
> -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=Skymobi/CN=client1'

CN=client1. 这个指和数据库用户client1匹配.

在数据库中创建client1用户即可

pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# create role client1 login encrypted password 'client1';
CREATE ROLE

再次连接 :

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U client1 digoal
psql: FATAL:  no pg_hba.conf entry for host "172.16.3.39", user "client1", database "digoal", SSL on
FATAL:  no pg_hba.conf entry for host "172.16.3.39", user "client1", database "digoal", SSL off

因为还没有加到sslcertgroup组, 添加后再连接即可.

pg93@db-172-16-3-33-> psql
psql (9.3devel)
Type "help" for help.
digoal=# alter group sslcertgroup add user client1 ;
ALTER ROLE

再次连接, 正常 :

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U client1 digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
digoal=>

这就带来一个问题, 如果172.16.3.39需要使用多个用户连接数据库时怎么办呢?

例如要使用digoal用户登录数据库.

那么又需要到CA服务器签名, 然后将证书的内容合并到~/.postgresql/postgresql.crt.

或者使用postgresql支持的user mapping.

配置方法如下 :

pg93@db-172-16-3-33-> vi $PGDATA/pg_ident.conf
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
m1 client1 client1
m1 client1 digoal
pg93@db-172-16-3-33-> vi $PGDATA/pg_hba.conf
hostssl all +sslcertgroup 172.16.3.39/32 cert clientcert=1,map=m1

# reload配置

pg93@db-172-16-3-33-> pg_ctl reload
server signaled

接下来客户端就可以用digoal和client1用户登录了. 当然前提是他们在sslcertgroup组里面.

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U client1 digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
digoal=> \q

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U digoal digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
digoal=> \q

其他用户因为没有配置user mapping, 所以无法登陆. 例如想使用postgres用户登录.

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres digoal
psql: FATAL:  certificate authentication failed for user "postgres"
FATAL:  no pg_hba.conf entry for host "172.16.3.39", user "postgres", database "digoal", SSL off

想登录的话, 配置好即可.

1. 配置group, 即使是超级用户也需要配置到组里面, 例如这里的postgres用户.

digoal=# alter group sslcertgroup add user postgres ;
ALTER ROLE

2. 配置pg_ident.conf, 加一条 :

m1 client1 postgres

3. 检查pg_hba.conf有这个map配置.

hostssl all +sslcertgroup 172.16.3.39/32 cert clientcert=1,map=m1

4. 连接, 正常

pg92@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres digoal
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
digoal=# \q

[其他]

1. 如果key和证书同时泄露是非常危险的, 就相当于泄露了密码一样.

假设172.16.3.40获得了172.16.3.39上的postgresql.key和postgresql.crt

那么就可以用来连接数据库了.

pg92@db-172-16-3-40-> psql -h 172.16.3.33 -p 1999 -U digoal postgres
psql: FATAL:  no pg_hba.conf entry for host "172.16.3.40", user "digoal", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "172.16.3.40", user "digoal", database "postgres", SSL off

在172.16.3.39上开端口代理即可.

[root@db-172-16-3-39 ~]# balance 20000 172.16.3.33:1999

连接代理端口 :

pg92@db-172-16-3-40-> psql -h 172.16.3.39 -p 20000 -U digoal postgres
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
postgres=>

又或者你的pg_hba.conf中本来就开放了172.16.3.0/24网段, 那就可以直接连接了. 不需要端口代理.

pg93@db-172-16-3-33-> vi pg_hba.conf
hostssl all +sslcertgroup 172.16.3.0/24 cert clientcert=1,map=m1
pg93@db-172-16-3-33-> pg_ctl reload
server signaled

从攻击机直连 :

pg92@db-172-16-3-40-> psql -h 172.16.3.33 -p 1999 -U digoal postgres
psql (9.2beta1, server 9.3devel)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: RC4-SHA, bits: 128)
Type "help" for help.
postgres=>

所以为了安全, 请务必配置好pg_hba.conf, 以及保护好密码和密钥.

2. 在windows中使用pgadmin以及ssl模式连接数据库.

把客户端生成的postgresql.crt, postgresql.key, 以及服务端的root.crt文件拷贝到windows中.

然后配置连接, 如图 :

最高验证级别为sslmode=verify-full, 会去匹配连接主机和root.crt中配置的CN=pgserver. 所以需要编辑本地的hosts文件, 直接连IP的话是不允许的. 如果使用其他的sslmode则不需要匹配CN.

这里不需要配置crl文件, 因为是自签名的.

PostgreSQL cert client auth method configed with hostssl and user mapping - 德哥@Digoal - PostgreSQL

主机名使用root.crt中配置的CN=pgserver

编辑hosts, 添加一行 :

172.16.3.150 pgserver

然后就可以连接了.

3. sslmode相关的模式和可以避免的攻击方式

sslmode	Eavesdropping protection	MITM protection	Statement
disable	No	No	I don't care about security, and I don't want to pay the overhead of encryption.
allow	Maybe	No	I don't care about security, but I will pay the overhead of encryption if the server insists on it.
prefer	Maybe	No	I don't care about encryption, but I wish to pay the overhead of encryption if the server supports it.
require	Yes	No	I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want.
verify-ca	Yes	Depends on CA-policy	I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust.
verify-full	Yes	Yes	I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it's the one I specify.