Using Go Driver to Operate an Encrypted Database

Before running the SQL statements in this section, ensure that the preparation and configuration phases are complete.

This section uses a complete execution process as an example to describe how to use the encrypted database syntax, including three phases: DDL statement execution, DML statement execution, and cleanup.

Connecting to an Encrypted Database

To connect to the encrypted database, you need to use the Go driver package openGauss-connector-go-pq. Currently, online import is not supported. You need to place the decompressed Go driver source code package in the local project and configure environment variables. For details about how to develop the Go driver, see "Application Development Guide > Development Based on the Go Driver" in Developer Guide. In addition, ensure that GCC 7.3 or later has been installed.

The Go driver supports operations related to the encrypted database. You need to set the enable_ce parameter and add the -tags=enable_ce tag during compilation, decompress GaussDB-Kernel_Database version number_OS version number_64bit_libpq.tar.gz to a specified directory, and add the path of the lib folder to the LD_LIBRARY_PATH environment variable. The following is an example of the encryption operation:

//The following uses a single IP address and port number as an example.
func main() {
    str := "host=127.0.0.1 port=8000 user=testuser password=******** dbname=postgres enable_ce=1" // DSN connection string
    // str := "opengauss://testuser:********@127.0.0.1:8000/postgres?enable_ce=1" // URL connection string

    // Obtain the handle of the database connection pool.
    db, err:= sql.Open("opengauss", str)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // The Open function is only used to verify parameters. Use the Ping method to check whether the data source is valid.
    err = db.Ping()
    if err == nil {
        fmt.Printf("Connection succeed!\n")
    } else {
        log.Fatal(err)
    }
}

Creating Keys for Executing Encrypted Equality Query

// Create a CMK.
// The following describes how to create a master key in multiple scenarios. Select one of the following methods as required: key management tool gs_ktool, Huawei Cloud key management service (huawei_kms).
// For details about the KEY_PATH format, see "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide.
_, err = db.Exec("CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = gs_ktool, KEY_PATH = \"gs_ktool/1\", ALGORITHM = AES_256_CBC);")
// In the Huawei Cloud scenario, the project ID and key ID are required in KEY_PATH. For details about how to obtain the key ID, see the preparation phase. For details about how to obtain the project ID, see the configuration phase.
// _, err = db.Exec("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = huawei_kms , KEY_PATH = 'https://kms.cn-north-4.myhuaweicloud.com/v1.0/00000000000000000000000000000000/kms/00000000-0000-0000-0000-00000000000', ALGORITHM = AES_256);");

// Create a CEK.
_, err = db.Exec("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);")

Creating an Encrypted Table for Executing an Encrypted Equality Query

// Create an encrypted table.
_, err = db.Exec("CREATE TABLE creditcard_info (id_number int, name varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC), credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));")
// Insert data.
_, err = db.Exec("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393'), (2,'mike','6217986500001722485'), (3,'joe','6315892300001244581');");
var var1 int
var var2 string
var var3 string
// Query data.
rows, err := db.Query("select * from creditcard_info where name = 'joe';")
defer rows.Close()
// Print information line by line.
for rows.Next() {
    err = rows.Scan(&var1, &var2, &var3)
    if err != nil {
        log.Fatal(err)
    } else {
        fmt.Printf("var1:%v, var2:%v, var3:%v\n", var1, var2, var3)
    }
}

Precompiling the Encrypted Table

// Call the Prepare method of the database instance to create a precompiled object.
delete_stmt, err := db.Prepare("delete from creditcard_info where name = $1;")
defer delete_stmt.Close()
// Call the Exec method of the precompiled object to bind parameters and execute the SQL statement.
_, err = delete_stmt.Exec("mike")

Performing the Copy In Operation on an Encrypted Table

// Call the Begin and Prepare methods of the database instance to create transaction objects and precompiled objects.
tx, err := db.Begin()
copy_stmt, err := tx.Prepare("Copy creditcard_info from stdin")
// Declare and initialize the data to be imported.
var records = []struct {
    field1 int
    field2 string
    field3 string
}{
    {4, "james", "6217986500001234567"},
    {
        field1: 5,
        field2: "john",
        field3: "6217986500007654321",
    },
}
// Call the Exec method of the precompiled object to bind parameters and execute the SQL statement.
for _, record := range records {
    _, err = copy_stmt.Exec(record.field1, record.field2, record.field3)
    if err != nil {
        log.Fatal(err)
    }
}
// Call the Commit method of the transaction object to commit the transaction.
err = copy_stmt.Close()
err = tx.Commit()