人大金仓KingbaseES sslinfo 插件

前言

KingbaseES对使用SSL 连接加密客户端/服务器通讯的本地支持，可以增加数据传输安全性。

本文展示配置ssl连接，并通过安装一个插件验证ssl加密认证使用。

一、配置ssl连接过程：

ssl_library的参数值是OpenSSL

test=#show ssl_library ;
 ssl_library
-------------
 OpenSSL
(1row)

test=# select version();
version
\----------------------------------------------------------------------------------------------------------------------
KingbaseES V008R006C006B0013 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.220080704 (Red Hat 4.1.2-46), 64-bit
(1row)

想要我们的数据库支持SSL连接，首先需要确保服务器安装了openssl：

[kingbase2@localhost ~]$ openssl version -d
OPENSSLDIR:"/etc/pki/tls"

KingbaseES 数据库配置单向SSL认证连接

首先为服务器创建一个有效期为365天的自签名证书，创建服务端证书和私钥文件并授权：

mkdir ~/openssl

openssl req -new -x509 -days 365 -nodes -text -subj '/CN=kingbase' -out ~/openssl/server.crt -keyout ~/openssl/server.key

chmod 600 ~/openssl/server.key

修改kingbase.conf配置文件

ssl = onssl_cert_file = '/home/kingbase2/openssl/server.crt'ssl_key_file = '/home/kingbase2/openssl/server.key'

启动数据库实例：
[kingbase2@localhostdata]$sys_ctlstartwaitingforservertostart....2022-12-0211:24:32.053CST [16431] LOG:sepapowerextensioninitialized2022-12-02 11:24:32.054 CST [16431] LOG:startingKingbaseESV008R006C006B0013onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.1.220080704(RedHat4.1.2-46),64-bit2022-12-02 11:24:32.055 CST [16431] LOG:listeningonIPv4address"0.0.0.0",port29202022-12-02 11:24:32.055 CST [16431] LOG:listeningonIPv6address"::",port29202022-12-02 11:24:32.056 CST [16431] LOG:listeningonUnixsocket"/tmp/.s.KINGBASE.2920"2022-12-02 11:24:32.077 CST [16431] LOG:redirectinglogoutputtologgingcollectorprocess2022-12-02 11:24:32.077 CST [16431] HINT:Futurelogoutputwillappearindirectory"sys_log".doneserverstarted

[kingbase2@localhostdata]$ksql-h127.0.0.1-dtest-Usystem-p2920Password for user system:ksql(V8.0)SSLconnection(protocol:TLSv1.2,cipher:ECDHE-RSA-AES256-GCM-SHA384,bits:256,compression:off)Type"help"forhelp.

注意连接时候需要加上-h参数，否则不以ssl方式连接。

二、插件实现方法

KingbaseES数据库可将以下内容添加到 kingbase.conf 文件的 shared_preload_libraries 中，重启数据库时自动加载。

shared_preload_libraries = 'sslinfo'

test=# select*from sys_available_extensions where name like'ssl%';
  name   | default_version | installed_version |              comment
---------+-----------------+-------------------+------------------------------------
 sslinfo |1.2|| information about SSL certificates
(1row)

test=# create extension sslinfo;
CREATE EXTENSION

test=# select*from ssl_is_used(), ssl_cipher();

ssl_is_used | ssl_cipher
-------------+-----------------------------
t | ECDHE-RSA-AES256-GCM-SHA384
(1row)

test=# select*from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn
-------+-----+---------+-----------------------------+------+-------------+-----------+---------------+-----------16437| f |||||||16440| f |||||||16442| f |||||||16441| f |||||||18551| t | TLSv1.2| ECDHE-RSA-AES256-GCM-SHA384 |256| f |||16435| f |||||||16434| f |||||||16436| f |||||||
(8rows)

连接已成功加密。如果“ssl = true”，那么这么客户端已经使用ssl连接数据库。