Field and Column Level Encryption on Google Cloud SQL (PostgreSQL and MySQL)

8 min readMay 8, 2020

This article explains how to leverage field/column level encryption on Google Cloud SQL

PostgreSQL 11
and MySQL 5.7.

The versions above were used for the tests, but the same approach can be used also on other versions of databases.

The examples in this article are focused both on symmetric and asymmetric encryption for PostgreSQL and only on symmetric for MySQL due to license limitations.

Introduction

GCP provides out-of-the-box several layers of encryption to protect data (see image below). Using multiple layers of encryption adds redundant data protection and allows us to select the optimal approach based on application requirements (Source: https://cloud.google.com/security/encryption-at-rest/default-encryption).

Although several layers of encryption are already in place out-of-the-box, for some specific industries (e.g.: FSI) can be useful or mandatory to add a further security layer like for example Field (or Column) Level Encryption.

The goal of the additional layer is to prevent access to sensitive data by people that are usually allowed to connect to the database or that can have maliciously access to the OS, but not to visualize sensitive data (eg: Name, Address, Credit Card Numbers, …) or that can get access to the storage of the machine hosting the database.

The field (or column) level encryption, described in this document, allows to:

Encrypt the data with an external keys managed by the customer in another environment (eg: On-Premise)
Choose what are the fields that need to be encrypted and protected (Field/Column Level)
Choose different keys to encrypt different fields
Prevent access to the clear data by people that are allowed to connect to the database, but not to visualize sensitive data
Grant the access to sensitive and protected fields only to users and applications that know the secret key

PostgreSQL 11

PostgreSQL, field level encryption is enabled by pgcrypto extension (Source: https://www.postgresql.org/docs/9.6/static/pgcrypto.html). This extension is natively supported also by Google CloudSQL (Source: https://cloud.google.com/sql/docs/postgres/extensions).

PostgreSQL Symmetric Encryption

Create the Google CloudSQL PostgreSQL instance following the standard documentation (https://cloud.google.com/sql/docs/postgres/create-instance) .
Connect to the database leveraging Google Cloud SDK or a database client.
Once connected to the database, proceed with the SQL statements below:

Enable pgcrypto extension on the PostgreSQL instance

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Create an example “employee” table to store the name and the role of the employees of a company

create table employee (name text, role text);

In this case, the field name is considered sensitive data and should be encrypted leveraging PGP_SYM_ENCRYPT function that accepts as input parameters the value of the field and the encryption key ‘AES_KEY’. The field role is not sensitive and can be stored as plain text. The following insert statement inserts a record with the name encrypted and the role as plain text.

INSERT INTO employee (name, role) VALUES (
PGP_SYM_ENCRYPT(‘Alessandro’,’AES_KEY’),
‘Google Cloud Customer Engineer’);

To test that insert statement worked as expected, it is possible to execute a simple SELECT statement that will return the encrypted value for the field name and the plain text for the field role.

select name, role from employee;

Output:

In order to retrieve the encrypted field it is necessary to use the PGP_SYM_DECRYPT function that can be used both in the SELECT statement and in the WHERE clause. To allow the decryption, the field name is also casted to the binary data type with the syntax: name::bytea .

SELECT PGP_SYM_DECRYPT(name::bytea, ‘AES_KEY’) as name, role
FROM employee
WHERE
PGP_SYM_DECRYPT(name::bytea, ‘AES_KEY’)=’Alessandro’;

Output:

PostgreSQL Asymmetric Encryption

Create the Google CloudSQL PostgreSQL instance following the standard documentation (https://cloud.google.com/sql/docs/postgres/create-instance) .
Before connecting to the database, execute the following commands on a Linux OS to generate the Public and Private keys:

Generate a couple of keys (Public and Private). During the process the following parameters are asked: your name, your email address and a password (can be also blank). The hexadecimal code highlighted below identifies the couple of keys (private and public) created.
Keep in mind the chosen password (YOUR_PASSWORD) because it will be used to decrypt the data with the private key.

gpg — gen-key

Output:

pub rsa3072 2020–03–13 [SC] [expires: 2022–03–13]
F5332A4839D400B8D5A140A90CCD14F82A11E415
F5332A4839D400B8D5A140A90CCD14F82A11E415
uid your_username <your_email@your_company.com>
sub rsa3072 2020–03–13 [E] [expires: 2022–03–13]

Export the private key into the new file private_key.txt with — armor option to get the text format.

gpg — export-secret-keys — armor F5332A4839D400B8D5A140A90CCD14F82A11E415 > ./private_key.txt

Export the public key into the new file public_key.txt with — armor option to get the text format.

gpg — export — armor F5332A4839D400B8D5A140A90CCD14F82A11E415 > ./public_key.txt

3. Now that the couple of keys (private and public) has been created, it is possible to connect to the database leveraging Google Cloud SDK or a database client.

4. Once connected to the database, proceed with the SQL statements below:

Enable pgcrypto extension on the PostgreSQL instance

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Create an example “employee_asymmetric” table to store the name and the role of the employees of a company

create table employee_asymmetric (name text, role text);

In this case, the field name is considered sensitive data and should be encrypted leveraging pgp_pub_encrypt function that accepts as input parameters the value of the field and the Public encryption key generated in the previous steps and available in the file “public_key.txt”. The field role is not sensitive and can be stored as plain text. The following insert statement inserts a record with the name encrypted and the role as plain text. The function dearmor is necessary to convert the public key into the binary format from the text format.

INSERT INTO employee_asymmetric (name, role) VALUES (pgp_pub_encrypt(‘Alessandro’, dearmor(‘ — — -BEGIN PGP PUBLIC KEY BLOCK — — -YOUR LONG PUBLIC KEY — — -END PGP PUBLIC KEY BLOCK — — -’)),
‘Google Cloud Customer Engineer’ );

To test that insert statement worked as expected, it is possible to execute a simple SELECT statement that will return the encrypted value for the field name and the plain text for the field role.

select name, role from employee_asymmetric;

Output:

In order to retrieve the encrypted field it is necessary to use the pgp_pub_decrypt function that can be used both in the SELECT statement and in the WHERE clause. To allow the decryption, the field name is also casted to the binary data type with the syntax: name::bytea . The function dearmor is necessary to convert the public key into the binary format from the text format.

SELECT pgp_pub_decrypt(name::bytea, dearmor(‘ — — -BEGIN PGP PRIVATE KEY BLOCK — — -YOUR LONG PRIVATE KEY — — -END PGP PRIVATE KEY BLOCK — — -’)‘pgcrypto’) as name,
Role
FROM employee_asymmetric
WHERE
role=’Google Cloud Customer Engineer’
AND pgp_pub_decrypt(name::bytea, dearmor(‘ — — -BEGIN PGP PRIVATE KEY BLOCK — — -YOUR LONG PRIVATE KEY — — -END PGP PRIVATE KEY BLOCK — — -’),’YOUR_PASSWORD’)=’Alessandro’;

Output:

MySQL 5.7

A similar example is presented for MySQL. For the full encryption documentation on MySQL, please refer to https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html.

Due to license limitations (MySQL Enterprise required), the example below is focused only on symmetric encryption.

MySQL Symmetric Encryption

Create the Google CloudSQL MySQL instance following the standard documentation (https://cloud.google.com/sql/docs/mysql/create-instance) .
Connect to the database leveraging Google Cloud SDK or a database client.
Once connected to the database, proceed with the SQL statements below:

Create a database to store this demo/test

create database encryption_test;

Start using the new database created in the previous step

use encryption_test;

Create the table to store the encrypted field (name) and the other field (role). Unlike on PostgreSQL, for the encrypted fields it is necessary to use the binary format because the encryption function returns a binary.

create table employee ( name varbinary(200), role varchar (200));

In this case, the field name is considered sensitive data and should be encrypted leveraging the AES_ENCRYPT function that accepts as input parameters the value of the field and the encryption key. The field role is not sensitive and can be stored as plain text. The following insert statement inserts a record with the name encrypted and the role as plain text.

INSERT into employee (name, role) VALUES (AES_ENCRYPT(‘Alessandro’, ‘key’), ‘Google Cloud Customer Engineer’);

To test that insert statement worked as expected, it is possible to execute a simple SELECT statement that will return the encrypted value for the field name and the plain text for the field role.

select * from employee;

In order to retrieve the encrypted field it is necessary to use the AES_DECRYPT function that can be used both in the SELECT statement and in the WHERE clause.

SELECT AES_DECRYPT(name, ‘key’) as name, role FROM employee WHERE AES_DECRYPT(name, ‘key’)=’Alessandro’;

Conclusions

Google CloudSQL allows users to manage field (or column) level encryption in PostgreSQL and MySQL further increasing the security stack natively provided by GCP.
The users or applications that need to leverage field level encryption should:

adapt the SQL syntax to use encrypt/decrypt functions
know and use the key to encrypt and decrypt the protected fields.

As all the techniques, the field (or column) level encryption has its pros and cons. These are summarized below:

Pros

Flexibility to choose which columns to encrypt
Flexibility to choose which key to use to encrypt each column
Minimum impact on performances if compared to file or full database encryption
Leveraging asymmetric encryption, it’s possible to encrypt the data with the public key on the database engine and decrypt the data with the private key on the client side. Thanks to this approach, the private key is never used on the database server and never stored, neither temporarily, where the data resides.

Cons

SQL syntax needs to be adapted to use encrypt/decrypt functions
Symmetric techniques leverage the database engine to encrypt and decrypt the data, this situation creates the risk of key exposure through log analysis, traffic sniffing, …
Some fields cannot be encrypted (e.g: Primary Keys)

Field and Column Level Encryption on Google Cloud SQL (PostgreSQL and MySQL)

Introduction

PostgreSQL 11

MySQL 5.7

Conclusions

Written by Alessandro Marrandino