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 11:47:11 -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]>
	<[email protected]>



> On Jan 10, 2023, at 9:26 AM, Mark Dilger <[email protected]> wrote:
> 
>    -- 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)),

As you mention upthread, tying columns together creates problems for statements that only operate on a subset of columns.  Allowing schema designers a choice about tying the encrypted column to zero or more other columns allows them to choose which works best for their security needs.

The example above would make a statement like "UPDATE patient_record SET patient_record = $1 \bind '{some json whatever}'" raise an exception at the libpq client level, but maybe that's what schema designers wants it to do.  If not, they should omit the column_encryption_salt option in the create table statement; but if so, they should expect to have to specify the other columns as part of the update statement, possibly as part of the where clause, like

	UPDATE patient_record
		SET patient_record = $1
		WHERE patient_id = 12345
		  AND patient_ssn = '111-11-1111' 
		\bind '{some json record}'

and have the libpq get the salt column values from the where clause (which may be tricky to implement), or perhaps use some new bind syntax like

	UPDATE patient_record
		SET patient_record = ($1:$2,$3)   -- new, wonky syntax
		WHERE patient_id = $2
		  AND patient_ssn = $3 
		\bind '{some json record}' 12345 '111-11-1111'

which would be error prone, since the sql statement could specify the ($1:$2,$3) inconsistently with the where clause, or perhaps specify the "new" salt columns even when not changed, like

	UPDATE patient_record
		SET patient_record = $1, patient_id = 12345, patient_ssn = "111-11-1111"
		WHERE patient_id = 12345
		  AND patient_ssn = "111-11-1111"
		\bind '{some json record}'

which looks kind of nuts at first glance, but is grammatically consistent with cases where one or both of the patient_id or patient_ssn are also being changed, like

	UPDATE patient_record
		SET patient_record = $1, patient_id = 98765, patient_ssn = "999-99-9999"
		WHERE patient_id = 12345
		  AND patient_ssn = "111-11-1111"
		\bind '{some json record}'

Or, of course, you can ignore these suggestions or punt them to some future patch that extends the current work, rather than trying to get it all done in the first column encryption commit.  But it seems useful to think about what future directions would be, to avoid coding ourselves into a corner, making such future work harder.

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

view thread (3+ messages)  latest in thread

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