PostgreSQL cert client auth method configed with hostssl and user mapping

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

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

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

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

生成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文件, 因为是自签名的.

主机名使用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.

【参考】

1. http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html#EXAMPLE-PG-HBA.CONF

2. http://www.postgresql.org/docs/9.3/static/auth-username-maps.html

3. http://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-CERT

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

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

6. libpq数据库连接函数参考

http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING

7. http://www.postgresql.org/docs/9.3/static/libpq-ssl.html

8. http://www.postgresql.org/docs/devel/static/ssl-tcp.html

9. http://en.wikipedia.org/wiki/Spoofing_attack