PostgreSQL SSL链路压缩例子
背景
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