Re: Transparent column encryption

> On Dec 31, 2022, at 6:17 AM, Peter Eisentraut  wrote:
> 
> Another update, with some merge conflicts resolved. 

Hi Peter, thanks for the patch!

I wonder if logical replication could be made to work more easily with this feature.  Specifically, subscribers of encrypted columns will need the encrypted column encryption key (CEK) and the name of the column master key (CMD) as exists on the publisher, but getting access to that is not automated as far as I can see. It doesn't come through automatically as part of a subscription, and publisher's can't publish the pg_catalog tables where the keys are kept (because publishing system tables is not supported.)  Is it reasonable to make available the CEK and CMK to subscribers in an automated fashion, to facilitate setting up logical replication with less manual distribution of key information?  Is this already done, and I'm just not recognizing that you've done it?


Can we do anything about the attack vector wherein a malicious DBA simply copies the encrypted datum from one row to another?  Imagine the DBA Alice wants to murder a hospital patient Bob by removing the fact that Bob is deathly allergic to latex.  She cannot modify the Bob's encrypted and authenticated record, but she can easily update Bob's record with the encrypted record of a different patient Charlie.  Likewise, if she want's Bob to pay Charlie's bill, she can replace Charlie's encrypted credit card number with Bob's, and once Bob is dead, he won't dispute the charges.

An encrypted-and-authenticated column value should be connected with its row in some way that Alice cannot circumvent.  In the patch as you have it written, the client application can include row information in the patient record (specifically, the patient's name, ssn, etc) and verify when any patient record is retrieved that this information matches.  But that's hardly "transparent" to the client.  It's something all clients will have to do, and easy to forget to do in some code path.  Also, for encrypted fixed-width columns, it is not an option.  So it seems the client needs to "salt" (maybe not the right term for what I have in mind) the encryption with some relevant other columns, and that's something the libpq client would need to understand, and something the patch's syntax needs to support.  Something like:

CREATE TABLE patient_records (
    -- Cryptographically connected to the encrypted record
    patient_id  BIGINT NOT NULL,
    patient_ssn CHAR(11),

    -- The encrypted record
    patient_record TEXT ENCRYPTED WITH (column_encryption_key = cek1,
                                        column_encryption_salt = (patient_id, patient_ssn)),

    -- Extra stuff, not cryptographically connected to anything
    next_of_kin TEXT,
    phone_number BIGINT,
    ...
);

I have not selected any algorithms that include such "salt"ing (again, maybe the wrong word) because I'm just trying to discuss the general feature, not get into the weeds about which cryptographic algorithm to select.

Thoughts?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company