Contents
Transparent Column Encryption
Transparent Column Encryption (TCE) lets you encrypt a column for storage to disk. This pattern is often called "Encryption at Rest". The column is stored encrypted in the postgres database files, as well as log streams and database dumps. TCE uses Server Key Management managed keys by ID.
python
%load_ext sql
python
%config SqlMagic.feedback=False
%config SqlMagic.displaycon=False
%sql postgresql://postgres@/
To use TCE, first create the extension:
sql
%%sql
CREATE EXTENSION IF NOT EXISTS pgsodium;
[]
Encrypt Whole Column with One Key ID
To encrypt a column, the first step is to create a table. Here is a simple table with one text
column that will be encrypted.
sql
%%sql
DROP TABLE IF EXISTS my_secrets CASCADE;
CREATE TABLE my_secrets (
secret text
);
[]
Create a new Key ID
The next step is create a single key id that will be used to encrypt the column with the pgsodium.create_key()
function. This new key is used to create a label for the column with a SECURITY LABEL
command that says which key id should be used to encrypt the table.
python
key = %sql SELECT * FROM pgsodium.create_key()
label = 'ENCRYPT WITH KEY ID ' + str(key[0].id)
print('The security label will be:', label)
The security label will be: ENCRYPT WITH KEY ID 3b72130d-32c1-42a2-8ed4-c35086ca60e8
Label a column
Now apply that label to the column:
sql
%%sql
SECURITY LABEL FOR pgsodium ON COLUMN my_secrets.secret IS :label;
[]
You can examine all labeled objects in the standard Postgre catalog table pg_catalog.pg_seclabel
:
sql
%%sql
SELECT objoid::regclass, provider, label FROM pg_seclabel WHERE provider = 'pgsodium';
objoid | provider | label |
---|---|---|
pgsodium.key | pgsodium | ENCRYPT WITH KEY COLUMN parent_key ASSOCIATED (id, associated_data) NONCE raw_key_nonce |
my_customer_secrets | pgsodium | DECRYPT WITH VIEW public.other_name_view |
my_customer_secrets | pgsodium | ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (id, associated_data, owner) NONCE nonce |
my_secrets | pgsodium | ENCRYPT WITH KEY ID 3b72130d-32c1-42a2-8ed4-c35086ca60e8 |
Insert test data
TCE works by dynamically creating an INSERT trigger for the labled table and a view that wraps the table and decrypts the column. To explain, here are some test rows for the table. Note that the inserted secret values are plaintext.
sql
%%sql
INSERT INTO my_secrets (secret) VALUES ('sekert1'), ('shhhhh'), ('0xABC_my_payment_processor_key');
[]
How Secrets are Stored
Now that there are some secrets in the table, selecting on the table will show that the data is stored in an authenticated encrypted form. The "signature" for authenticated the secret is appended to the value, which is why each value is 32 bytes longer.
sql
%%sql
SELECT * FROM my_secrets;
secret |
---|
ZpIusOfIP76sHzOTTWhPn6S0GNz14K2bmZFR2KRzHrYTRLCW8NO8 |
V96NMLHu6nhnJHw1AXA/+zsDpk8Hq7NY7DbbtBQjqG9TQ6t9dF4= |
9z2kXtIYOgSNl/fcZEXqz44baRpVyFhV/jH/yp/jP/FbpR1Qle0oDIZrMF0iCyomTtGsKEKPioWZ wf64xVs= |
Accessing Decrypted Values
When a column is labled with TCE using SECURITY LABEL
, pgsodium dynamically generate a view that can decrypt rows on the fly. By default this view is named decrypted_<table_name>
for the table with any labeled columns.
sql
%%sql
SELECT * FROM decrypted_my_secrets;
secret | decrypted_secret |
---|---|
ZpIusOfIP76sHzOTTWhPn6S0GNz14K2bmZFR2KRzHrYTRLCW8NO8 | sekert1 |
V96NMLHu6nhnJHw1AXA/+zsDpk8Hq7NY7DbbtBQjqG9TQ6t9dF4= | shhhhh |
9z2kXtIYOgSNl/fcZEXqz44baRpVyFhV/jH/yp/jP/FbpR1Qle0oDIZrMF0iCyomTtGsKEKPioWZ wf64xVs= |
0xABC_my_payment_processor_key |
Using per-Row Key Ids, Associated Data, and Nonces
The above approach is simple, there is one key to manage and it is used for the whole column. But in many cases you will want finer grained control over which key is applied to which row. For example if you host customer content, you may require a different key for different customers.
Furthermore authenticated encryption is useful, but you often have data associated with a secret that does not need to be encrypted but does need to be authenticated meaning that the associated data is included in the generation of the authentication signature. See the wikipedia page Authenticated Encryption with Assocaited Data for more information on this technique. pgsodium lets you specify one or more columns that can be associated with a secret as shown below.
Finally, a common pattern in encryption is to use a nonce value to deduplicate secrets and associated data. Without this nonce, duplicate secrets and associated data would create duplicate encrypted values, and this information can be used by attackers in some situations.
To put it all together, lets create another table for customer secrets:
sql
%%sql
DROP TABLE IF EXISTS my_customer_secrets CASCADE;
CREATE TABLE my_customer_secrets (
id bigserial,
secret text,
associated_data json,
owner text NOT NULL,
key_id uuid REFERENCES pgsodium.key(id) DEFAULT (pgsodium.create_key()).id,
nonce bytea DEFAULT pgsodium.crypto_aead_det_noncegen());
[]
sql
%%sql
SECURITY LABEL FOR pgsodium ON TABLE my_customer_secrets IS 'DECRYPT WITH VIEW public.other_name_view';
[]
Notice that in this case there some new columns, an integer id, an owner, some "associated" data in JSON format, and a nonce. These columns will be used in the security label below. Notice also that the key_id
and nonce
columns have defaults, so that if you don't provide a value for them, a new key_id and nonce will by automatically generated.
The new label has different syntax than the first example, instead of specifying a KEY ID
the label specifies a KEY COLUMN
and some optional ASSOCIATED
data columns, of which there may be one or more, and a NONCE
column.
sql
%%sql
SECURITY LABEL FOR pgsodium ON COLUMN my_customer_secrets.secret
IS 'ENCRYPT WITH KEY COLUMN key_id ASSOCIATED (id, associated_data, owner) NONCE nonce';
[]
Insert some test data:
sql
%%sql
INSERT INTO my_customer_secrets (secret, associated_data, owner)
VALUES ('blue', '{"type":"color"}', 'bob'),
('nuts', '{"type":"food"}', 'alice'),
('fast', '{"type":"car"}', 'mallory');
[]
As above, secret is now stored en anthenticated encrypted form. The id
, associated_data
, and owner
columns are "mixed in" to the signature that is stored with the secret, so they cannot be forged. Any atttempt to decrypt the secret with inauthentic associated data will fail.
sql
%%sql
SELECT secret, associated_data, owner, key_id FROM my_customer_secrets;
secret | associated_data | owner | key_id |
---|---|---|---|
GrDDDYZXzBvAjKWWeVrCBtt8ngVM3t+k4JFd37ll0EtGtREB | {'type': 'color'} | bob | bb04c8f9-c9ff-4978-a39d-afe44ca98e9a |
N7427oX6/ENTnuYuk4FfGdo1tldjiZI/wF4Hk9nT97Um+xSm | {'type': 'food'} | alice | fa0050ed-2876-4f98-b677-0000cf5fffb4 |
AV2GSzBDgAO71TqCXUKJD4U3Bd7H55rfH3grNEElO6w3uajE | {'type': 'car'} | mallory | eb9291a2-2cf2-4ce3-8e99-e002b47b9d57 |
Decrypted secret access the view by the name specified in the table label above.
sql
%%sql
SELECT decrypted_secret, associated_data, owner, key_id FROM other_name_view;
decrypted_secret | associated_data | owner | key_id |
---|---|---|---|
blue | {'type': 'color'} | bob | bb04c8f9-c9ff-4978-a39d-afe44ca98e9a |
nuts | {'type': 'food'} | alice | fa0050ed-2876-4f98-b677-0000cf5fffb4 |
fast | {'type': 'car'} | mallory | eb9291a2-2cf2-4ce3-8e99-e002b47b9d57 |