Re: Transparent column encryption

> On Jan 10, 2023, at 9:26 AM, Mark Dilger <mark.dilger@enterprisedb.com> 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
tochoose 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
haveto 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
somenew 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,
orperhaps 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_idor 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
thantrying to get it all done in the first column encryption commit.  But it seems useful to think about what future
directionswould be, to avoid coding ourselves into a corner, making such future work harder. 

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