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

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

时间： 2024-07-29 06:15:54

PostgreSQL cert client auth method configed with hostssl and user mapping的相关文章

PostgreSQL md5 auth method introduce, with random salt protect

在上一篇BLOG中介绍了不要在pg_hba.conf中使用password认证方法, 除非你的客户端和数据库服务器之间的网络是绝对安全的. http://blog.163.com/digoal@126/blog/static/1638770402013423102431541/ MD5方法,认证过程 : Encrypting Passwords Across A Network The MD5 authentication method double-encrypts the password

PostgreSQL disable pg_hba.conf trust auth method

在PostgreSQL中有一项认证为trust认证, 使用该认证方法不需要输入密码即可登录数据库. 也就是说数据库服务器管理员只要有操作系统root权限, 很容易就可以登录数据库查询数据. 在比较高的安全场合, 可能需要禁止掉trust方法. 这样的话即使配置了trust认证方法也需要提供密码. 方法很简单, 只要修改一下hba.c即可. 如下. vi src/backend/libpq/hba.c if (strcmp(token->string, "trust") == 0)

PostgreSQL 11 preview - compress method 接口 - 暨开放接口系列

标签 PostgreSQL , 开放接口 , udf , type , index , scan , language , fdw , sample , aggregate , operator , window , storage , compress 背景 PostgreSQL是一款扩展能力极强的数据库,这也是PG可以深入各个垂直行业的原因. PostgreSQL的开放式接口 已有非常多跟进自定义接口扩展出来的插件. 下面是自定义接口对应的文档. 1.自定义函数 https://www.po

一天学会PostgreSQL应用开发与管理 - 8 PostgreSQL 管理

本章大纲 一.权限体系 1 逻辑结构 2 权限体系 3 schema使用 , 特别注意 4 用户 5 public 6 如何查看和解读一个对象的当前权限状态 二.索引介绍 1 索引有什么用? 2 索引的类型 3 索引合并扫描 4 表膨胀检查 5 检查膨胀 6 索引维护 三.系统配置 1 存储.文件系统规划 2 网络规划 3 CPU评估 4 内核配置 5 资源限制 6 防火墙配置 四.数据库初始化 1 initdb 介绍 2 postgresql.conf参数配置 3 pg_hba.conf数据库

Clojure世界：Http Client

    使用http client提交表单或者下载网页也是非常常见的任务,比如使用Java的时候可以用标准库的HttpURLConnection,也可以选择Apache Http Client.在clojure里也有这样的类库,这里我将介绍三个各有特色的http client实现.     首先,我最先推荐使用clj-http这个类库,它是Apache HttpClient的clojure wrapper,是一个提供同步API的简单易用的Http Client. 名称: clj-http 主页:

postgresql 9.4 在linux环境的安装步骤详解

环境说明 系统:centos 6.4 64位 软件:postgresql 9.4.1     软件下载 cd /usr/local/src/ wget https://ftp.postgresql.org/pub/source/v9.4.1/postgresql-9.4.1.tar.gz   安装依赖包 yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-deve

laravel容器延迟加载以及auth扩展详解

 昨天按照手册教程,动手写一个Auth扩展,按照包独立性的原则,我不希望将Auth::extend()这种方法写在 start.php 中,毫无疑问,我选择了在服务提供器register()方法中注册扩展驱动.然而,事与愿违-- 发现问题 当我在 LoauthServiceProvider 中这样写的时候:   代码如下: public function register() { // Auth::extend('loauth',function($app){}); }   报错   代码如下:

laravel容器延迟加载以及auth扩展详解_php实例

昨天按照手册教程,动手写一个Auth扩展,按照包独立性的原则,我不希望将Auth::extend()这种方法写在 start.php 中,毫无疑问,我选择了在服务提供器register()方法中注册扩展驱动.然而,事与愿违-- 发现问题 当我在 LoauthServiceProvider 中这样写的时候: 复制代码 代码如下: public function register() {     //     \Auth::extend('loauth',function($app){}); } 报

OpenVPN client端/server端配置文件详细说明

一,OpenVPN server端配置文件详细说明 ################################################# # 针对多客户端的OpenVPN 2.0 的服务器端配置文件示例 # # 本文件用于多客户端<->单服务器端的OpenVPN服务器端配置 # # OpenVPN也支持单机<->单机的配置(更多信息请查看网站上的示例页面) # # 该配置支持Windows或者Linux/BSD系统.此外,在Windows上,记得将路径加上双引号, #