Re: Transparent column encryption

On 06.12.21 19:28, Robert Haas wrote:
>> Speaking of parameter descriptions, the trickiest part of this whole
>> thing appears to be how to get transparently encrypted data into the
>> database (as opposed to reading it out). It is required to use
>> protocol-level prepared statements (i.e., extended query) for this.
> Why? If the client knows the CEK, can't the client choose to send
> unprepared insert or update statements with pre-encrypted blobs? That
> might be a bad idea from a security perspective because the encrypted
> blob might then got logged, but we sometimes log parameters, too.

The client can send something like

PQexec(conn, "INSERT INTO tbl VALUES ('ENCBLOB', 'ENCBLOB')");

and it will work. (See the included test suite where 'ENCBLOB' is
actually computed by pgcrypto.) But that is not transparent encryption.
The client wants to send "INSERT INTO tbl VALUES ('val1', 'val2')" and
have libpq take care of encrypting 'val1' and 'val2' before hitting the
wire. For that you need to use the prepared statement API so that the
values are available separately from the statement. And furthermore the
client needs to know what columns the insert statements is writing to,
so that it can get the CEK for that column. That's what it needs the
parameter description for.

As alluded to, workarounds exist or might be made available to do part
of that work yourself, but that shouldn't be the normal way of using it.