Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.300.101.1.3\))
Subject: Re: Transparent column encryption
From: Mark Dilger <mark.dilger@enterprisedb.com>
In-Reply-To: <39414634-723D-43E4-9EC6-7E955201733C@enterprisedb.com>
Date: Tue, 10 Jan 2023 11:47:11 -0800
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <61E7BB91-45B2-48AF-B52F-F114092A06BE@enterprisedb.com>
References: <89157929-c2b6-817b-6025-8e4b2d89d88f@enterprisedb.com>
 <48a9f2c2-4a57-27d8-7c53-16a23a01014e@enterprisedb.com>
 <79f08a39-a7da-5157-cef4-378fb60c18f8@enterprisedb.com>
 <258c5064-437e-f41e-7537-5e8c343c33cc@enterprisedb.com>
 <ddb316e8-a978-ee69-1edd-0ee82ecc6d8b@enterprisedb.com>
 <edb5eb1f-5818-5521-537d-20cf7f470742@enterprisedb.com>
 <6bd99fea-3298-854d-d37f-554151342f36@enterprisedb.com>
 <963aa100-7e78-3463-0645-700eaaa325f2@enterprisedb.com>
 <06830254-6d87-86b2-0280-bf2eee7736a5@enterprisedb.com>
 <a86c8d36-e9c5-cc5b-2c50-cc563d818419@enterprisedb.com>
 <75f394fa-f539-1875-079c-c654deceed41@enterprisedb.com>
 <8bcedb49-1496-0755-bd24-1cbabf30ec84@enterprisedb.com>
 <d9dd3634-6154-b0b7-f3a9-0d86dad7b8ff@enterprisedb.com>
 <bdb6433c-2d4e-8ae3-3bda-913f0b53fbb8@enterprisedb.com>
 <39414634-723D-43E4-9EC6-7E955201733C@enterprisedb.com>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Archived-At: <https://www.postgresql.org/message-id/61E7BB91-45B2-48AF-B52F-F114092A06BE%40enterprisedb.com>
Precedence: bulk


> On Jan 10, 2023, at 9:26 AM, Mark Dilger =
<mark.dilger@enterprisedb.com> wrote:
>=20
>    -- Cryptographically connected to the encrypted record
>    patient_id  BIGINT NOT NULL,
>    patient_ssn CHAR(11),
>=20
>    -- The encrypted record
>    patient_record TEXT ENCRYPTED WITH (column_encryption_key =3D cek1,
>                                        column_encryption_salt =3D =
(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 =3D $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 =3D $1
		WHERE patient_id =3D 12345
		  AND patient_ssn =3D '111-11-1111'=20
		\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 =3D ($1:$2,$3)   -- new, wonky syntax
		WHERE patient_id =3D $2
		  AND patient_ssn =3D $3=20
		\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 =3D $1, patient_id =3D 12345, =
patient_ssn =3D "111-11-1111"
		WHERE patient_id =3D 12345
		  AND patient_ssn =3D "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 =3D $1, patient_id =3D 98765, =
patient_ssn =3D "999-99-9999"
		WHERE patient_id =3D 12345
		  AND patient_ssn =3D "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.

=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company