本文共 11548 字，大约阅读时间需要 38 分钟。
要支持ssl连接, 数据库服务端和客户端都需要openssl包.以CentOS 5.x 64为例 : openssl-0.9.8e-20.el5openssl-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 -dOPENSSLDIR: "/etc/pki/tls"pg93@db-172-16-3-33-> cd /etc/pki/tlspg93@db-172-16-3-33-> lltotal 36Klrwxrwxrwx 1 root root 19 Apr 10 09:01 cert.pem -> certs/ca-bundle.crtdrwxr-xr-x 2 root root 4.0K Apr 10 09:01 certsdrwxr-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.cnfdrwxr-xr-x 2 root root 4.0K Mar 5 19:26 privatessl认证配置 : PostgreSQL 服务器配置 : 1. 生成自签名的key, postgres操作系统用户执行 : openssl req -new -text -out server.req进入交互模式 : 输入phrase : 假设这里填的是digoalEnter 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输入emailEmail Address []:Please enter the following 'extra' attributesto 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.pem2. 接下来删除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 passwordLOCATION: initialize_SSL, be-secure.c:784使用pg_ctl -w参数后会等待用户输入, 可以正常启动.pg93@db-172-16-3-33-> pg_ctl start -wwaiting for server to start....Enter PEM pass phrase:.LOG: 00000: loaded library "pg_stat_statements"LOCATION: load_libraries, miscinit.c:1296 doneserver started删除pass phrase后则不会出现这个问题.3. 删除passphrase, openssl rsa -in privkey.pem -out server.keyrm 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.crt5. 修改server.key文件权限 : chmod 600 server.key6. 然后将server.crt和server.key移动到$PGDATAmv server.crt server.key $PGDATA7. 接下来要配置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 renegotiationsssl_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 md59. 重启数据库 : pg_ctl restart -m fast10. (客户端也需要openssl lib库)客户端连接数据库 : 注意到提示了SSL连接.postgres@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres -d digoalPassword 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 sslpsql 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.8eopenssl-0.9.8e-20.el511. 创建sslinfo extension, 可以查看一些ssl相关的连接信息.postgres@db-172-16-3-39-> psql -h 172.16.3.33 -p 1999 -U postgres postgresPassword 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 EXTENSIONdigoal=# 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.confhostssl all all 0.0.0.0/0 md5#hostnossl all all 0.0.0.0/0 md5reload[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 bytes33 packets captured33 packets received by filter0 packets dropped by kernel调整pg_hba.conf#hostssl all all 0.0.0.0/0 md5hostnossl all all 0.0.0.0/0 md5reload[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 bytes52 packets captured52 packets received by filter0 packets dropped by kernel3. 使用wireshark分析数据包 : nossl.dmp中找到了md5内容.这个md5值并不是pg_shadow中存储的md5值, 而是加上了一个token后再次md5的值. 所以密码相对来说被破解的概率较小.但是数据则不是加密的, 很容易被截获.在ssl.dmp中则只有加密后的信息, 因为所有的数据都加密了, 所以无法窥探到有价值的信息.Encrypting Passwords Across A NetworkThe 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 AuthenticationIt 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.html2. http://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-CERT3. http://www.postgresql.org/docs/9.3/static/auth-username-maps.html4. http://h71000.www7.hp.com/doc/83final/ba554_90007/ch04s02.html5. http://www.postgresql.org/docs/9.3/static/libpq-ssl.html6. http://www.postgresql.org/docs/9.3/static/runtime-config-connection.html#GUC-SSL7. http://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html8. http://www.postgresql.org/docs/9.3/static/sslinfo.html9. http://joelonsql.com/2013/04/27/securing-postgresql-using-hostssl-cert-clientcert1/10. http://www.oschina.net/translate/securing-postgresql-using-hostssl-cert-clientcert1?cmp11. pg93@db-172-16-3-33-> openssl genrsa helpusage: 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 generator12. pg93@db-172-16-3-33-> openssl rsa helpunknown option helprsa [options]outfilewhere 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 helpunknown option helpreq [options] outfilewhere 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
转载地址：http://yrlox.baihongyu.com/