Last active
May 13, 2022 15:48
Star
You must be signed in to star a gist
Google BigQuery Client-side AEAD Encryption https://blog.salrashid.dev/articles/2022/bq_client_side_aead/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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