Column level encryption allows users to select specific information or attributes to be encrypted instead of encrypting an entire database. Some data is more sensitive than others such as personal beliefs, home addresses, etc. This information can identify a person. In order to ensure private information is protected in transit and at rest, data should be encrypted. Encryption will encode plaintext data into ciphertext.

Non-designated readers or receivers will not able to read the data without the decryption key. Even Database Administrator should not be allowed to see sensitive data belonging to others. The fact that not all data is sensitive and important, Column Level Encryption was created to allow users flexibility in choosing what sort of attributes (table columns) should be encrypted.

You may also like: Transparent Data Encryption for Databases.

Here, I am going to explain how text data that is categorized as sensitive should be encrypted in GCP managed Cloud SQL databases. Think of this data as descriptive, unstructured (no fixed format and length) in nature (e.g. home address, personal opinion, or beliefs on religious matters, etc). 

Advantages of Column Level Encryption

1. Flexibility in data to encrypt. The application can be written to control when, where, by whom, and how data is viewed.

2. More secure as each column can have its own unique encryption key within the database.

3. Encryption is possible when data is active and not just “at rest.”

4. Retrieval speed is maintained because there's less encrypted data.

Google Cloud KMS Service

Unlike Microsoft SQL server, managed the Cloud SQL service offering from GCP with MySQL and Postgres variants does not offer Column Level Encryption natively. This limitation forces us to encrypt/ decrypt each sensitive value, one by one, inside our program and then persist into a database (by encryption) column or display on an application's user interface (by decryption). 

Asymmetric keys (Asymmetric decrypt) from Google Cloud KMS can be used to encrypt and decrypt sensitive data. The public key encrypts sensitive data; whereas the private key is used to decrypt it.

To create an Asymmetric decrypt key in Cloud KMS, select the crypto algorithm that provides at least 128 bits of security. 

You need to use Cloud KMS APIs to perform crypto operations — encrypt and decrypt. 

A service account is used in Google to invoke API of Service without the user getting involved. The service account is then assigned permissions to access the resources it needs. We will create two service accounts —one with an encrypt and another with a decrypt role. Following the steps below: 

1. Download a JSON key file for that service account.

2. Provision that key file into your production environment.

3. Load the credentials from the key file in your code.

cloudkms.cryptoKeyEncrypter ---role to encrypt data
cloudkms.cryptoKeyDecrypter -- role to decrypt data

Encryption Logic

Please refer code block below to encrypt each sensitive value using Cloud KMS API client
KeyManagementServiceClient. Use Service account JSON key file created above for encrypt. 

After this, persist each encrypted value into database programmatically.

public byte[] Encrypt(string projectId, string locationId, string keyRingId, string cryptoKeyId, string plaintext)
        {
            var credential = GoogleCredential.FromFile(@<<Encrypt Service account json key file>>)
                    .CreateScoped(KeyManagementServiceClient.DefaultScopes);
            var channel = new Grpc.Core.Channel(KeyManagementServiceClient.DefaultEndpoint.ToString(),
               credential.ToChannelCredentials());
            var client = KeyManagementServiceClient.Create(channel);

            CryptoKeyName cryptoKeyName = new CryptoKeyName(projectId, locationId, keyRingId, cryptoKeyId);
            byte[] plaintextBA = System.Text.Encoding.UTF8.GetBytes(plaintext); 
            CryptoKeyPathName pathName = CryptoKeyPathName.Parse(cryptoKeyName.ToString());
            EncryptResponse result = client.Encrypt(pathName, ByteString.CopyFrom(plaintextBA));
            return result.Ciphertext.ToByteArray();
        }

Decryption Logic

Please refer code block below to decrypt each sensitive column value using Cloud KMS API client KeyManagementServiceClient. Use Service account JSON key file created for decrypt. 

This decrypted plan value is then shown on GUI to authorised users. 

Ensure that access to Service account json key file for decryption is not available to any users except the program.

public string Decrypt(string projectId, string locationId, string keyRingId, string cryptoKeyId,
             string encryptedText) 
        {
            var credential = GoogleCredential.FromFile(@<<Decrypt Service account json key file>>)
                    .CreateScoped(KeyManagementServiceClient.DefaultScopes);
            var channel = new Grpc.Core.Channel(KeyManagementServiceClient.DefaultEndpoint.ToString(), credential.ToChannelCredentials());
            var client = KeyManagementServiceClient.Create(channel);

            CryptoKeyName cryptoKeyName = new CryptoKeyName(projectId, locationId, keyRingId, cryptoKeyId);
            DecryptResponse result = client.Decrypt(cryptoKeyName, encryptedText);
            return result.Plaintext.ToStringUtf8();
        }

Bring Your own Keys (Customer Generated)

You have the option to use a Google-generated key or import your own key into Cloud HSM. For security reasons, you can manually wrap your key created outside Cloud Key Management Service and then import it to Cloud KMS. Please refer to the links below to understand this process

• https://cloud.google.com/kms/docs/wrapping-a-key.

• https://cloud.google.com/kms/docs/importing-a-wrapped-key.

Conclusion

This may be one approach that can be considered for GCP in order to achieve column level encryption for sensitive (unstructured) data. Databases with native support for column level encryption are the best bet from a performance perspective.

Here, you need to do a fair bit of work to manually manage encryption in the absence (as of this writing) of native support for Column Level Encryption in Cloud SQL Postgres database service. Performance may take a hit if there are many values for which crypto operations need to be performed. 

Related Articles

• New System of Encryption of User Data in Android 5.0.
• Encryption, Part 1: Symmetric Encryption.