使用gsql操作密态数据库实验

原作者：罗海鸥

密态数据库核心是加解密都放在客户端，解决数据库上云后数据安全问题，自MogDB 1.1.0版本开始引入。

本次实验环境（一套MogDB主备系统）

解释[omm@luo129 ~]$ gs_om -t status --detail
[   Cluster State   ]

cluster_state   : Normal
redistributing  : No
current_az      : AZ_ALL

[  Datanode State   ]

    node  node_ip         port      instance                       state
----------------------------------------------------------------------------------------
1  luo    192.168.109.128 26000      6001 /opt/mogdb/testdb/data   P Primary Normal
2  luo129 192.168.109.129 26000      6002 /opt/mogdb/testdb/data   S Standby Normal


MogDB=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 luo       | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

第一步登录数据库：（一定加上-C参数）

解释[omm@luo ~]$ gsql -C
gsql ((MogDB 5.0.2 build 245a39c2) compiled at 2023-09-21 16:30:30 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=#
MogDB=#

第二步创建客户端主密钥CMK和列加密密钥CEK：

解释MogDB=#
MogDB=# CREATE CLIENT MASTER KEY ImgCMK1ABC WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value1a", ALGORITHM = RSA_2048);
CREATE CLIENT MASTER KEY
MogDB=# CREATE CLIENT MASTER KEY ImgCMKABC WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value2a", ALGORITHM = RSA_2048);
CREATE CLIENT MASTER KEY
MogDB=#  CREATE COLUMN ENCRYPTION KEY ImgCEK1ABC WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1ABC, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY
MogDB=#  CREATE COLUMN ENCRYPTION KEY ImgCEKABC WITH VALUES (CLIENT_MASTER_KEY = ImgCMKABC, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY

查询存储密钥信息的系统表：（imgcmk是之前实验创建的，忽略就行）

解释MogDB=# SELECT * FROM gs_client_global_keys;
 global_key_name | key_namespace | key_owner | key_acl |        create_date
-----------------+---------------+-----------+---------+----------------------------
 imgcmk          |          2200 |        10 |         | 2023-11-27 17:04:53.857207
 imgcmk1abc      |          2200 |        10 |         | 2023-11-30 16:22:12.404601
 imgcmkabc       |          2200 |        10 |         | 2023-11-30 16:22:18.733278
(3 rows)


MogDB=# SELECT column_key_name,column_key_distributed_id ,global_key_id,key_owner  FROM gs_column_keys;
 column_key_name | column_key_distributed_id | global_key_id | key_owner
-----------------+---------------------------+---------------+-----------
 imgcek          |                3618369306 |         16861 |        10
 imgcek1abc      |                3715701679 |         25032 |        10
 imgcekabc       |                2140732266 |         25036 |        10
(3 rows)

第三步创建加密表：

解释MogDB=# CREATE TABLE test_creditcard(id    int, name         text encrypted with (column_encryption_key = ImgCEKABC, encryption_type = DETERMINISTIC),credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1ABC, encryption_type = DETERMINISTIC));
CREATE TABLE
MogDB=#
MogDB=# \d+ test_creditcard
                            Table "public.test_creditcard"
   Column    |       Type        | Modifiers  | Storage  | Stats target | Description
-------------+-------------------+------------+----------+--------------+-------------
 id          | integer           |            | plain    |              |
 name        | text              |  encrypted | extended |              |
 credit_card | character varying |  encrypted | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

第四步往加密表中写入数据：

MogDB=# INSERT INTO test_creditcard VALUES (1,'joe','6217986500001288393'),(2, 'joy','6219985678349800033');
INSERT 0 2

测试是否加密：

本地客户端带-C参数（未加密）

解释MogDB=# select * from test_creditcard ;
 id | name |     credit_card
----+------+---------------------
  1 | joe  | 6217986500001288393
  2 | joy  | 6219985678349800033
(2 rows)

本地客户端不带-C参数（加密）

解释MogDB=# select * from test_creditcard ;
 id |                                                                         name
      |                                                                                     credit_card

----+------------------------------------------------------------------------------------------------------------------------------------------------
------+----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
  1 | \x016afb987f2e7780ef5c09c9deff4f5dbeb2dd283721532a7ea619953212101a47c52d481b31000000598d919367066801d596f5f7ec8b68e30d7fb4e406b9b5aca13f01c77f7
62585 | \x01af1b79dd8ac6dee8f181611e3ae50de3a356eab0929f79546c888b35d7c26fde172cd8fc31000000affb1ac0dc10ae9df376796f72c968092a41489e4a15a43ab555356b3
007c5a1b1b7e7d141b4620b6073ea8386417c6c
  2 | \x016afb987ff28325c00f4024903dac4a6d45f8c2c025d94057bbebcab46e6dca573e8b311531000000fa58b436cdaeda2d7607c36c5c63cd9566904fe78e92e2455620218e51e
92736 | \x01af1b79dd7cbd7d564ac1ee1587f6a4e3e7386b2493788613faaf7f8c43fd16b6d827bc9931000000fae8d65716358d2566d8ea5ad7b372733a5cbe010ddf76de356360fdb
81502e5f9e7bbbe8e150dbd8e4db4c80706789c
(2 rows)

测试远程登录是否可以看到解密后的数据：（远程客户端无法查看加密列的数据）

解释[omm@luo129 ~]$ gsql -r  -h 192.168.109.128 -U luo
Password for user luo:
gsql ((MogDB 5.0.2 build 245a39c2) compiled at 2023-09-21 16:30:30 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=> select * from test_creditcard ;
 id |                                                                         name
      |                                                                                     credit_card

----+------------------------------------------------------------------------------------------------------------------------------------------------
------+----------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
  1 | \x016afb987f2e7780ef5c09c9deff4f5dbeb2dd283721532a7ea619953212101a47c52d481b31000000598d919367066801d596f5f7ec8b68e30d7fb4e406b9b5aca13f01c77f7
62585 | \x01af1b79dd8ac6dee8f181611e3ae50de3a356eab0929f79546c888b35d7c26fde172cd8fc31000000affb1ac0dc10ae9df376796f72c968092a41489e4a15a43ab555356b3
007c5a1b1b7e7d141b4620b6073ea8386417c6c
  2 | \x016afb987ff28325c00f4024903dac4a6d45f8c2c025d94057bbebcab46e6dca573e8b311531000000fa58b436cdaeda2d7607c36c5c63cd9566904fe78e92e2455620218e51e
92736 | \x01af1b79dd7cbd7d564ac1ee1587f6a4e3e7386b2493788613faaf7f8c43fd16b6d827bc9931000000fae8d65716358d2566d8ea5ad7b372733a5cbe010ddf76de356360fdb
81502e5f9e7bbbe8e150dbd8e4db4c80706789c
(2 rows)

MogDB=> \q
[omm@luo129 ~]$ gsql -r  -h 192.168.109.128 -U luo -C
Password for user luo:
gsql ((MogDB 5.0.2 build 245a39c2) compiled at 2023-09-21 16:30:30 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=> select * from test_creditcard ;
ERROR(CLIENT): failed to decrypt column encryption key
MogDB=> select id from test_creditcard ;
 id
----
  1
  2
(2 rows)

备库本地登录也无法访问加密列

解释[omm@luo129 ~]$ gsql -r -C
gsql ((MogDB 5.0.2 build 245a39c2) compiled at 2023-09-21 16:30:30 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# select * from test_creditcard ;
ERROR(CLIENT): failed to decrypt column encryption key
MogDB=#
MogDB=#
MogDB=#
MogDB=# select id from test_creditcard ;
 id
----
  1
  2
(2 rows)

解决办法：拷贝主库/opt/mogdb/testdb/app/etc/localkms目录内容放到备库相同目录

解释[omm@luo ~]$  cd /opt/mogdb/testdb/app/etc/localkms
[omm@luo localkms]$ ll
total 32
-rw------- 1 omm omm 1752 Nov 30 16:22 key_path_value1a.priv
-rw------- 1 omm omm   84 Nov 30 16:22 key_path_value1a.priv.rand
-rw------- 1 omm omm  536 Nov 30 16:22 key_path_value1a.pub
-rw------- 1 omm omm   84 Nov 30 16:22 key_path_value1a.pub.rand
-rw------- 1 omm omm 1752 Nov 30 16:22 key_path_value2a.priv
-rw------- 1 omm omm   84 Nov 30 16:22 key_path_value2a.priv.rand
-rw------- 1 omm omm  536 Nov 30 16:22 key_path_value2a.pub
-rw------- 1 omm omm   84 Nov 30 16:22 key_path_value2a.pub.rand
[omm@luo localkms]$ scp * 192.168.109.129:`pwd`
key_path_value1a.priv                                                                                              100% 1752   337.5KB/s   00:00
key_path_value1a.priv.rand                                                                                         100%   84    10.6KB/s   00:00
key_path_value1a.pub                                                                                               100%  536   112.5KB/s   00:00
key_path_value1a.pub.rand                                                                                          100%   84    13.0KB/s   00:00
key_path_value2a.priv                                                                                              100% 1752   211.4KB/s   00:00
key_path_value2a.priv.rand                                                                                         100%   84    12.8KB/s   00:00
key_path_value2a.pub                                                                                               100%  536    83.3KB/s   00:00
key_path_value2a.pub.rand                                                                                          100%   84    14.5KB/s   00:00
[omm@luo localkms]$

在备库进行测试（可以访问加密列）

解释[omm@luo129 ~]$  cd /opt/mogdb/testdb/app/etc/localkms
[omm@luo129 localkms]$
[omm@luo129 localkms]$ ll
total 32
-rw------- 1 omm omm 1752 Nov 30 17:15 key_path_value1a.priv
-rw------- 1 omm omm   84 Nov 30 17:15 key_path_value1a.priv.rand
-rw------- 1 omm omm  536 Nov 30 17:15 key_path_value1a.pub
-rw------- 1 omm omm   84 Nov 30 17:15 key_path_value1a.pub.rand
-rw------- 1 omm omm 1752 Nov 30 17:15 key_path_value2a.priv
-rw------- 1 omm omm   84 Nov 30 17:15 key_path_value2a.priv.rand
-rw------- 1 omm omm  536 Nov 30 17:15 key_path_value2a.pub
-rw------- 1 omm omm   84 Nov 30 17:15 key_path_value2a.pub.rand
[omm@luo129 localkms]$
[omm@luo129 localkms]$ gsql -r -C
gsql ((MogDB 5.0.2 build 245a39c2) compiled at 2023-09-21 16:30:30 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# select * from test_creditcard ;
 id | name |     credit_card
----+------+---------------------
  1 | joe  | 6217986500001288393
  2 | joy  | 6219985678349800033
(2 rows)

当然这一特性也存在一定局限性，比如只能进行等值查询。

解释MogDB=# select * from test_creditcard ;
 id | name |     credit_card
----+------+---------------------
  1 | joe  | 6217986500001288393
  2 | joy  | 6219985678349800033
(2 rows)
MogDB=# select * from test_creditcard where credit_card >0;
ERROR(CLIENT): operator is not allowed on datatype of this column
MogDB=#
MogDB=# select * from test_creditcard where credit_card like '621798650000128%';
ERROR(CLIENT): operator is not allowed on datatype of this column
MogDB=#

获取更多信息请访问MogDB官网https://docs.mogdb.io/