Skip to content

Instantly share code, notes, and snippets.

@salrashid123
Last active May 13, 2022 15:48
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save salrashid123/7b6dd5f9d387acd0ec69d8c0992450d3 to your computer and use it in GitHub Desktop.
Google BigQuery Client-side AEAD Encryption https://blog.salrashid.dev/articles/2022/bq_client_side_aead/
package main
/*
Client-side BQ AEAD Encryption
Sample that encrypts data client side and uses BigQuery streaming insert.
The data is encrypted and is compatible with BQ AEAD functions
https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions
You can then decrypt the data by referencing a table with the cleartextkeyset or inline
as a parameter as shown below. If you query with a parameter inline, the keyset will NOT
get auditlogged (which is good, really)
BQ AEAD has built-in functions to create a Keyset for you automatically , see (KEYS.NEW_KEYSET).
if you want to use your own aesgcm key and import it into a TINK keyset
However, if you have your own raw AES_GCM key and want to use it with bigquery, you need to import
that into a TINK Keyset. The following snippet imports a raw keyset into TINK and is compatible with BQ.
https://github.com/salrashid123/tink_samples/tree/main/external_aes_gcm
{
"primaryKeyId": 2069540565,
"key": [
{
"keyData": {
"typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey",
"value": "GiA573JIYV6HdGy1bgRiKwh4gpdeGlJAXjfDGgYopmqS2Q==",
"keyMaterialType": "SYMMETRIC"
},
"status": "ENABLED",
"keyId": 2069540565,
"outputPrefixType": "TINK"
}
]
}
$ bq query --nouse_legacy_sql 'SELECT * FROM aead.INFORMATION_SCHEMA.TABLES WHERE table_name="encrypteddata"'
+---------------------+--------------+---------------+------------+--------------------+----------+---------------------+-------------------------------------------------------+
| table_catalog | table_schema | table_name | table_type | is_insertable_into | is_typed | creation_time | ddl |
+---------------------+--------------+---------------+------------+--------------------+----------+---------------------+-------------------------------------------------------+
| mineral-minutia-820 | aead | encrypteddata | BASE TABLE | YES | NO | 2022-01-13 14:06:30 | CREATE TABLE `mineral-minutia-820.aead.encrypteddata` |
| | | | | | | | ( |
| | | | | | | | customer_id INT64 NOT NULL, |
| | | | | | | | encrypted_animal BYTES |
| | | | | | | | ); |
+---------------------+--------------+---------------+------------+--------------------+----------+---------------------+-------------------------------------------------------+
$ go run main.go
2022/01/13 09:08:32 Tink Keyset: {
"primaryKeyId": 2069540565,
"key": [
{
"keyData": {
"typeUrl": "type.googleapis.com/google.crypto.tink.AesGcmKey",
"value": "GiA573JIYV6HdGy1bgRiKwh4gpdeGlJAXjfDGgYopmqS2Q==",
"keyMaterialType": "SYMMETRIC"
},
"status": "ENABLED",
"keyId": 2069540565,
"outputPrefixType": "TINK"
}
]
}
2022/01/13 09:08:32 Tink Encrypted: AXtartWvzjSkFPR/h/YJss85bFIk/x3hrsChQTtKgOtOQBXFfT0=
$ bq query --use_legacy_sql=false 'SELECT
*
FROM mineral-minutia-820.aead.encrypteddata AS ecd1;'
+-------------+------------------------------------------------------+
| customer_id | encrypted_animal |
+-------------+------------------------------------------------------+
| 32 | AXtartWvzjSkFPR/h/YJss85bFIk/x3hrsChQTtKgOtOQBXFfT0= |
+-------------+------------------------------------------------------+
bq query \
--parameter=keyset1::CNXd6toHEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIDnvckhhXod0bLVuBGIrCHiCl14aUkBeN8MaBiimapLZGAEQARjV3eraByAB \
--use_legacy_sql=false 'SELECT
ecd1.customer_id as ecd1_cid,
AEAD.DECRYPT_STRING(FROM_BASE64(@keyset1),
ecd1.encrypted_animal,
"somedata"
)
FROM mineral-minutia-820.aead.encrypteddata AS ecd1;'
+----------+-------+
| ecd1_cid | f0_ |
+----------+-------+
| 32 | liger |
+----------+-------+
*/
import (
"bytes"
"context"
"encoding/base64"
"encoding/json"
"log"
"cloud.google.com/go/bigquery"
"github.com/google/tink/go/aead"
"github.com/google/tink/go/insecurecleartextkeyset"
"github.com/google/tink/go/keyset"
)
const (
keySetString = "CNXd6toHEmQKWAowdHlwZS5nb29nbGVhcGlzLmNvbS9nb29nbGUuY3J5cHRvLnRpbmsuQWVzR2NtS2V5EiIaIDnvckhhXod0bLVuBGIrCHiCl14aUkBeN8MaBiimapLZGAEQARjV3eraByAB"
)
type Item struct {
CustomerID int32
EncryptedAnimal string
}
func (i *Item) Save() (map[string]bigquery.Value, string, error) {
return map[string]bigquery.Value{
"customer_id": i.CustomerID,
"encrypted_animal": i.EncryptedAnimal,
}, bigquery.NoDedupeID, nil
}
func main() {
decoded, err := base64.StdEncoding.DecodeString(keySetString)
if err != nil {
log.Fatal(err)
}
ksr := keyset.NewBinaryReader(bytes.NewBuffer(decoded))
ks, err := ksr.Read()
if err != nil {
log.Fatal(err)
}
nkh, err := insecurecleartextkeyset.Read(&keyset.MemReaderWriter{Keyset: ks})
if err != nil {
log.Fatal(err)
}
ksw := &keyset.MemReaderWriter{}
if err := insecurecleartextkeyset.Write(nkh, ksw); err != nil {
log.Fatal(err)
}
buf := new(bytes.Buffer)
w := keyset.NewJSONWriter(buf)
if err := w.Write(ksw.Keyset); err != nil {
log.Fatal(err)
}
var prettyJSON bytes.Buffer
err = json.Indent(&prettyJSON, buf.Bytes(), "", "\t")
if err != nil {
log.Fatal(err)
}
log.Printf("Tink Keyset: %s\n", string(prettyJSON.Bytes()))
a, err := aead.New(nkh)
if err != nil {
log.Fatal(err)
}
plainText := "liger"
ec, err := a.Encrypt([]byte(plainText), []byte("somedata"))
if err != nil {
log.Fatal(err)
}
encryptedData := base64.StdEncoding.EncodeToString(ec)
log.Printf("Tink Encrypted: %s", encryptedData)
projectID := "mineral-minutia-820"
datasetID := "aead"
tableID := "encrypteddata"
ctx := context.Background()
client, err := bigquery.NewClient(ctx, projectID)
if err != nil {
log.Fatal(err)
}
defer client.Close()
inserter := client.Dataset(datasetID).Table(tableID).Inserter()
items := []*Item{
{
CustomerID: 32,
EncryptedAnimal: encryptedData,
},
}
if err := inserter.Put(ctx, items); err != nil {
log.Fatal(err)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment