KingbaseES V8R6 sslinfo 插件

前言

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

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

一、配置ssl连接过程：

ssl_library的参数值是OpenSSL

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

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

想要我们的数据库支持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 = on 
ssl_cert_file = '/home/kingbase2/openssl/server.crt' 
ssl_key_file = '/home/kingbase2/openssl/server.key'

启动数据库实例：
[kingbase2@localhost data]$ sys_ctl start
waiting for server to start....2022-12-02 11:24:32.053 CST [16431] LOG:  sepapower extension initialized
2022-12-02 11:24:32.054 CST [16431] LOG:  starting KingbaseES V008R006C006B0013 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
2022-12-02 11:24:32.055 CST [16431] LOG:  listening on IPv4 address "0.0.0.0", port 2920
2022-12-02 11:24:32.055 CST [16431] LOG:  listening on IPv6 address "::", port 2920
2022-12-02 11:24:32.056 CST [16431] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.2920"
2022-12-02 11:24:32.077 CST [16431] LOG:  redirecting log output to logging collector process
2022-12-02 11:24:32.077 CST [16431] HINT:  Future log output will appear in directory "sys_log".
 done
server started

[kingbase2@localhost data]$ ksql -h 127.0.0.1 -d test -Usystem -p2920
Password for user system:
ksql (V8.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

注意连接时候需要加上-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
(1 row)

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
(1 row)

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 | | | | | | |
(8 rows)

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