Setting a Ledger Database

Overview

Background

The ledger database, which integrates a blockchain idea, records a user operation in two types of historical tables: a user history table and a global blockchain table. When a user creates a tamper-proof user table, the system automatically adds a hash column to the table to save the hash summary of each row of data. In blockchain mode, a user history table is created to record the change behavior of each data record in the user table. The user's modification to the tamper-proof user table will be recorded in the global blockchain table. Because the history table can only be appended and cannot be modified, the records in the history table are connected to form the modification history of the tamper-proof user table.

The name and structure of the user history table are as follows:

Table 1 Columns in the blockchain._hist user history table

Column Name	Data Type	Description
rec_num	bigint	Sequence number of a row-level modification operation in the history table
hash_ins	hash16	Hash value of the data row inserted by the INSERT or UPDATE operation
hash_del	hash16	Hash value of the data row deleted by the DELETE or UPDATE operation
pre_hash	hash32	Summary of the data in the history table of the current user

Table 2 Mapping between hash_ins and hash_del

-	hash_ins	hash_del
INSERT	(√) Hash value of the inserted row	Empty
DELETE	Empty	(√) Hash value of the deleted row
UPDATE	(√) Hash value of the newly inserted data	(√) Hash value of the row before deletion

Procedure

Create a schema in tamper-proof mode.

For example, create ledgernsp in tamper-proof mode.
```
mogdb=# CREATE SCHEMA ledgernsp WITH BLOCKCHAIN;
```

Create a tamper-proof user table in tamper-proof mode.

For example, create a tamper-proof user table ledgernsp.usertable.

mogdb=# CREATE TABLE ledgernsp.usertable(id int, name text);

Check the structure of the tamper-proof user table and the corresponding user history table.

mogdb=# \d+ ledgernsp.usertable;
                     Table "ledgernsp.usertable"
 Column |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              |
 name   | text    |           | extended |              |
 hash   | hash16  |           | plain    |              |
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no
History table name: ledgernsp_usertable_hist

mogdb=# \d+ blockchain.ledgernsp_usertable_hist;
             Table "blockchain.ledgernsp_usertable_hist"
  Column  |  Type  | Modifiers | Storage | Stats target | Description
----------+--------+-----------+---------+--------------+-------------
 rec_num  | bigint |           | plain   |              |
 hash_ins | hash16 |           | plain   |              |
 hash_del | hash16 |           | plain   |              |
 pre_hash | hash32 |           | plain   |              |
Indexes:
    "gs_hist_16388_index" PRIMARY KEY, btree (rec_num int4_ops) TABLESPACE pg_default
Has OIDs: no
Distribute By: HASH(rec_num)
Location Nodes: ALL DATANODES
Options: internal_mask=263

NOTE:

Tamper-proof tables cannot be non-row-store tables, temporary tables, foreign tables, or unlogged tables. Non-row-store tables do not have the temper-proof attribute.

When a temper-proof table is created, a system column named hash is automatically added. Therefore, the maximum number of columns in the temper-proof table is 1599.

Modify the data in the tamper-proof user table.

For example, execute INSERT, UPDATE, or DELETE on the tamper-proof user table.

mogdb=# INSERT INTO ledgernsp.usertable VALUES(1, 'alex'), (2, 'bob'), (3, 'peter');
INSERT 0 3
mogdb=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
 id | name  |       hash
----+-------+------------------
  1 | alex  | 1f2e543c580cb8c5
  2 | bob   | 8fcd74a8a6a4b484
  3 | peter | f51b4b1b12d0354b
(3 rows)

mogdb=# UPDATE ledgernsp.usertable SET name = 'bob2' WHERE id = 2;
UPDATE 1
mogdb=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
 id | name  |       hash
----+-------+------------------
  1 | alex  | 1f2e543c580cb8c5
  2 | bob2  | 437761affbb7c605
  3 | peter | f51b4b1b12d0354b
(3 rows)

mogdb=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
 id | name  |       hash
----+-------+------------------
  1 | alex  | 1f2e543c580cb8c5
  2 | bob2  | 437761affbb7c605
  3 | peter | f51b4b1b12d0354b
(3 rows)

mogdb=# DELETE FROM ledgernsp.usertable WHERE id = 3;
DELETE 1
mogdb=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id;
 id | name |       hash
----+------+------------------
  1 | alex | 1f2e543c580cb8c5
  2 | bob2 | 437761affbb7c605
(2 rows)

Viewing Historical Operation Records in the Ledger

Prerequisites

You are an audit administrator or a role that has the audit administrator permissions.
The database is running properly, and a series of addition, deletion, and modification operations are performed on the tamper-proof database to ensure that operation records are generated in the ledger for query.

Background

