GBase 8a集群SSL配置

1、生成SSL连接证书

（1）生成server端的密钥和证书

1、进入目录 /usr/local/ssl/ # 若不存在，创建
2、生成服务端证书
# (1) 生成 ca-cert.pem 证书
# 需要填写Country Name等信息，本例按照下面方式填写，也可以依据用户实际情况填写，以下是参数说明：
reg:     该指令用来创建和处理PKCS#10格式的证书以及自签名证书，做Root CA
-sha1：  证书采用SHA1哈希算法
-nodes：如果该选项被指定，如果私钥文件已经被创建则不用加密。
-new：本选项产生一个新的CSR，它会要用户输入创建CSR的一些必须的信息
-x509：本选项将产生自签名的证书，即Root CA
-days：指定自签名证书的有效期限。默认为30天。
-key：证书私钥文件的来源

[root@node1 ssl]# openssl req -sha1 -new -x509 -nodes -days 3560 -keyout ca-key.pem > ca-cert.pem
Generating a 2048 bit RSA private key
...................+++
..............................+++
writing new private key to 'ca-key.pem'
-----
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) [XX]:gz
State or Province Name (full name) []:gbase8a
Locality Name (eg, city) [Default City]:gz
Organization Name (eg, company) [Default Company Ltd]:gd
Organizational Unit Name (eg, section) []:gd
Common Name (eg, your name or your server's hostname) []:gbase
Email Address []:
[root@node1 ssl]#

# (2) 生成密钥
同样填写一些信息，password部分（A challenge password []: ）建议填写复杂一些的密码

-newkey rsa:bits：用于生成新的rsa密钥以及证书请求。如果用户不知道生成的私钥文件名称，默认采用privkey.pem，生成的证书请求。如果用户不指定输出文件(-out)，则将证书请求文件打印在屏幕上。生成的私钥文件可以用-keyout来指定。生成过程中需要用户输入私钥的保护口令以及证书申请中的一些信息。

-keyout ：指明创建的新的私有密钥文件的文件名。
[root@node1 ssl]# openssl req -sha1 -newkey rsa:2048 -days 3560 -keyout server-key.pem > server-req.pem
Generating a 2048 bit RSA private key
..............................+++
..................................................................................................                                                                                                      ...........+++
writing new private key to 'server-key.pem'
Enter PEM pass phrase:                 [password]
Verifying - Enter PEM pass phrase:     [password]
-----
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) [XX]:gz
State or Province Name (full name) []:gbase8a
Locality Name (eg, city) [Default City]:gz
Organization Name (eg, company) [Default Company Ltd]:gd
Organizational Unit Name (eg, section) []:gd
Common Name (eg, your name or your server's hostname) []:gbase
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:password
An optional company name []:gz

# (3) 将server-key.pem 导出为RSA类型
-out filename：输出证书请求文件
-in filename：输入的证书请求文件
[root@node1 ssl]# openssl rsa -in server-key.pem -out server-key.pem
Enter pass phrase for server-key.pem:      [password]
writing RSA key

# (4) 生成 server-cert.pem
-set_serial n：设置生成证书的证书序列号
[root@node1 ssl]# openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Signature ok
subject=/C=gz/ST=gbase8a/L=gz/O=gd/OU=gd/CN=gbase
Getting CA Private Key

（2）生成client端的密钥和证书

# 在同一目录下，生成 client 端的密钥和证书，生成密钥，输入信息与 server 端相同。
# (1) 生成密钥
[root@node1 ssl]# openssl req -sha1 -newkey rsa:2048 -days 3560 -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
..........................+++
......................................+++
writing new private key to 'client-key.pem'
-----
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) [XX]:gz
State or Province Name (full name) []:gbase8a
Locality Name (eg, city) [Default City]:gz
Organization Name (eg, company) [Default Company Ltd]:gd
Organizational Unit Name (eg, section) []:gd
Common Name (eg, your name or your server's hostname) []:gbase
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:password
An optional company name []:gz

# (2) 将client-key.pem 导出为RSA类型
[root@node1 ssl]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key

# (3) 生成 client-cert.pem
[root@node1 ssl]# openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Signature ok
subject=/C=gz/ST=gbase8a/L=gz/O=gd/OU=gd/CN=gbase
Getting CA Private Key

2、配置集群

（1）配置server端

1、修改集群(所有gcluster节点)配置文件 gbase_8a_gcluster.cnf ，在[gbased]下添加ssl信息
vi $GCLUSTER_BASE/config/gbase_8a_gcluster.cnf
[gbased]
basedir = /opt/{IP}/gcluster/server
datadir = /opt/{IP}/gcluster/userdata/gcluster
socket = /opt/{IP}/gcluster_5258.sock
pid_file = /opt/{IP}/gcluster/log/gcluster/gclusterd.pid
# 添加的信息
ssl-ca=/usr/local/ssl/ca-cert.pem
ssl-cert=/usr/local/ssl/server-cert.pem
ssl-key=/usr/local/ssl/server-key.pem

2、重启集群
[gbase@node1 ~]$ gcluster_services all restart
3、检查配置参数
[gbase@node1 config]$ gccli

GBase client 9.5.3.27.115423108. Copyright (c) 2004-2023, GBase.  All Rights Reserved.

gbase> show variables like 'have%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+
2 rows in set (Elapsed: 00:00:00.00)

show variables like '%ssl%';

（2）配置client端

1、将 server 端生成的 ca-cert.pem，client-req.pem，client-key.pem，client-cert.pem 拷贝到 client 端，修改集群配置文件 gbase_8a_gcluster.cnf，在[client]里添加 ssl 信息
[root@node1 ssl]# scp ca-cert.pem client-req.pem client-key.pem client-cert.pem root@192.168.140.70:/usr/local/tmp/ssl
ca-cert.pem                                                                                                                                                           100% 1273     1.2MB/s   00:00
client-req.pem                                                                                                                                                        100% 1041     1.0MB/s   00:00
client-key.pem                                                                                                                                                        100% 1675     1.4MB/s   00:00
client-cert.pem                                                                                                                                                       100% 1143     1.6MB/s   00:00
2、修改集群(需要测试的gcluster的client端)配置文件 gbase_8a_gcluster.cnf ，在[gbased]下添加ssl信息
# 如果client端没有配置ssl认证的话，则按默认方式连接server
vi $GCLUSTER_BASE/config/gbase_8a_gcluster.cnf
[client]
port=5258
socket = /opt/192.168.140.70/gcluster_5258.sock
connect_timeout=43200
#default_character_set=gbk
# 添加的信息
ssl-ca=/usr/local/tmp/ssl/ca-cert.pem
ssl-cert=/usr/local/tmp/ssl/client-cert.pem
ssl-key=/usr/local/tmp/ssl/client-key.pem
3、重启集群
[gbase@node3 ~]$ gcluster_services all restart
4、测试
[gbase@node3 ~]$ gccli -uuser_666 -pUser_666 -h192.168.140.68
GBase client 9.5.3.27.115423108. Copyright (c) 2004-2023, GBase.  All Rights Reserved.

gbase> status;
--------------
gccli ver 9.5.3.27.115423108, for redhat-linux (x86_64) using readline 6.3

Connection id:          31
Current database:
Current user:           user_666@192.168.140.70
# 这里出现 "Cipher in use" 表示ssl加密连接成功
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         9.5.3.27.115423108
Protocol version:       10
Connection:             192.168.140.68 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               5258
Uptime:                 Elapsed: 00:15:00.00

Threads: 3  Questions: 53  Slow queries: 0  Opens: 29  Flush tables: 1  Open tables: 22  Queries per second avg: 0.58
--------------

# 虽然用户名和密码是对的。因为并没有配置ssl,所以直接报错
[gbase@node1 ~]$ gccli -uuser_666 -pUser_666
ERROR 1045 (28000): Access denied for user 'user_666'@'localhost' (using password: YES)


5、如果client没有配置，则按默认方式连接server。可以通过下面参数，强制使用ssl
grant all on *.* to user_666 require ssl;
grant usage on *.* to user_666 identified by 'User_666' require ssl;

补充如何强制使用SSL认证：

[gbase@node1 ~]$ gccli
gbase> create user ssl_user identified by 'ssl_user' ;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> select * from gbase.user where user='ssl_user'\G
*************************** 1. row ***************************
                   Host: %
                   User: ssl_user
               Password: *8EB387DF9E95F4C633DD1499D860D9B17A8C4E7D
             Default_VC:
            Select_priv: N
            Insert_priv: N
            Update_priv: N
            Delete_priv: N
            Create_priv: N
              Drop_priv: N
            Reload_priv: N
          Shutdown_priv: N
           Process_priv: N
              File_priv: N
             Grant_priv: N
        References_priv: N
             Index_priv: N
             Alter_priv: N
           Show_db_priv: N
             Super_priv: N
  Create_tmp_table_priv: N
       Lock_tables_priv: N
           Execute_priv: N
        Repl_slave_priv: N
            Unmask_priv: N
       Create_view_priv: N
         Show_view_priv: N
    Create_routine_priv: N
     Alter_routine_priv: N
       Create_user_priv: N
             Event_priv: N
           Trigger_priv: N
        Drop_table_priv: N
         Drop_view_priv: N
     Drop_database_priv: N
     		   # 这里是空的，表示没有使用ssl认证
               ssl_type:
             ssl_cipher:
            x509_issuer:
           x509_subject:
          max_questions: 0
            max_updates: 0
        max_connections: 0
   max_user_connections: 0
               max_cpus: 0
           max_memories: 0
          max_tmp_space: 0
         resource_group: 0
          task_priority: 2
user_limit_storage_size:
      user_storage_size: 0
                    UID: 643
                 plugin: gbase_native_password
            auth_string:
1 row in set (Elapsed: 00:00:00.00)

# 配置ssl认证
[gbase@node1 ~]$ gccli
gbase> grant usage on *.* to ssl_user identified by 'ssl_user' require ssl;
Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase> select * from gbase.user where user='ssl_user'\G
*************************** 1. row ***************************
                   Host: %
                   User: ssl_user
               Password: *8EB387DF9E95F4C633DD1499D860D9B17A8C4E7D
             Default_VC:
            Select_priv: N
            Insert_priv: N
            Update_priv: N
            Delete_priv: N
            Create_priv: N
              Drop_priv: N
            Reload_priv: N
          Shutdown_priv: N
           Process_priv: N
              File_priv: N
             Grant_priv: N
        References_priv: N
             Index_priv: N
             Alter_priv: N
           Show_db_priv: N
             Super_priv: N
  Create_tmp_table_priv: N
       Lock_tables_priv: N
           Execute_priv: N
        Repl_slave_priv: N
            Unmask_priv: N
       Create_view_priv: N
         Show_view_priv: N
    Create_routine_priv: N
     Alter_routine_priv: N
       Create_user_priv: N
             Event_priv: N
           Trigger_priv: N
        Drop_table_priv: N
         Drop_view_priv: N
     Drop_database_priv: N
     		   # 查看user表的ssl_type变成了ANY，而不是默认的空
               ssl_type: ANY
             ssl_cipher:
            x509_issuer:
           x509_subject:
          max_questions: 0
            max_updates: 0
        max_connections: 0
   max_user_connections: 0
               max_cpus: 0
           max_memories: 0
          max_tmp_space: 0
         resource_group: 0
          task_priority: 2
user_limit_storage_size:
      user_storage_size: 0
                    UID: 643
                 plugin: gbase_native_password
            auth_string:
1 row in set (Elapsed: 00:00:00.00)

3、配置jdbc连接

1、复制凭证
[root@node1 ssl]# scp client-cert.pem ca-cert.pem  root@192.168.140.69:/opt/jdk
client-cert.pem                                                 100% 1143     1.0MB/s   00:00
ca-cert.pem                                                     100% 1273   931.9KB/s   00:00
2、将Client凭证转换成DER格式
openssl x509 -outform DER -in client-cert.pem -out client.cert
3、生成客户端keystone文件
[root@node2 jdk]# keytool -import -file client.cert -keystore keystore
输入密钥库口令:      [password]
再次输入新口令:      [password]
所有者: CN=gbase, OU=gd, O=gd, L=gz, ST=gbase8a, C=gz
发布者: CN=gbase, OU=gd, O=gd, L=gz, ST=gbase8a, C=gz
序列号: 1
有效期为 Sun Aug 20 16:57:50 CST 2023 至 Wed Aug 17 16:57:50 CST 2033
证书指纹:
         MD5:  09:3A:71:2D:EF:0C:8B:CA:F8:A3:D6:25:4A:DE:5C:ED
         SHA1: F1:A7:7C:D0:FD:7B:C8:F6:AD:91:DC:35:E6:38:AB:35:CF:2F:B8:2E
         SHA256: 89:2A:42:A5:75:49:9E:07:38:7F:D3:D1:74:F2:12:8E:AF:7F:35:E1:0F:BD:50:DE:09:0B:01:                                                                                                      BF:15:2E:16:28
签名算法名称: SHA1withRSA
主体公共密钥算法: 2048 位 RSA 密钥
版本: 1
是否信任此证书? [否]:  y
证书已添加到密钥库中
4、根据ca-cert.pem文件生成truststore
[root@node2 jdk]# keytool -import -file ca-cert.pem -keystore truststore
输入密钥库口令:      [password]
再次输入新口令:      [password]
所有者: CN=gbase, OU=gd, O=gd, L=gz, ST=gbase8a, C=gz
发布者: CN=gbase, OU=gd, O=gd, L=gz, ST=gbase8a, C=gz
序列号: e92ef341259e50cb
有效期为 Sun Aug 20 16:37:58 CST 2023 至 Thu May 19 16:37:58 CST 2033
证书指纹:
         MD5:  75:84:23:11:28:18:A2:5E:6F:EC:BC:92:C5:5E:41:16
         SHA1: 50:13:0D:8B:30:5E:7C:76:37:56:26:C6:71:E7:E0:CB:5F:57:A9:27
         SHA256: C4:96:19:65:62:18:29:90:BE:16:71:08:4E:3E:00:FA:C7:66:9E:E4:FF:14:0B:82:25:CE:DA:                                                                                                      22:2C:23:73:4A
签名算法名称: SHA1withRSA
主体公共密钥算法: 2048 位 RSA 密钥
版本: 3

扩展:

#1: ObjectId: 2.5.29.35 Criticality=false
AuthorityKeyIdentifier [
KeyIdentifier [
0000: 8B 64 0B 5A 5F F1 D4 BE   FA E4 18 15 9A EC 3B F6  .d.Z_.........;.
0010: 3A 27 CC 18                                        :'..
]
]

#2: ObjectId: 2.5.29.19 Criticality=false
BasicConstraints:[
  CA:true
  PathLen:2147483647
]

#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 8B 64 0B 5A 5F F1 D4 BE   FA E4 18 15 9A EC 3B F6  .d.Z_.........;.
0010: 3A 27 CC 18                                        :'..
]
]

