PostgreSQL cann’t use NULL ciphers(do not encryption) when use hostssl?

背景

PostgreSQL目前没有协议层数据压缩，对于大数据量的应用，或者云数据库场景，容易造成网络瓶颈。

目前必须通过openssl来做数据压缩。但是OPENSSL需要加密，会带来额外的CPU开销，同时还需要客户端支持OPENSSL库。

openssl version   
openssl-1.0.1p  

输出不加密的ciphers，看看这些cipher是不是被PostgreSQL支持呢？

postgres@digoal-> /opt/openssl/bin/openssl ciphers -v 'eNULL'   
ECDHE-RSA-NULL-SHA      SSLv3 Kx=ECDH     Au=RSA  Enc=None      Mac=SHA1  
ECDHE-ECDSA-NULL-SHA    SSLv3 Kx=ECDH     Au=ECDSA Enc=None      Mac=SHA1  
AECDH-NULL-SHA          SSLv3 Kx=ECDH     Au=None Enc=None      Mac=SHA1  
ECDH-RSA-NULL-SHA       SSLv3 Kx=ECDH/RSA Au=ECDH Enc=None      Mac=SHA1  
ECDH-ECDSA-NULL-SHA     SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=None      Mac=SHA1  
NULL-SHA256             TLSv1.2 Kx=RSA      Au=RSA  Enc=None      Mac=SHA256  
NULL-SHA                SSLv3 Kx=RSA      Au=RSA  Enc=None      Mac=SHA1  
NULL-MD5                SSLv3 Kx=RSA      Au=RSA  Enc=None      Mac=MD5  

这里将ssl_ciphers配置为eNULL或者以上都会有问题。

ssl = on                        # (change requires restart)  
ssl_ciphers = 'NULL-SHA256'  
                                        # (change requires restart)  
#ssl_prefer_server_ciphers = on         # (change requires restart)  
#ssl_ecdh_curve = 'prime256v1'          # (change requires restart)  
#ssl_renegotiation_limit = 512MB        # amount of data between renegotiations  
#ssl_renegotiation_limit = 0    # amount of data between renegotiations  
#ssl_cert_file = 'server.crt'           # (change requires restart)  
#ssl_key_file = 'server.key'            # (change requires restart)  
#ssl_ca_file = ''                       # (change requires restart)  
#ssl_crl_file = ''                      # (change requires restart)  

连接数据库时报错

postgres@digoal-> psql postgresql://postgres:postgres@192.168.150.128:1921/postgres?sslmode=require\&application_name='ab'  
psql: SSL error: sslv3 alert handshake failure  
  
2015-08-18 15:36:50.801 CST,,,51545,"192.168.150.128:21611",55d2e092.c959,2,"",2015-08-18 15:36:50 CST,,0,LOG,08P01,"could not accept SSL connection: no shared cipher",,,,,,,,"open_server_SSL, be-secure.c:1034",""  

对应代码

	if (r <= 0)  
        {  
                err = SSL_get_error(port->ssl, r);  
                switch (err)  
                {  
		......  
                        case SSL_ERROR_SSL:  
                                ereport(COMMERROR,  
                                                (errcode(ERRCODE_PROTOCOL_VIOLATION),  
                                                 errmsg("could not accept SSL connection: %s",  
                                                                SSLerrmessage())));  

PostgreSQL 从9.4开始，不支持sslv2和sslv3的ciphers.(因为v2,v3报了严重的安全漏洞)

src/backend/libpq/be-secure.c

    /* set up ephemeral DH keys, and disallow SSL v2/v3 while at it */  
        SSL_CTX_set_tmp_dh_callback(SSL_context, tmp_dh_cb);  
        SSL_CTX_set_options(SSL_context,  
                                                SSL_OP_SINGLE_DH_USE  
                                                SSL_OP_NO_SSLv2 | SSL_OP_NO_SSLv3);  

src/interfaces/libpq/fe-secure.c

		/* Disable old protocol versions */  
                SSL_CTX_set_options(SSL_context, SSL_OP_NO_SSLv2 | SSL_OP_NO_SSLv3);  

但是，这是为什么？IDEA-CBC-SHA应该属于sslv3 的cipher，为什么又可以用？

[root@digoal postgresql-9.4.4]# /opt/openssl/bin/openssl ciphers -v 'ALL'|grep IDEA-CBC-SHA  
IDEA-CBC-SHA            SSLv3 Kx=RSA      Au=RSA  Enc=IDEA(128) Mac=SHA1  
  
ssl_ciphers = 'IDEA-CBC-SHA'  
  
restart postgresql   
  
postgres@digoal-> psql postgresql://postgres:postgres@192.168.150.128:1921/postgres?sslmode=require\&application_name='ab'  
  
psql (9.4.4)  
SSL connection (protocol: TLSv1.2, cipher: IDEA-CBC-SHA, bits: 128, compression: on)  
Type "help" for help.  
postgres=#   

如果要openssl支持压缩，必须在安装openssl时加上zlib

./config --prefix=/opt/openssl zlib shared  
gmake  
gmake test  
gmake install  
  
vi /etc/ld.so.conf  
/opt/openssl/lib  

安装postgresql时指定这个openssl的lib库

LDFLAGS=-L/opt/openssl/lib CPPFLAGS=-I/opt/openssl/include ./configure --prefix=/opt/pgsql9.4.4 --with-pgport=1921 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-debug --enable-dtrace  

参考

1. https://www.openssl.org/source/

2. 《PostgreSQL ssl ciphers performance 比较》

3. http://www.postgresql.org/message-id/flat/4FD9698F.2090407@timbira.com#4FD9698F.2090407@timbira.com

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

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

6. http://blog.csdn.net/as3luyuan123/article/details/13609819

7. http://blog.hagander.net/archives/222-A-few-short-notes-about-PostgreSQL-and-POODLE.html

digoal’s 大量PostgreSQL文章入口