Only users with the AUDITADMIN attribute can view historical operation records in the ledger. For details about database users and how to create users, see Managing Users and Their Permissions.
To query the global blockchain table gs_global_chain, run the following command:
```
SELECT * FROM gs_global_chain;
```
This table contains 11 fields. For details about the meaning of each field, see GS_GLOBAL_CHAIN.
To query the user history table in BLOCKCHAIN mode,

for example, the schema of the user table is ledgernsp, the table name is usertable, and the name of the corresponding user history table is blockchain.ledgernsp_usertable_hist, you can run the following command:
```
SELECT * FROM blockchain.ledgernsp_usertable_hist;
```
The user history table contains four fields. For details about the meaning of each field, see Table 1.

NOTE: Generally, the name of a user history table is in the format of blockchain.hist. If the schema name or table name of the tamper-proof user table is too long, the length of the table name generated using the preceding format may exceed the upper limit. In this case, the blockchain.hist format is used to name the table.

Procedure

Log in as the OS user omm to the primary node of the database.
Run the following command to connect to the database:
```
gsql -d postgres -p 8000
```
postgres is the name of the database, and 8000 is the port number.

View records in the global blockchain table.

mogdb=# SELECT * FROM gs_global_chain;
 blocknum |  dbname  | username |           starttime           | relid |  relnsp   |  relname  |     relhash      |            globalhash            |
               txcommand
----------+----------+----------+-------------------------------+-------+-----------+-----------+------------------+----------------------------------+------------------
------------------------------------------------------------
       0 | postgres | omm      | 2021-04-14 07:00:46.32757+08  | 16393 | ledgernsp | usertable | a41714001181a294 | 6b5624e039e8aee36bff3e8295c75b40 | insert into ledge
rnsp.usertable values(1, 'alex'), (2, 'bob'), (3, 'peter');
       1 | postgres | omm      | 2021-04-14 07:01:19.767799+08 | 16393 | ledgernsp | usertable | b3a9ed0755131181 | 328b48c4370faed930937869783c23e0 | update ledgernsp.
usertable set name = 'bob2' where id = 2;
       2 | postgres | omm      | 2021-04-14 07:01:29.896148+08 | 16393 | ledgernsp | usertable | 0ae4b4e4ed2fcab5 | aa8f0a236357cac4e5bc1648a739f2ef | delete from ledge
rnsp.usertable where id = 3;

The query result indicates that user omm has consecutively executed three DML commands, including INSERT, UPDATE, and DELETE.

View records in the user history table.

mogdb=# SELECT * FROM blockchain.ledgernsp_usertable_hist;
 rec_num |     hash_ins     |     hash_del     |             pre_hash
---------+------------------+------------------+----------------------------------
      0 | 1f2e543c580cb8c5 |                  | e1b664970d925d09caa295abd38d9b35
      1 | 8fcd74a8a6a4b484 |                  | dad3ed8939a141bf3682043891776b67
      2 | f51b4b1b12d0354b |                  | 53eb887fc7c4302402343c8914e43c69
      3 | 437761affbb7c605 | 8fcd74a8a6a4b484 | c2868c5b49550801d0dbbbaa77a83a10
      4 |                  | f51b4b1b12d0354b | 9c512619f6ffef38c098477933499fe3
(5 rows)

The query result shows that user omm inserts three rows of data to the ledgernsp.usertable table, updates one row of data, deletes one row of data, and leaves two rows of data, and the hash values are 1f2e543c580cb8c5 and 437761affbb7c605.

Query user table data and hash verification columns.

mogdb=# SELECT *, hash FROM ledgernsp.usertable;
 id | name |       hash
----+------+------------------
 1 | alex | 1f2e543c580cb8c5
 2 | bob2 | 437761affbb7c605
(2 rows)

The query result indicates that the remaining two records in the user table are the same as those in step 4.

Checking Ledger Data Consistency

Prerequisites

The database is running properly, and a series of addition, deletion, and modification operations are performed on the tamper-proof database to ensure that operation records are generated in the ledger for query.

Background

Currently, the ledger database provides two verification interfaces: ledger_hist_check(text, text) and ledger_gchain_check(text, text). When a common user invokes a verification interface, only the tables that the user has the permission to access can be verified.
The interface for verifying the tamper-proof user table and user history table is pg_catalog.ledger_hist_check. To verify a table, run the following command:
```
SELECT pg_catalog.ledger_hist_check(schema_name text,table_name text);
```
If the verification is successful, the function returns t. Otherwise, the function returns f.
The pg_catalog.ledger_gchain_check interface is used to check whether the tamper-proof user table, user history table, and global blockchain table are consistent. To verify consistency, run the following command:
```
SELECT pg_catalog.ledger_gchain_check(schema_name text, table_name text);
```
If the verification is successful, the function returns t. Otherwise, the function returns f.

Procedure

