作者: digoal

日期: 2015-08-18

标签: PostgreSQL , compression , openssl , sslmode=require

背景

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

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

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

《PostgreSQL ssl ciphers performance 比较》

那么能不能只压缩，不加密呢？

PostgreSQL数据链路能不能只压缩不加密

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或者以上列出的cipher都会有问题。

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

连接时使用这两个参数

psql "sslmode=require sslcompression=1" -h host -p port -U user dataname

https://www.postgresql.org/docs/9.6/static/libpq-connect.html#LIBPQ-CONNECT-SSLMODE

查看链路是否开启压缩和加密

PostgreSQL 9.6的patch，允许用户查看backend的连接信息，如果是SSL连接，输出SSL版本，cipher算法，加密比特位，是否压缩，DNS等信息。

Table 27-6. pg_stat_ssl View

The pg_stat_ssl view will contain one row per backend or WAL sender process, showing statistics about SSL usage on this connection.

It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection.

参考

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

2. http://blog.163.com/digoal@126/blog/static/16387704020134229431304/

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. http://blog.163.com/digoal@126/blog/static/163877040201342233131835

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