Message-ID: <ab0e5a96-c46c-66cf-4822-ad47a0ac6240@enterprisedb.com>
Date: Tue, 7 Dec 2021 16:06:55 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.3.2
Subject: Re: Transparent column encryption
Content-Language: en-US
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
References: <89157929-c2b6-817b-6025-8e4b2d89d88f@enterprisedb.com>
 <CA+Tgmob_xikGmJg9gYjc7fAX3a9RzAZhnziKqoAQgdfNjCADjw@mail.gmail.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: 
 <CA+Tgmob_xikGmJg9gYjc7fAX3a9RzAZhnziKqoAQgdfNjCADjw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ab0e5a96-c46c-66cf-4822-ad47a0ac6240%40enterprisedb.com>
Precedence: bulk

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.