Re: Transparent column encryption

On 15.07.22 19:47, Jacob Champion wrote:
>> The CEK key
>> material is in turn encrypted by an assymmetric key called the column
>> master key (CMK).
>
> I'm not yet understanding why the CMK is asymmetric.

I'm not totally sure either. I started to build it that way because
other systems were doing it that way, too. But I have been thinking
about adding a symmetric alternative for the CMKs as well (probably AESKW).

I think there are a couple of reasons why asymmetric keys are possibly
useful for CMKs:

Some other products make use of secure enclaves to do computations on
(otherwise) encrypted values on the server. I don't fully know how that
works, but I suspect that asymmetric keys can play a role in that. (I
don't have any immediate plans for that in my patch. It seems to be a
dying technology at the moment.)

Asymmetric keys gives you some more options for how you set up the keys
at the beginning. For example, you create the asymmetric key pair on
the host where your client program that wants access to the encrypted
data will run. You put the private key in an appropriate location for
run time. You send the public key to another host. On that other host,
you create the CEK, encrypt it with the CMK, and then upload it into the
server (CREATE COLUMN ENCRYPTION KEY). Then you can wipe that second
host. That way, you can be even more sure that the unencrypted CEK
isn't left anywhere. I'm not sure whether this method is very useful in
practice, but it's interesting.

In any case, as I mentioned above, this particular aspect is up for
discussion.

Also note that if you use a KMS (cmklookup "run" method), the actual
algorithm doesn't even matter (depending on details of the KMS setup),
since you just tell the KMS "decrypt this", and the KMS knows by itself
what algorithm to use. Maybe there should be a way to specify "unknown"
in the ckdcmkalg field.

>> +#define PG_CEK_AEAD_AES_128_CBC_HMAC_SHA_256 130
>> +#define PG_CEK_AEAD_AES_192_CBC_HMAC_SHA_384 131
>> +#define PG_CEK_AEAD_AES_256_CBC_HMAC_SHA_384 132
>> +#define PG_CEK_AEAD_AES_256_CBC_HMAC_SHA_512 133
>
> It looks like these ciphersuites were abandoned by the IETF. Are there
> existing implementations of them that have been audited/analyzed? Are
> they safe (and do we know that the claims made in the draft are
> correct)? How do they compare to other constructions like AES-GCM-SIV
> and XChacha20-Poly1305?

The short answer is, these same algorithms are used in equivalent
products (see MS SQL Server, MongoDB). They even reference the same
exact draft document.

Besides that, here is my analysis for why these are good choices: You
can't use any of the counter modes, because since the encryption happens
on the client, there is no way to coordinate to avoid nonce reuse. So
among mainstream modes, you are basically left with AES-CBC with a
random IV. In that case, even if you happen to reuse an IV, the
possible damage is very contained.

And then, if you want to use AEAD, you combine that with some MAC, and
HMAC is just as good as any for that.

The referenced draft document doesn't really contain any additional
cryptographic insights, it's just a guide on a particular way to put
these two together.

So altogether I think this is a pretty solid choice.

>> +-- \gencr
>> +-- (This just tests the parameter passing; there is no encryption here.)
>> +CREATE TABLE test_gencr (a int, b text);
>> +INSERT INTO test_gencr VALUES (1, 'one') \gencr
>> +SELECT * FROM test_gencr WHERE a = 1 \gencr
>> + a | b
>> +---+-----
>> + 1 | one
>> +(1 row)
>> +
>> +INSERT INTO test_gencr VALUES ($1, $2) \gencr 2 'two'
>> +SELECT * FROM test_gencr WHERE a IN ($1, $2) \gencr 2 3
>> + a | b
>> +---+-----
>> + 2 | two
>> +(1 row)
> I'd expect \gencr to error out without sending plaintext. I know that
> under the hood this is just setting up a prepared statement, but if I'm
> using \gencr, presumably I really do want to be encrypting my data.
> Would it be a problem to always set force-column-encryption for the
> parameters we're given here? Any unencrypted columns could be provided
> directly.

Yeah, this needs a bit of refinement. You don't want something named
"encr" but it only encrypts some of the time. We could possibly do what
you suggest and make it set the force-encryption flag, or maybe rename
it or add another command that just uses prepared statements and doesn't
promise anything about encryption from its name.

This also ties in with how pg_dump will eventually work. I think by
default pg_dump will just dump things encrypted and set it up so that
COPY writes it back encrypted. But there should probably be a mode that
dumps out plaintext and then uses one of these commands to load the
plaintext back in. What these psql commands need to do also depends on
what pg_dump needs them to do.

>> + <para>
>> + Null values are not encrypted by transparent column encryption; null values
>> + sent by the client are visible as null values in the database. If the fact
>> + that a value is null needs to be hidden from the server, this information
>> + needs to be encoded into a nonnull value in the client somehow.
>> + </para>
>
> This is a major gap, IMO. Especially with the switch to authenticated
> ciphers, because it means you can't sign your NULL values. And having
> each client or user that's out there solve this with a magic in-band
> value seems like a recipe for pain.
>
> Since we're requiring "canonical" use of text format, and the docs say
> there are no embedded or trailing nulls allowed in text values, could we
> steal the use of a single zero byte to mean NULL? One additional
> complication would be that the client would have to double-check that
> we're not writing a NULL into a NOT NULL column, and complain if it
> reads one during decryption. Another complication would be that the
> client would need to complain if it got a plaintext NULL.

You're already alluding to some of the complications. Also consider
that null values could arise from, say, outer joins. So you could be in
a situation where encrypted and unencrypted null values coexist. And of
course the server doesn't know about the encrypted null values. So how
do you maintain semantics, like for aggregate functions, primary keys,
anything that treats null values specially? How do clients deal with a
mix of encrypted and unencrypted null values, how do they know which one
is real. What if the client needs to send a null value back as a
parameter? All of this would create enormous complications, if they can
be solved at all.

I think a way to look at this is that this column encryption feature
isn't suitable for disguising the existence or absence of data, it can
only disguise the particular data that you know exists.

>> + <para>
>> + The <quote>associated data</quote> in these algorithms consists of 4
>> + bytes: The ASCII letters <literal>P</literal> and <literal>G</literal>
>> + (byte values 80 and 71), followed by the algorithm ID as a 16-bit unsigned
>> + integer in network byte order.
>> + </para>
>
> Is this AD intended as a placeholder for the future, or does it serve a
> particular purpose?

It has been recommended that you include the identity of the encryption
algorithm in the AD. This protects the client from having to decrypt
stuff that wasn't meant to be decrypted (in that way).