Feedback-ID: i131946ab:Fastmail
Message-ID: <00b0c4f3-0d9f-dcfd-2ba0-eee5109b4963@enterprisedb.com>
Date: Wed, 25 Jan 2023 20:00:26 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.6.1
Subject: Re: Transparent column encryption
Content-Language: en-US
To: Jacob Champion <jchampion@timescale.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
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>
 <5003d222-5975-38c1-e471-888e642f23aa@timescale.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: <5003d222-5975-38c1-e471-888e642f23aa@timescale.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/00b0c4f3-0d9f-dcfd-2ba0-eee5109b4963%40enterprisedb.com>
Precedence: bulk

On 19.01.23 21:48, Jacob Champion wrote:
> I like the existing "caveats" documentation, and I've attached a sample
> patch with some more caveats documented, based on some of the upthread
> conversation:
> 
> - text format makes fixed-length columns leak length information too
> - you only get partial protection against the Evil DBA
> - RSA-OAEP public key safety
> 
> (Feel free to use/remix/discard as desired.)

I have added those in the v15 patch I just posted.

> When writing the paragraph on RSA-OAEP I was reminded that we didn't
> really dig into the asymmetric/symmetric discussion. Assuming that most
> first-time users will pick the builtin CMK encryption method, do we
> still want to have an asymmetric scheme implemented first instead of a
> symmetric keywrap? I'm still concerned about that public key, since it
> can't really be made public.

I had started coding that, but one problem was that the openssl CLI 
doesn't really provide any means to work with those kinds of keys.  The 
"openssl enc" command always wants to mix in a password.  Without that, 
there is no way to write a test case, and more crucially no way for 
users to set up these kinds of keys.  Unless we write our own tooling 
for this, which, you know, the patch just passed 400k in size.

> For the padding caveat:
> 
>> +      There is no concern if all values are of the same length (e.g., credit
>> +      card numbers).
> 
> I nodded along to this statement last year, and then this year I learned
> that CCNs aren't fixed-length. So with a 16-byte block, you're probably
> going to be able to figure out who has an American Express card.

Heh.  I have removed that parenthetical remark.

> The column encryption algorithm is set per-column -- but isn't it
> tightly coupled to the CEK, since the key length has to match? From a
> layperson perspective, using the same key to encrypt the same plaintext
> under two different algorithms (if they happen to have the same key
> length) seems like it might be cryptographically risky. Is there a
> reason I should be encouraged to do that?

Not really.  I was also initially confused by this setup, but that's how 
other similar systems are set up, so I thought it would be confusing to 
do it differently.

> With the loss of \gencr it looks like we also lost a potential way to
> force encryption from within psql. Any plans to add that for v1?

\gencr didn't do that either.  We could do it.  The libpq API supports 
it.  We just need to come up with some syntax for psql.

> While testing, I forgot how the new option worked and connected with
> `column_encryption=on` -- and then I accidentally sent unencrypted data
> to the server, since `on` means "not enabled". :( The server errors out
> after the damage is done, of course, but would it be okay to strictly
> validate that option's values?

fixed in v15

> Are there plans to document client-side implementation requirements, to
> ensure cross-client compatibility? Things like the "PG\x00\x01"
> associated data are buried at the moment (or else I've missed them in
> the docs). If you're holding off until the feature is more finalized,
> that's fine too.

This is documented in the protocol chapter, which I thought was the 
right place.  Did you want more documentation, or in a different place?

> Speaking of cross-client compatibility, I'm still disconcerted by the
> ability to write the value "hello world" into an encrypted integer
> column. Should clients be required to validate the text format, using
> the attrealtypid?

Well, we can ask them to, but we can't really require them, in a 
cryptographic sense.  I'm not sure what more we can do.

> It occurred to me when looking at the "unspecified" CMK scheme that the
> CEK doesn't really have to be an encryption key at all. In that case it
> can function more like a (possibly signed?) cookie for lookup, or even
> be ignored altogether if you don't want to use a wrapping scheme
> (similar to JWE's "direct" mode, maybe?). So now you have three ways to
> look up or determine a column encryption key (CMK realm, CMK name, CEK
> cookie)... is that a concept worth exploring in v1 and/or the documentation?

I don't completely follow this.