Re: Transparent column encryption - Mailing list pgsql-hackers

> On Dec 31, 2022, at 6:17 AM, Peter Eisentraut <peter.eisentraut@enterprisedb.com> 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
encryptedcolumns will need the encrypted column encryption key (CEK) and the name of the column master key (CMD) as
existson the publisher, but getting access to that is not automated as far as I can see. It doesn't come through
automaticallyas part of a subscription, and publisher's can't publish the pg_catalog tables where the keys are kept
(becausepublishing system tables is not supported.)  Is it reasonable to make available the CEK and CMK to subscribers
inan automated fashion, to facilitate setting up logical replication with less manual distribution of key information?
Isthis 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
tolatex.  She cannot modify the Bob's encrypted and authenticated record, but she can easily update Bob's record with
theencrypted record of a different patient Charlie.  Likewise, if she want's Bob to pay Charlie's bill, she can replace
Charlie'sencrypted 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.
Inthe 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
todo 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
somethingthe 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
discussthe 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