public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mark Dilger <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Transparent column encryption
Date: Tue, 10 Jan 2023 09:26:53 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>



> On Dec 31, 2022, at 6:17 AM, Peter Eisentraut <[email protected]> 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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Transparent column encryption
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox