PostgreSQL 如何实现网络压缩传输或加密传输(openssl)

要支持ssl连接, 数据库服务端和客户端都需要openssl包.
以CentOS 5.x 64为例 : 
openssl-0.9.8e-20.el5
openssl-devel-0.9.8e-20.el5
默认情况下PostgreSQL 读取openssl的配置文件openssl.cnf, 在openssl version -d返回的目录中.
当然也可以使用OPENSSL_CONF环境变量读取指定的配置的文件.
PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.

查看目录 : 
pg93@db-172-16-3-33-> openssl version -d
OPENSSLDIR: "/etc/pki/tls"
pg93@db-172-16-3-33-> cd /etc/pki/tls
pg93@db-172-16-3-33-> ll
total 36K
lrwxrwxrwx 1 root root   19 Apr 10 09:01 cert.pem -> certs/ca-bundle.crt
drwxr-xr-x 2 root root 4.0K Apr 10 09:01 certs
drwxr-xr-x 2 root root 4.0K Apr 10 09:01 misc
-rw-r--r-- 1 root root 9.6K Mar  5 19:26 openssl.cnf
drwxr-xr-x 2 root root 4.0K Mar  5 19:26 private

ssl认证配置 : 
PostgreSQL 服务器配置 : 
1. 生成自签名的key, postgres操作系统用户执行 : 
openssl req -new -text -out server.req

