金仓数据库KingbaseES安全指南--6.6. SSL客户端证书认证

目录

6.6.1. 关于SSL客户端证书认证

6.6.2. SSL客户端证书认证的工作原理

6.6.3. 配置SSL客户端证书身份验证

6.6.4. 通过SSL客户端证书身份验证连接Kingbase

---

6.6.1. 关于SSL客户端证书认证

传输层安全ssl协议可以KingbaseES数据库的强身份验证相结合。这种认证方法使用证书验证方法， 即SSL客户端证书执行认证。因此，它只适用于 SSL 连接。

6.6.2. SSL客户端证书认证的工作原理

当使用SSL客户端证书认证方式时，服务器将要求客户端提供一个有效的、可信的证书。不会有密码提示将被发送到客户端。证书的cn（通用名）属性将与被请求的数据库用户名进行比较，并且如果匹配将允许登录。用户名映射可以被用来允许cn与数据库用户名不同。

下列被支持的配置选项用于 SSL 客户端证书认证 ：

map 允许在系统和数据库用户名之间的映射。详见 用户名映射 。

在一条指定SSL客户端证书认证 的sys_hba.conf记录中，认证选项 clientcert被假定为verify-ca 或 verify-full，并且它不能被关掉， 因为这种方法中一个客户端证书是必需的。cert方法对基本clientcert证书验证测试所增加的东西是检查cn属性是否匹配数据库用户名。

6.6.3. 配置SSL客户端证书身份验证

6.6.3.1. 证书制作

1. 查看openssl 版本

假设以及安装完成openssl环境： .. code:

