Best practices for keyset management for BigQuery AEAD function [closed]

Question

My goal is to build a secure solution based on BigQuery. I use BigQuery tables protected with CMEK. Also, I have a dataflow job that writes data in the table. It encrypts some values with an AEAD function. https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions I read data from BigQuery with Tableau. And decrypt some values with an AEAD function.

I need a secure and reliable approach for keyset management that used with AEAD. Keyset should be available for Dataflow during encryption. Also, the keyset should be available in Tableau for decryption.

rsantiago rsantiago · Accepted Answer · 2020-10-22T20:40:56

As a brief statement of my understanding, your use case consists of keeping data encrypted at rest with CMEKs and AEAD in where Cloud Dataflow and external tools like Tableau can access the data.

I think the mayor problem is that key management is not addressed by AEAD as stated in the Standard documentation:

"While AEAD algorithms are quite useful, they do nothing to address the issues of key generation and key management".

Nevertheless, I can think of the following approaches:

To manage CMEKs it is possible that you want to use Cloud KMS.

Cloud Dataflow can access CMEKs by code Java or Python. But, you will need to check the Tableau documentation to know how to configure/connect to CMEKs in KMS.

From the link you shared, I can read a AEAD keyset is either in byte representation or a json file in plain text; so, I can visualize two paths to manage keysets:
- If you want to take them outside of BigQuery (jsons), you can manage them as secrets and use tools like Secret Manager that allow you use Java or Python to manage the secrets, you can use those languages to integrate with Cloud Dataflow. It's not clear to me how Tableau can use this approach though.
- You can keep the keysets in BigQuery close to their data and perform configurations let your apps access only the data they are authorized to see. In this scenario, Authorized Views in BiQuery would help you. You will need to perform additional configuration in BA and build sql queries to create the Views. Then, Dataflow and Tableau can connect and read from the Authorized View transparently.

As an example of the second point, you can create an Authorized View customers_pii with this query that accesses keysets:

  SELECT
  ecd.customer_id as customer_id,
  AEAD.DECRYPT_BYTES(
    (SELECT ck.keyset
     FROM aead.CustomerKeysets AS ck
     WHERE ecd.customer_id = ck.customer_id),
    ecd.encrypted_animal,
    CAST(ecd.customer_id AS STRING)
  ) AS favorite_animal
FROM aead.EncryptedCustomerData AS ecd;

then, users/apps cannot access keysets, they should query this way to retrieve the data in the View:

SELECT customer_id from customers_pii;

Best practices for keyset management for BigQuery AEAD function [closed]

1 Answers