Dazzle "library" action - prize solicitation of Jincang of the National People's Congress - KingbaseES table space encryption - transparent encryption

Posted by jipacek on Wed, 13 Oct 2021 00:13:37 +0200

[this article is participating in the action of showing off the "Treasury" - prize solicitation of Jincang of the National People's Congress]

Activity link: https://marketing.csdn.net/p/98bd30353e7cb998b6070a89e8b91edb

Transparent storage encryption refers to encrypting the data when it is written to the disk, and decrypting it when the authorized user re reads the data. The encryption and decryption process is transparent to users without modifying the application. Authorized users will not even notice that the data has been encrypted on the storage medium. Currently, only table space encryption is supported under transparent encryption.

All of the following use cases have been tested based on KingbaseES V8R6.

1, Configure transparent encryption

V8R6 database built-in encryption algorithm supports sm4 and rc4. During database initialization, specify the default transparent encryption algorithm through the - e option. If not specified, the default is sm4.

initdb -D data -USYSTEM -e sm4

Load encryption library file

shared_preload_libraries = 'sysencrypt'

Open wallet: to create an encrypted table space, the wallet must be open. When creating a table, the wallet can be closed, which does not affect the use of encryption. When Oracle creates and accesses tables in an encrypted tablespace, the wallet must be open.

test=# openup wallet with password "123456" ;
OPEN WALLET
test=# closeup wallet with password "123456" ;
CLOSE WALLET
test=# alter wallet with password "Kingbase";
ALTER WALLET
test=# openup wallet with password "Kingbase" ;
OPEN WALLET

Create encrypted tablespaces

test=# CREATE TABLESPACE ENC_TS LOCATION '/data/tbs01' with (encryption = true);
CREATE TABLESPACE
test=# select * from pg_tablespace;
  oid  |   spcname   | spcowner | spcacl |    spcoptions     
-------+-------------+----------+--------+-------------------
  1663 | sys_default |       10 |        | 
  1664 | sys_global  |       10 |        | 
  1986 | sysaudit    |       10 |        | 
 71438 | enc_ts      |       10 |        | {encryption=true}
(4 rows)

2, Data table encryption result verification

1. Create data table

test=# create table t1_encrypt(id integer) tablespace ENC_TS;
CREATE TABLE
test=# insert into t1_encrypt select generate_series(1,100);
INSERT 0 100
test=# create table t1_noencrypt(id integer);
CREATE TABLE
test=# insert into t1_noencrypt select generate_series(1,100);
INSERT 0 100

test=# select tablename,tablespace from pg_tables where tablename in ('t1_encrypt','t1_noencrypt');
  tablename   | tablespace 
--------------+------------
 t1_encrypt   | enc_ts
 t1_noencrypt | 
(2 rows)

test=# select relname,reltablespace,relfilenode from pg_class where relname in ('t1_encrypt','t1_noencrypt');
relname | reltablespace | relfilenode
--------------+---------------+-------------
t1_encrypt | 71438 | 71439
t1_noencrypt | 0 | 71442
(2 rows)

2. Verify encryption results

Unencrypted table:

[kingbase@dbhost03 ~]$ hexdump -c /data/kbdata/base/61904/71442
0000000  \0  \0  \0  \0 030   )   - 224  \0  \0  \0  \0 250 001 200 023
0000010  \0     004      \0  \0  \0  \0 340 237   8  \0 300 237   8  \0
0000020 240 237   8  \0 200 237   8  \0   ` 237   8  \0   @ 237   8  \0
0000030     237   8  \0  \0 237   8  \0 340 236   8  \0 300 236   8  \0
0000040 240 236   8  \0 200 236   8  \0   ` 236   8  \0   @ 236   8  \0
0000050     236   8  \0  \0 236   8  \0 340 235   8  \0 300 235   8  \0
0000060 240 235   8  \0 200 235   8  \0   ` 235   8  \0   @ 235   8  \0
0000070     235   8  \0  \0 235   8  \0 340 234   8  \0 300 234   8  \0
0000080 240 234   8  \0 200 234   8  \0   ` 234   8  \0   @ 234   8  \0
0000090     234   8  \0  \0 234   8  \0 340 233   8  \0 300 233   8  \0
00000a0 240 233   8  \0 200 233   8  \0   ` 233   8  \0   @ 233   8  \0
00000b0     233   8  \0  \0 233   8  \0 340 232   8  \0 300 232   8  \0
00000c0 240 232   8  \0 200 232   8  \0   ` 232   8  \0   @ 232   8  \0
00000d0     232   8  \0  \0 232   8  \0 340 231   8  \0 300 231   8  \0
00000e0 240 231   8  \0 200 231   8  \0   ` 231   8  \0   @ 231   8  \0

Encryption table:

[kingbase@dbhost03 61904]$ hexdump -c 71439 
0000000  \0  \0  \0  \0   H 345   , 224  \0  \0  \0  \0 250 001 200 023
0000010  \0     004      \0  \0  \0  \0 340 237   8  \0 300 237   8  \0
0000020 030 035   ` 330 221   } 200   v   H   p 334 315 264   -   T    
0000030 321   "   ! 333 254 347 331   c 322 306   (   P 373 301 216   \
0000040   p   J   * 270 243 351 331 334 205 035 207 221 322 202   " 355
0000050 270 267   4 312   F   H 355  \n 273   C 004   d   > 267 307 271
0000060   i   B 276   ~   m   o 202 222 317  \b   b   K   1 310 317 213
0000070 250   n 252 277   !   * 322 213   >   X 323 246   V 261 177   ,
0000080 035 364 221   i 267 334 332 342 374   E   y 270   p 373 276   1
0000090 353   j 241 024 216 275 027 243   a 335   *   0 350 301 271 035
00000a0 241 335 250 337   v 305   d   W 262 305 365   W 270   '   3 206
00000b0 301 222   M   F   c   ( 360 035 216 347   `   g   ` 321 220   W
00000c0 243 345   ^ 321   V   : 252 331 274 030 274   ( 301   x 250  \v

Result: the encrypted table hexdump result is encrypted.

3, Specifies the tablespace encryption key

When you create an encrypted tablespace, you can specify an encryption key. enckey can not be specified. The system assigns or applies by default. If specified, this key will be used.

CREATE TABLESPACE ENC_TS LOCATION '/data/tbs01' with (encryption = true, enckey = 'kb123456');

Note: this encryption key is transparent to the user. The enckey is used internally in the system. In fact, it is not necessary. It is not recommended that the user specify it.