PostgreSQL SSL链路压缩例子__PostgreSQL SSL链路压缩例子: 世界上功能最强大的开源数据库...

PostgreSQL SSL链路压缩例子

作者: 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

Column	Type	Description
pid	integer	Process ID of a backend or WAL sender process
ssl	boolean	True if SSL is used on this connection
version	text	Version of SSL in use, or NULL if SSL is not in use on this connection
cipher	text	Name of SSL cipher in use, or NULL if SSL is not in use on this connection
bits	integer	Number of bits in the encryption algorithm used, or NULL if SSL is not used on this connection
compression	boolean	True if SSL compression is in use, false if not, or NULL if SSL is not in use on this connection
clientdn	text	Distinguished Name (DN) field from the client certificate used, or NULL if no client certificate was supplied or if SSL is not in use on this connection. This field is truncated if the DN field is longer than NAMEDATALEN (64 characters in a standard build)

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