Check whether the tamper-proof user table ledgernsp.usertable is consistent with the corresponding user history table.
```
mogdb=# SELECT pg_catalog.ledger_hist_check('ledgernsp', 'usertable');
 ledger_hist_check
-------------------
t
(1 row)
```
The query result shows that the results recorded in the tamper-proof user table and user history table are consistent.
Check whether the records in the tamper-proof ledgernsp.usertable table are the same as those in the corresponding user history table and global blockchain table.
```
mogdb=#  SELECT pg_catalog.ledger_gchain_check('ledgernsp', 'usertable');
 ledger_gchain_check
---------------------
t
(1 row)
```
The query result shows that the records of ledgernsp.usertable in the preceding three tables are consistent and no tampering occurs.

Archiving a Ledger Database

Prerequisites

You are an audit administrator or a role that has the audit administrator permissions.
The database is running properly, and a series of addition, deletion, and modification operations are performed on the tamper-proof database to ensure that operation records are generated in the ledger for query.
The storage path audit_directory of audit files has been correctly configured in the database.

Context

Currently, the ledger database provides two archiving interfaces: ledger_hist_archive(text, text) and ledger_gchain_archive(void). Only the audit administrator can invoke the ledger database interfaces.
The interface for archiving the user history table is pg_catalog.ledger_hist_archive. To archive the table, run the following command:
```
SELECT pg_catalog.ledger_hist_archive(schema_name text,table_name text);
```
If the archiving is successful, the function returns t. Otherwise, the function returns f.
The interface for archiving the global blockchain table is pg_catalog.ledger_gchain_archive. To archive the table, run the following command:
```
SELECT pg_catalog.ledger_gchain_archive();
```
If the archiving is successful, the function returns t. Otherwise, the function returns f.

Procedure

Archive a specified user history table.

mogdb=# SELECT pg_catalog.ledger_hist_archive('ledgernsp', 'usertable')
 ledger_hist_archive
---------------------
t
(1 row)

The user history table is archived as a record:

mogdb=# SELECT * FROM blockchain.ledgernsp_usertable_hist;
 rec_num |     hash_ins     |     hash_del     |             pre_hash
---------+------------------+------------------+----------------------------------
       3 | e78e75b00d396899 | 8fcd74a8a6a4b484 | fd61cb772033da297d10c4e658e898d7
(1 row)

The command output indicates that the user history table of the current node is exported successfully.

Export the global blockchain table.

mogdb=# SELECT pg_catalog.ledger_gchain_archive();
 ledger_gchain_archive
-----------------------
t
(1 row)

The global history table will be archived to n (number of user tables) data records by user table:

mogdb=# SELECT * FROM gs_global_chain;
 blocknum |  dbname  | username |           starttime           | relid |  relnsp   |  relname  |     relhash      |            globalhash            | txcommand
----------+----------+----------+-------------------------------+-------+-----------+-----------+------------------+----------------------------------+-----------
        1 | postgres | libc     | 2021-05-10 19:59:38.619472+08 | 16388 | ledgernsp | usertable | 57c101076694b415 | be82f98ee68b2bc4e375f69209345406 | Archived.
(1 row)

The command output indicates that the global blockchain table of the current node is successfully exported.

Repairing a Ledger Database

Prerequisites

You are an audit administrator or a role that has the audit administrator permissions.
The database is running properly, and a series of addition, deletion, and modification operations are performed on the tamper-proof database to ensure that operation records are generated in the ledger for query.

Context

When an exception occurs or a global blockchain table or user history table is damaged, you can use the ledger_gchain_repair(text, text) or ledger_hist_repair(text, text) interface to repair the table. After the fault is rectified, the result of invoking the global blockchain table or user history table verification interface is true.
The interface for repairing a user history table is pg_catalog.ledger_hist_repair. To repair the table, run the following command:
```
SELECT pg_catalog.ledger_hist_repair(schema_name text,table_name text);
```
If the repair is successful, the function returns the hash increment of the user history table during the repair.
The interface for repairing the global blockchain table is pg_catalog.ledger_gchain_repair. To repair the table, run the following command:
```
SELECT pg_catalog.ledger_gchain_repair(schema_name text,table_name text);
```
If the repair is successful, the function returns the hash increment of the global blockchain table during the repair.

Procedure

Repair a specified user history table.
```
mogdb=# SELECT pg_catalog.ledger_hist_repair('ledgernsp', 'usertable');
 ledger_hist_repair
--------------------
84e8bfc3b974e9cf
(1 row)
```
The query result indicates that the user history table on the current node is successfully repaired. The hash increment of the user history table is 84e8bfc3b974e9cf.
Repair a specified global blockchain table.
```
mogdb=# SELECT pg_catalog.ledger_gchain_repair('ledgernsp', 'usertable');
 ledger_gchain_repair
----------------------
a41714001181a294
(1 row)
```
The query result indicates that the global blockchain table is successfully repaired and a piece of repair data is inserted. The hash value is a41714001181a294.