#/pkg/8.0.0025/db/bin/ca$ openssl verison
openssl: /home/test/pkg/8.0.0025/db/lib/libssl.so.1.0.0: version `OPENSSL_1.0.2' not found (required by openssl)
openssl: /home/test/pkg/8.0.0025/db/lib/libcrypto.so.1.0.0: version `OPENSSL_1.0.2' not found (required by openssl)
openssl: /home/test/pkg/8.0.0025/db/lib/libcrypto.so.1.0.0: version `OPENSSL_1.0.2g' not found (required by openssl)

出现上述缺少链接库问题，是因为LD_LIBRARY_PATH用的是数据库的lib，置空后正常，
#/pkg/8.0.0025/db/bin/ca$ export LD_LIBRARY_PATH=
#/pkg/8.0.0025/db/bin/ca$ openssl version
OpenSSL 1.0.2g  1 Mar 2016

1. CA证书制作：

#/pkg/8.0.0025/db/bin/ca$ openssl req -newkey rsa:2048 -keyout ca.key -keyform PEM -out ca.csr -outform PEM
Generating a 2048 bit RSA private key
...............................................................................................................................................+++
...+++
writing new private key to 'ca.key'
Enter PEM pass phrase:123456
Verifying - Enter PEM pass phrase: 123456
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:CA
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
#/pkg/8.0.0025/db/bin/ca$ openssl x509 -req -in ca.csr -out ca.crt -signkey ca.key -days 3650
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=CA
Getting Private key
Enter pass phrase for ca.key:123456

1. Root证书制作：

#/pkg/8.0.0025/db/bin/ca$ openssl req -newkey rsa:2048 -keyout root.key -keyform PEM -out root.csr -outform PEM
Generating a 2048 bit RSA private key
..................+++
............+++
writing new private key to 'root.key'
Enter PEM pass phrase:123456
Verifying - Enter PEM pass phrase:123456
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:ROOT
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

#/pkg/8.0.0025/db/bin/ca$ openssl x509 -req -in root.csr -out root.crt -signkey root.key -days 3650
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=ROOT
Getting Private key
Enter pass phrase for root.key:123456

1. 生成服务器证书：

#/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus
...........................................++++++
.++++++
e is 65537 (0x10001)
Enter pass phrase for server.key:123456
Verifying - Enter pass phrase for server.key:123456

#/pkg/8.0.0025/db/bin/ca$ openssl req -new -key server.key -out server.csr
Enter pass phrase for server.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:SERVER
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

拷贝openssl配置文件到当前目录：

.code:

#cp /etc/ssl/openssl.cnf ./
#/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial -out server.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=SERVER
Getting CA Private Key

1. 生成用户usystem证书：

#/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out usystem.key 1024
Generating RSA private key, 1024 bit long modulus
...................................................++++++
..........++++++
e is 65537 (0x10001)
Enter pass phrase for usystem.key:123456
Verifying - Enter pass phrase for usystem.key:123456

#/pkg/8.0.0025/db/bin/ca$ openssl req -new -key usystem.key -out usystem.csr
Enter pass phrase for usystem.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:SYSTEM
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
#/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in usystem.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out usystem.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=SYSTEM
Getting CA Private Key

1. 生成kingbase证书：

#/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out kingbase.key 1024
Generating RSA private key, 1024 bit long modulus
......................................................................++++++
...++++++
e is 65537 (0x10001)
Enter pass phrase for kingbase.key:123456
Verifying - Enter pass phrase for kingbase.key: 123456
#/pkg/8.0.0025/db/bin/ca$ openssl req -new -key kingbase.key -out kingbase.csr
Enter pass phrase for kingbase.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:KINGBASE
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
#/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in kingbase.csr -CA root.crt -CAkey root.key -CAcreateserial -out kingbase.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=KINGBASE
Getting CA Private Key

1. 生成userver证书：

#/pkg/8.0.0025/db/bin/ca$ openssl genrsa -des3 -out userver.key 1024
Generating RSA private key, 1024 bit long modulus
.........................++++++
......++++++
e is 65537 (0x10001)
Enter pass phrase for userver.key:123456
Verifying - Enter pass phrase for userver.key:123456
#/pkg/8.0.0025/db/bin/ca$ openssl req -new -key userver.key -out userver.csr
Enter pass phrase for userver.key:123456
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:BJ
Locality Name (eg, city) []:BJ
Organization Name (eg, company) [Internet Widgits Pty Ltd]:KB
Organizational Unit Name (eg, section) []:KB
Common Name (e.g. server FQDN or YOUR name) []:SERVER
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
#/pkg/8.0.0025/db/bin/ca$ openssl x509 -sha1 -req -in userver.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out userver.crt -outform PEM -days 3650 -passin pass:123456 -extfile ./openssl.cnf -extensions v3_req
Signature ok
subject=/C=CN/ST=BJ/L=BJ/O=KB/OU=KB/CN=SERVER
Getting CA Private Key

6.6.3.2. 配置KingbaseES服务器

1. 环境准备：

1. 在kingbase.conf中增加 ssl=on；

2. 在sys_hba.conf中，host修改为 hostssl，MD5修改为ukpwd ；

3. 把 所有的 crt和key 文件权限改为 600；

4. 把 ca.crt、root.crt、server.crt、userver.crt、server.key、userver.key 放到 数据库目录下；

5. 把 ca.crt、root.crt、kingbase.crt、usyssao.crt、usyssso.crt、usystem.crt 、kingbase.key、usyssao.key、usyssso.key、usystem.key 放到bin目录下；

1. 启动数据库：

# ./kingbase -D ca_data/

启动日志打印如下：
LOG:  转存许可证信息...
LOG:  许可证版本：2.0
LOG:  产品线：ES
LOG:  产品版本：8.0.0
LOG:  版本类型：ME
LOG:  操作系统：LNX
LOG:  体系结构：X64
LOG:  浮动日期模式：是
LOG:  基准日期：2021-09-26
LOG:  许可证有效时间：180天
LOG:  最大连接数：无限制
LOG:  用户信息：V8
LOG:  试用模式：否
LOG:  网卡物理地址检查模式：否
LOG:  网卡物理地址：
LOG:  IPV4地址检查模式：否
LOG:  IPV4地址：
LOG:  序列号：1e763e91-cb914c9a-dfe158e5-f462527b
sh: /home/test/pkg/8.0.0025/db/lib/libtinfo.so.5: no version information available (required by sh)
please enter the server key password:123456
LOG:  SSL 的证书吊销列表文件"/home/test/pkg/8.0.0025/db/bin/ca_data/root.crl"未找到，忽略：没有那个文件或目录
DETAIL:  证书将不予核对吊销列表
LOG:  the encrypt device is opened
LOG:  数据库系统已关闭在 2021-09-27 16:39:46 CST
LOG:  checkpoint record is at 1/1DB9050
LOG:  redo record is at 1/1DB9050; undo record is at 0/0; shutdown TRUE
LOG:  下一个事务ID: 0/269; 下一个OID: 31827
LOG:  下一个 MultiXactId: 1; 下一个 MultiXactOffset: 0
LOG:  数据库系统准备接受连接

注意

上面缺失root.crl警告信息不影响功能正常使用。

6.6.4. 通过SSL客户端证书身份验证连接Kingbase

通过ksql连接数据库，指定相关证书参数：-b kingbase用户证书，-r root用户证书，-k kingbase用户的key -g 指定的是用户证书，-G 指定对应的key

例如，下面这个例子：

#./isql -p19940 -USYSTEM  -d TEST -b ./kingbase.crt -r ./root.crt -k ./kingbase.key -g usystem.crt -G usystem.key -p19970
用户密码 SYSTEM:123456
Enter the password of client key:123456
please input the usb token pin:123456

本次登录信息:
            用户名: SYSTEM
            主机: [local]
            登录时间: 2021-09-27 16:42:57.052739+08

这是你第一次登录.

本次登录和最后一次登录之间的失败次数: 0

密码的过期时间没有设置.

类型:  \h SQL帮助命令
       \? isql帮助命令
       \g 将之前的SQL语句发往服务器执行
       \q 退出

TEST=#

例如，异常连接测试（用户密码错误）：

#./isql -p19940 -USYSTEM  -d TEST -b ./kingbase.crt -r ./root.crt -k ./kingbase.key -g usystem.crt -G usystem.key -p19970
用户密码 SYSTEM:123
Enter the password of client key:123456
please input the usb token pin:123456
ksql: FATAL:  用户"SYSTEM"的口令认证失败

例如，异常连接测试（pin码错误）：

# ./isql -p19940 -USYSTEM  -d TEST -b ./kingbase.crt -r ./root.crt -k ./kingbase.key -g usystem.crt -G usystem.key -p19970
用户密码 SYSTEM:123456
Enter the password of client key:123456
please input the usb token pin:1234
ksql: could not read usbkey key file, maybe wrong pin code