进入交互模式 : 
输入phrase : 假设这里填的是digoal
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
输入国家缩写
Country Name (2 letter code) [GB]:CN
输入省份缩写
State or Province Name (full name) [Berkshire]:Zhejiang
输入城市缩写
Locality Name (eg, city) [Newbury]:Hangzhou
输入组织缩写
Organization Name (eg, company) [My Company Ltd]:skymobi
输入单位缩写
Organizational Unit Name (eg, section) []:
输入common name, 必填.
Common Name (eg, your name or your server's hostname) []:db-172-16-3-33.sky-mobi.com
输入email
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
密码直接回车
A challenge password []:
An optional company name []:
输入完后在当前目录下生成了以下两个文件 : 
-rw-r--r-- 1 pg93 pg93 2.1K May 22 16:18 server.req
-rw-r--r-- 1 pg93 pg93  963 May 22 16:18 privkey.pem

如果直接在命令行中指定, 可以使用以下缩写 : 
commonName (alias CN)
surname (alias SN)
givenName (alias GN)
countryName (alias C)
localityName (alias L)
stateOrProvinceName (alias ST)
organizationName (alias O)
organizationUnitName (alias OU)
例如以上命令可以使用下面代替, 减少输入 : 
openssl req -new -text -out server.req -subj '/C=CN/ST=Zhejiang/L=Hangzhou/O=skymobi/CN=db-172-16-3-33.sky-mobi.com'
直接输入phrase即可.
同样会生成两个文件 : 
-rw-r--r-- 1 pg93 pg93 2.1K May 22 16:27 server.req
-rw-r--r-- 1 pg93 pg93  963 May 22 16:27 privkey.pem

2. 接下来删除passphrase, 不删除的话启动数据库会报这个错, 提示输入pass phrase : 
pg93@db-172-16-3-33-> Enter PEM pass phrase:
FATAL:  XX000: could not load private key file "server.key": problems getting password
LOCATION:  initialize_SSL, be-secure.c:784
使用pg_ctl -w参数后会等待用户输入, 可以正常启动.
pg93@db-172-16-3-33-> pg_ctl start -w
waiting for server to start....Enter PEM pass phrase:.
LOG:  00000: loaded library "pg_stat_statements"
LOCATION:  load_libraries, miscinit.c:1296
 done
server started
删除pass phrase后则不会出现这个问题.

3. 删除passphrase, 
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
如果想保留passphrase的话, 第四步的命令使用
openssl req -x509 -in server.req -text -key privkey.pem -out server.crt
这里会提示输入passphrase.
然后第六步改为
mv server.crt privkey.pem $PGDATA
同时修改postgresql.conf时改为
ssl_key_file = 'privkey.pem' 
4. 接下来turn the certificate into a self-signed certificate and to copy the key and certificate to where the server will look for them.
openssl req -x509 -in server.req -text -key server.key -out server.crt

5. 修改server.key文件权限 : 
chmod 600 server.key
6. 然后将server.crt和server.key移动到$PGDATA
mv server.crt server.key $PGDATA


7. 接下来要配置postgresql.conf. 打开ssl.
ssl = on                                # (change requires restart)
ssl_ciphers = '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'  

8. 接下来配置pg_hba.conf, 让客户端使用ssl连接数据库.
hostssl all all 0.0.0.0/0 md5


9. 重启数据库 : 
pg_ctl restart -m fast

10. (客户端也需要openssl lib库)客户端连接数据库 : 
注意到提示了SSL连接.
postgres@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres -d digoal
Password for user postgres: 
psql (9.1.3, server 9.3devel)
WARNING: psql version 9.1, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

查看到客户端psql调用了libssl这个库.
[root@db-172-16-3-39 ~]# lsof|grep psql|grep ssl
psql       9018  postgres  mem       REG                8,1   315064    5331140 /lib64/libssl.so.0.9.8e
来自这个包 : 
[root@db-172-16-3-39 ~]# rpm -qf /lib64/libssl.so.0.9.8e
openssl-0.9.8e-20.el5
11. 创建sslinfo extension, 可以查看一些ssl相关的连接信息.
postgres@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres postgres
Password for user postgres: 
psql (9.1.3, server 9.3devel)
WARNING: psql version 9.1, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
postgres=# create extension sslinfo;
CREATE EXTENSION
digoal=# select ssl_is_used();
 ssl_is_used 
-------------
 t
(1 row)
digoal=# select ssl_cipher();
     ssl_cipher     
--------------------
 DHE-RSA-AES256-SHA
(1 row)
digoal=# select ssl_version();
 ssl_version 
-------------
 TLSv1
(1 row)

[其他]
1. 配置了ssl=on后, pg_hba.conf中如果只配置了host选项, 那么会优先选择ssl认证.
如果要强制nossl, 那么使用hostnossl.
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
2. tcpdump 对比ssl和nossl的包信息.
调整pg_hba.conf
hostssl all all 0.0.0.0/0 md5
#hostnossl all all 0.0.0.0/0 md5
reload
[root@db-172-16-3-33 ~]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w ssl.dmp
使用psql连接数据库.
dump结果 : 
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
33 packets captured
33 packets received by filter
0 packets dropped by kernel

调整pg_hba.conf
#hostssl all all 0.0.0.0/0 md5
hostnossl all all 0.0.0.0/0 md5
reload
[root@db-172-16-3-33 ~]# tcpdump -i eth0 host 172.16.3.39 -s 0 -w nossl.dmp
使用psql连接数据库.
dump结果 : 
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
52 packets captured
52 packets received by filter
0 packets dropped by kernel

3. 使用wireshark分析数据包 : 
nossl.dmp中找到了md5内容.
这个md5值并不是pg_shadow中存储的md5值, 而是加上了一个token后再次md5的值. 所以密码相对来说被破解的概率较小.
但是数据则不是加密的, 很容易被截获.

在ssl.dmp中则只有加密后的信息, 因为所有的数据都加密了, 所以无法窥探到有价值的信息.

Encrypting Passwords Across A Network
The MD5 authentication method double-encrypts the password on the client before sending it to the server. It first MD5-encrypts it based on the user name, and then encrypts it based on a random salt sent by the server when the database connection was made. It is this double-encrypted value that is sent over the network to the server. Double-encryption not only prevents the password from being discovered, it also prevents another connection from using the same encrypted password to connect to the database server at a later time.

SSL Host Authentication
It is possible for both the client and server to provide SSL certificates to each other. It takes some extra configuration on each side, but this provides stronger verification of identity than the mere use of passwords. It prevents a computer from pretending to be the server just long enough to read the password sent by the client. It also helps prevent "man in the middle" attacks where a computer between the client and server pretends to be the server and reads and passes all data between the client and server.

[参考]
1. http://www.postgresql.org/docs/9.3/static/ssl-tcp.html
2. http://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-CERT
3. http://www.postgresql.org/docs/9.3/static/auth-username-maps.html
4. http://h71000.www7.hp.com/doc/83final/ba554_90007/ch04s02.html
5. http://www.postgresql.org/docs/9.3/static/libpq-ssl.html
6. http://www.postgresql.org/docs/9.3/static/runtime-config-connection.html#GUC-SSL
7. http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html
8. http://www.postgresql.org/docs/9.3/static/sslinfo.html
9. http://joelonsql.com/2013/04/27/securing-postgresql-using-hostssl-cert-clientcert1/
10. http://www.oschina.net/translate/securing-postgresql-using-hostssl-cert-clientcert1?cmp
11. 
pg93@db-172-16-3-33-> openssl genrsa help
usage: genrsa [args] [numbits]
 -des            encrypt the generated key with DES in cbc mode
 -des3           encrypt the generated key with DES in ede cbc mode (168 bit key)
 -aes128, -aes192, -aes256
                 encrypt PEM output with cbc aes
 -out file       output the key to 'file
 -passout arg    output file pass phrase source
 -f4             use F4 (0x10001) for the E value
 -3              use 3 for the E value
 -engine e       use engine e, possibly a hardware device.
 -rand file:file:...
                 load the file (or the files in the directory) into
                 the random number generator
12. 
pg93@db-172-16-3-33-> openssl rsa help
unknown option help
rsa [options] <infile >outfile
where options are
 -inform arg     input format - one of DER NET PEM
 -outform arg    output format - one of DER NET PEM
 -in arg         input file
 -sgckey         Use IIS SGC key format
 -passin arg     input file pass phrase source
 -out arg        output file
 -passout arg    output file pass phrase source
 -des            encrypt PEM output with cbc des
 -des3           encrypt PEM output with ede cbc des using 168 bit key
 -aes128, -aes192, -aes256
                 encrypt PEM output with cbc aes
 -text           print the key in text
 -noout          don't print key out
 -modulus        print the RSA key modulus
 -check          verify key consistency
 -pubin          expect a public key in input file
 -pubout         output a public key
 -engine e       use engine e, possibly a hardware device.
13. 
pg93@db-172-16-3-33-> openssl req help
unknown option help
req [options] <infile >outfile
where options  are
 -inform arg    input format - DER or PEM
 -outform arg   output format - DER or PEM
 -in arg        input file
 -out arg       output file
 -text          text form of request
 -pubkey        output public key
 -noout         do not output REQ
 -verify        verify signature on REQ
 -modulus       RSA modulus
 -nodes         don't encrypt the output key
 -engine e      use engine e, possibly a hardware device
 -subject       output the request's subject
 -passin        private key password source
 -key file      use the private key contained in file
 -keyform arg   key file format
 -keyout arg    file to send the key to
 -rand file:file:...
                load the file (or the files in the directory) into
                the random number generator
 -newkey rsa:bits generate a new RSA key of 'bits' in size
 -newkey dsa:file generate a new DSA key, parameters taken from CA in 'file'
 -[digest]      Digest to sign with (see openssl dgst -h for list)
 -config file   request template file.
 -subj arg      set or modify request subject
 -multivalue-rdn enable support for multivalued RDNs
 -new           new request.
 -batch         do not ask anything during request generation
 -x509          output a x509 structure instead of a cert. req.
 -days          number of days a certificate generated by -x509 is valid for.
 -set_serial    serial number to use for a certificate generated by -x509.
 -newhdr        output "NEW" in the header lines
 -asn1-kludge   Output the 'request' in a format that is wrong but some CA's
                have been reported as requiring
 -extensions .. specify certificate extension section (override value in config file)
 -reqexts ..    specify request extension section (override value in config file)
 -utf8          input characters are UTF8 (default ASCII)
 -nameopt arg    - various certificate name options
 -reqopt arg    - various request text options