822
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. https://www.postgresql.org/docs/9.3/static/ssl-tcp.html
2. https://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-CERT
3. https://www.postgresql.org/docs/9.3/static/auth-username-maps.html
4. https://h71000.www7.hp.com/doc/83final/ba554_90007/ch04s02.html
5. https://www.postgresql.org/docs/9.3/static/libpq-ssl.html
6. https://www.postgresql.org/docs/9.3/static/runtime-config-connection.html#GUC-SSL
7. https://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html
8. https://www.postgresql.org/docs/9.3/static/sslinfo.html
9. https://joelonsql.com/2013/04/27/securing-postgresql-using-hostssl-cert-clientcert1/
10. https://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
最後更新：2017-04-01 13:38:49