The YugabyteDB YSQL API supports column-level encryption via the Postgres extension called pgcrypto.
The pgcrypto module in PostgreSQL is a powerful tool for securely storing and managing encrypted data using either symmetric key encryption or public key encryption.
In this post we’ll cover public key encryption which uses a pair of keys, a public key and a private key. The public key encrypts the data while the private key decrypts the data.
First we need to install pgcrypto:
yugabyte=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
Now we need to generate a new public and private RSA key pair using the Linux gpg key generator:
[root@localhost ~]# gpg --batch --passphrase '' --quick-generate-key "Jim Knicely (YB Key) <jimk@yugabytedb.tech>" rsa2048
gpg: key 93977B1AC4030B85 marked as ultimately trusted
gpg: revocation certificate stored as '/root/.gnupg/openpgp-revocs.d/A0A8EFB82C3B7B905921E56E93977B1AC4030B85.rev'
[root@localhost ~]# fpr=$(gpg --list-options show-only-fpr-mbox --list-secret-keys | sed -r -n '$!d;s@^([^[:space:]]+).*@\1@g;p')
[root@localhost ~]# echo $fpr
A0A8EFB82C3B7B905921E56E93977B1AC4030B85
[root@localhost ~]# gpg --batch --passphrase '' --quick-add-key $fpr rsa2048 encrypt
Next, let’s export the public and private keys of the newly generated RSA key.
First the private key:
[root@localhost ~]# gpg --export-secret-keys --armor $fpr > ./private_key.txt
Then the public key:
[root@localhost ~]# gpg --export --armor $fpr > ./public_key.txt
Now we are ready to encrypt and decrypt data.
Say we have an EMPLOYEE table that has a column called SSN which stores an employee’s social security number. We’ll definately want to encrypt that data!
yugabyte=# CREATE TABLE employee(id INT PRIMARY KEY, name VARCHAR, ssn VARCHAR);
CREATE TABLE
To encrypt data we’ll use the pgp_pub_encrypt function which was installed via the pgcrypto extension. We need to pass in two parameters to the function: the data to encrypt and the full text of the pgp public key block.
Remember that we exported the public key into a file named public_key.txt. To make INSERTs a little easier, let’s store the content of that file in a variable which we can pass into the pgp_pub_encrypt function.
yugabyte=# \set public_key `cat ./public_key.txt`
Now we can insert some encrypted data…
yugabyte=# INSERT INTO employee (id, name, ssn) SELECT 1, 'Jim', PGP_PUB_ENCRYPT('123456789', dearmor(:'public_key'));
INSERT 0 1
yugabyte=# INSERT INTO employee (id, name, ssn) SELECT 2, 'Jane', PGP_PUB_ENCRYPT('987654321', dearmor(:'public_key'));
INSERT 0 1
yugabyte=# \x
Expanded display is on.
yugabyte=# SELECT * FROM employee WHERE name = 'Jim';
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | 1
name | Jim
ssn | \xc1c04c03d536ef10bcf0e6950107ff40f757664238adb259fd5ef37ffbbe3bd7f18d5fd9a32d54af6e7a62dc798b30acf9aa23ce3eef24bd5fe666a3b863a7f9a5c5c5260683c0edad6dac96ef9ebf28b52a483e60ffb7eee58eacadc7bf8e7d8c7af0d7e229ba96c7a42457c27c1bde86ebeb93fc2f7663aa9dbd5dfa22f41cefeaca30cd66f581cd2cc28e069fad276d9a1ccf8ade7e4a1cc7b55060ad62adf51b0e81961251810fd44b990169cd946a181cc3b720caa82ef0fbbf6da82c990621dc56866f6ea3bc8d24c1cf700faa82ec6dbf672df5a1127f4febc65d8c58b6897f753e3ca1af43ef0395eab7c4ff8a326676b9e15e8d6dbf07ccce78da64cb656c87c78e142d431fbc1b48f99dd23a01b5df5b933e7073bdf235e0c23842b891ec1d2fa4af910dd67757ab2d3ec97eb89471412250722a2723a2e83404fdda3a41a90b4faa83429ed1
To decrypt data we’ll use the pgp_pub_decrypt function which was also installed via the pgcrypto extension. We need to pass in two parameters to the function, this time: the data to decrypt and the full text of the pgp private key block.
Remember that we exported the private key into a file named private_key.txt. To make SELECTs a little easier, let’s again store the content of that file in a variable which we can pass into the function.
yugabyte=# \set private_key `cat ./private_key.txt`
Now we can select some decrypted data…
yugabyte=# SELECT id, name, PGP_PUB_DECRYPT(ssn::bytea, dearmor(:'private_key')) ssn FROM employee;
id | name | ssn
----+------+-----------
1 | Jim | 123456789
2 | Jane | 987654321
(2 rows)
Have Fun!