是否信任此证书? [否]:  y
证书已添加到密钥库中

Java代码：

/**
* yum -y install java-1.8.0-openjdk*
* cd /usr/lib/jvm
* cd java-1.8.0-openjdk 
* 需要把jar包放到下面的目录下
* $JAVA_HOME/jre/lib/ext/
* 或者使用 java -cp .:gbase*.jar JDBCTest.java 方式
*/

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class JDBCTest {
    public static String driver_class = "com.gbase.jdbc.Driver";
    public static String url = "jdbc:gbase://192.168.140.68:5258/test?user=user_666&password=User_666&useSSL=true&requireSSL=true&noAccessToProcedureBodies=true";

    public static void main(String[] args) {
          String trustStorePath = "/opt/jdk/truststore";
          String keyStorePath = "/opt/jdk/keystore";
          System.setProperty("javax.net.ssl.keyStore", keyStorePath);
          System.setProperty("javax.net.ssl.keyStorePassword", "password");
          System.setProperty("javax.net.ssl.trustStore", trustStorePath);
          System.setProperty("javax.net.ssl.trustStorePassword", "password");
          dml();
    }

    private static void dml() {
        doInsert();
        doUpdate();
        doDel();
        doSelect();

        /**
         * 通过CallableStatement调用
         */
        doprcGetResult();
    }


    public static String getUrl() {
        return url;
    }


    public static void setUrl(String url) {
        JDBCTest.url = url;
    }

    //jdbc标准增删改查样例.start.{

    /**
     * 执行查询额语句.
     */
    public static void doSelect() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            System.out.println("==========================  start select  ===============================");
            conn = getConnection();
            String sql = "select * from testtb u where u.c2 = ? ";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, "2222");
            rs = pstm.executeQuery();

            System.out.println("execute sql = " + sql);

            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                System.out.println("----------------------------");
                for (int i = 0; i < columnCount; i++) {
                    System.out.println(rsmd.getColumnName(i + 1) + "=" + rs.getObject(i + 1));
                }
                System.out.println("----------------------------");
            }
            System.out.println("==========================  end select  ===============================");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, pstm, rs);
        }
    }

    /**
     * 执行插入语句
     */
    public static void doInsert() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            System.out.println("==========================  start insert  ===============================");
            conn = getConnection();
            String sql = "insert into testtb values(?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, "c1");
            pstm.setString(2, "2222");
            pstm.addBatch();
            pstm.setString(1, "c3");
            pstm.setString(2, "c4");
            pstm.addBatch();
            System.out.println("execute sql = " + sql);

            int[] count = pstm.executeBatch();
            int sum = 0;
            for (int i = 0; i < count.length; i++) {
                sum = count[i] + sum;
            }
            System.out.println("----------------------------");
            System.out.println("affected " + sum + " row(s)");
            System.out.println("----------------------------");

            System.out.println("==========================  end insert  ===============================");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, pstm, rs);
        }
    }

    /**
     * 执行更新.
     */
    public static void doUpdate() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {

            System.out.println("==========================  start update  ===============================");
            conn = getConnection();
            String sql = "update testtb t set t.c1 = ? where t.c2 = ?";
            pstm = conn.prepareStatement(sql);
            System.out.println("execute sql = " + sql);

            pstm.setString(1, "has updated");
            pstm.setString(2, "2222");
            int count = pstm.executeUpdate();
            System.out.println("----------------------------");
            System.out.println("affected " + count + " row(s)");
            System.out.println("----------------------------");

            System.out.println("==========================  end update  ===============================");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, pstm, rs);
        }
    }

    /**
     * 执行删除.数据
     */
    public static void doDel() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {

            System.out.println("==========================  start delete  ===============================");
            conn = getConnection();
            String sql = "delete from testtb where c2 = ?";
            pstm = conn.prepareStatement(sql);

            System.out.println("execute sql = " + sql);

            pstm.setString(1, "2222");
            int count = pstm.executeUpdate();
            System.out.println("----------------------------");
            System.out.println("affected " + count + " row(s)");
            System.out.println("----------------------------");

            System.out.println("==========================  end delete  ===============================");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, pstm, rs);
        }
    }
    //jdbc标准增删改查样例.end.}


    // 存储过程调用示例.start.{

    /**
     * 通过CallableStatement调用
     * 存储过程获取结果集
     */
    public static void doprcGetResult() {
        Connection conn = null;
        CallableStatement cstm = null;
        ResultSet rs = null;
        try {
            System.out.println("==========================  start call procedure  ===============================");
            String values = "";
            conn = getConnection();
            cstm = conn.prepareCall("{call procGetResult()}");
            cstm.execute();
            do {
                rs = cstm.getResultSet();
                ResultSetMetaData rsmd = rs.getMetaData();

                while (rs.next()) {
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        values += rs.getString(i) + " ";
                        if (i == rsmd.getColumnCount()) {
                            values += ".\r\n";
                        }
                    }
                }
            } while (cstm.getMoreResults());
            System.out.println("----------------------------");
            System.out.println("结果集=" + values);
            System.out.println("----------------------------");

            System.out.println("==========================  end call procedure  ===============================");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, cstm, rs);
        }
    }


    /**
     * 创建数据库连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        try {
            Class.forName(driver_class);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println(url);
        Connection conn = DriverManager.getConnection(url);
        return conn;
    }

    /**
     * 关闭ResultSet、Statement、Connection
     *
     * @param conn
     * @param pstm
     * @param rs
     */
    public static void closeAll(Connection conn, Statement pstm, ResultSet rs) {
        if (rs != null) {
            try {
                if (!rs.isClosed()) {
                    rs.isClosed();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstm != null) {
            try {
                if (!pstm.isClosed()) {
                    pstm.isClosed();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.isClosed();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}