Encryption Key Rotation

Key rotation is recommended for security. This procedure shows how to encrypt data with an initial version of a key, then create a new key, encrypt all of the data with the new key, and drop the old key. This cycle of steps is a simple solution for rotating keys periodically.

For example, create a staff table that contains employee ID and social security number (SSN) columns. Assume that the SSN values need to be encrypted and protected from decryption by some users. In this case, both the ID values and the SSN values are unique. If your encrypted table does not have a unique ID, you can generate one by using a sequence.

Create the staff table:

premdb=# create table staff(id bigint, ssn varchar(100));
CREATE TABLE

Insert some rows:

premdb=# insert into staff values(91234,'111-01-0987');
INSERT 0 1
premdb=# insert into staff values(85673, '222-99-4321');
INSERT 0 1
premdb=# select * from staff;
  id   |     ssn     
-------+-------------
 91234 | 111-01-0987
 85673 | 222-99-4321
(2 rows)

Create a key:

premdb=# create key ssnkey0 secret '0f5689f32';
CREATE KEY

Create staff_ks, an encrypted version of the staff table:
```
premdb=# create table staff_ks(id bigint, ssn varchar(100));
CREATE TABLE
```
Note: Make sure the column that will hold encrypted values is wide enough. When a social security number, for example, is encrypted, it will require a much wider column.

Insert rows into the encrypted table,

premdb=# insert into staff_ks(ssn) 
select concat('v0', '-', encrypt_ks(ssn, ssnkey0)) from staff;
INSERT 0 2
premdb=# select * from staff_ks;                                                                                                                                   id   |        ssn         
-------+--------------------
 91234 | v0-w70ijTHGijjZrh0
 85673 | v0-vBmijvHIizDX|p0
(2 rows)

Use a prefix of the form xy- for the encrypted strings. In this example, v0- is the prefix. The hyphen character is never used in encrypted strings, so a prefix that ends with - is a good choice.

Decrypt the encrypted values to verify that encryption and decryption are working.

premdb=# select decrypt_ks(substr(ssn,4),ssnkey0) from staff_ks;
 decrypt_ks  
-------------
 111-01-0987
 222-99-4321
(2 rows)

Add some new rows to the staff table.

premdb=# truncate table staff;
TRUNCATE TABLE
premdb=# insert into staff values(13579, '451-90-3421');
INSERT 0 1
premdb=# insert into staff values(97684, '455-66-2356');
INSERT 0 1
premdb=# select * from staff;
  id   |     ssn     
-------+-------------
 13579 | 451-90-3421
 97684 | 455-66-2356
(2 rows)

Create a new key:

premdb=# create key ssnkey1 secret '41f55e912f';
CREATE KEY
premdb=# select * from sys.key;
 key_id |   name   | schema_id | owner_id |         creation_time         
--------+----------+-----------+----------+-------------------------------
  16450 | ssnkey0  |      2200 |    16444 | 2019-11-12 11:52:51.871322-08
  16516 | ssnkey1  |      2200 |    16444 | 2019-11-13 13:28:03.204117-08
(4 rows)

Update the encrypted table, using the new key, ssnkey1, for the new rows.

premdb=# insert into staff_ks(id, ssn) 
select id, concat('v1', '-', encrypt_ks(ssn, ssnkey1)) from staff;
INSERT 0 2
premdb=# select * from staff_ks;
  id   |        ssn         
-------+--------------------
 13579 | v1-hHUpDsQqa1yDwA3
 97684 | v1-hHUoDAwra5CCzM3
 91234 | v0-w70ijTHGijjZrh0
 85673 | v0-vBmijvHIizDX|p0
(4 rows)

Decrypt the rows with a CASE expression that applies the appropriate key to the v0 and v1 rows.

premdb=# select id, case left(ssn,3)
when 'v0-' then decrypt_ks(substr(ssn,4), ssnkey0)
when 'v1-' then decrypt_ks(substr(ssn,4), ssnkey1)
else 'missing key'
end
from staff_ks;
  id   |    case     
-------+-------------
 13579 | 451-90-3421
 97684 | 455-66-2356
 91234 | 111-01-0987
 85673 | 222-99-4321
(4 rows)

Update the staff_ks table so that the original old key, ssnkey0, is no longer needed. The first update encrypts the v0- values with the new key and removes the v0 prefix. The second statement removes the v1- prefix from the rows that are already encrypted with the new key.

premdb=# update staff_ks 
set ssn=encrypt_ks(decrypt_ks(substr(ssn,4),ssnkey0),ssnkey1) 
where left(ssn,3)='v0-';
UPDATE 2
premdb=# select * from staff_ks;
  id   |        ssn         
-------+--------------------
 91234 | k1UpDIAqaDiEmI3
 85673 | jDkpDsAsaTCCwA3
 13579 | v1-hHUpDsQqa1yDwA3
 97684 | v1-hHUoDAwra5CCzM3
(4 rows)

premdb=# update staff_ks 
set ssn=substr(ssn,4) 
where left(ssn,3)='v1-';
UPDATE 2
premdb=# select * from staff_ks;
  id   |       ssn       
-------+-----------------
 13579 | hHUpDsQqa1yDwA3
 97684 | hHUoDAwra5CCzM3
 91234 | k1UpDIAqaDiEmI3
 85673 | jDkpDsAsaTCCwA3
(4 rows)

Drop the old key.

premdb=# drop key ssnkey0 cascade;
DROP KEY