Using Go Driver to Operate an Encrypted Database

Updated on 2024-06-07 GMT+08:00

View PDF

Before running the SQL statements in this section, ensure that the master key has been generated and the parameters for accessing the master key are clear.

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

Connecting to an Encrypted Database

To connect to an 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 a single port (ip:port) as an example. In this example, the username and password are stored in environment variables. Before running this example, set environment variables in the local environment (set the environment variable name based on the actual situation).
func main() {
    // Set the parameters for accessing the master key.
    // Two methods are provided here. Select either of them.
    // Authentication mode 1: AK/SK authentication (For details about how to obtain parameters, including the project ID, AK, and SK, see the master key generation phase.)
    kmsarg := "key_info='keyType=huawei_kms, kmsProjectId={Project ID}, ak={AK}, sk={SK}'"
    // Example: kmsarg := "key_info='keyType=huawei_kms,kmsProjectId=0b59929e8100268a2f22c01429802728,ak=XMAUMJY******DFWLQW, sk=ga6rO8lx1Q4uB*********2gf80muIzUX'"
    // Authentication mode 2: Account and password authentication (For details about how to obtain related parameters, including the IAM server address, IAM username, IAM user password, account name, and project, see the master key generation phase.)
     kmsarg := "key_info='keyType=huawei_kms," +
     kmsarg := "key_info='keyType=hcs_kms," +
            "iamUrl={IAM server address}," +
            "iamUser={IAM username}," +
            "iamPassword={IAM user password}," +
            "iamDomain={Account name}," +
            "kmsProject={Project}'"
        /* Example:
            kmsarg := "key_info='keyType=huawei_kms," +
                "iamUrl=https://iam.xxx.com/v3/auth/tokens," +
                "iamUser=test," +
                "iamPassword=*********," +
                "iamDomain=test_account," +
                "kmsProject=xxx'";
        */
        

    hostip := os.Getenv("GOHOSTIP")   // GOHOSTIP is the IP address written into the environment variable.
    port := os.Getenv("GOPORT")       // GOPORT indicates the port number written into the environment variable.
    usrname := os.Getenv("GOUSRNAME") // GOUSRNAME indicates the username written into environment variables.
    passwd := os.Getenv("GOPASSWD")   // GOPASSWDW indicates the user password written into the environment variable.
    str := "host=" + hostip + " port=" + port + " user=" + usrname + " password=" + passwd + " dbname=postgres enable_ce=1" + kmsarg // DSN connection string
    // str := "opengauss://" + usrname + ":" + passwd + "@" + hostip + ":" + port + "/postgres?enable_ce=1" + kmsarg  // 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

// Define a master key.
// For details about how to obtain the following parameters, including KMS server address and key ID, see the master key generation phase.
_, err = db.Exec("CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = huawei_kms, KEY_PATH = '{KMS server address}/{Key ID}', ALGORITHM = AES_256);")
// Example of KEY_PATH: https://kms.cn-north-4.myhuaweicloud.com/v1.0/0b59929e8100268a2f22c01429802728/kms/9a262917-8b31-41af-a1e0-a53235f32de9
// Explanation: In the master key generation phase, the KMS has generated and stored the master key. Running this syntax only stores the master key information in the database for future access.
// Note: For details about the KEY_PATH format, see "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide.
// Define a column key.
_, 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

// Define 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()