Re: Transparent column encryption

On 20.07.22 08:12, Masahiko Sawada wrote:
> ---
> Regarding the documentation, I'd like to have a page that describes
> the generic information of the transparent column encryption for users
> such as what this feature actually does, what can be achieved by this
> feature, CMK rotation, and its known limitations. The patch has
> "Transparent Column Encryption" section in protocol.sgml but it seems
> to be more internal information.

I have added more documentation in the v6 patch.

> ---
> In datatype.sgml, it says "Thus, clients that don't support
> transparent column encryption or have disabled it will see the
> encrypted values as byte arrays." but I got an error rather than
> encrypted values when I tried to connect to the server using by
> clients that don't support the encryption:
> 
> postgres(1:6040)=# select * from tbl;
> no CMK lookup found for realm ""

This has now been improved in v6.  The protocol changes need to be 
activated explicitly at connection time, so if you use a client that 
doesn't support it or activates it, you get the described behavior.

> ---
> In single-user mode, the user cannot decrypt the encrypted value but
> probably it's fine in practice.

Yes, there is nothing really to do about that.

> ---
> Regarding the column master key rotation, would it be useful if we
> provide a tool for that? For example, it takes old and new CMK as
> input, re-encrypt all CEKs realted to the CMK, and registers them to
> the server.

I imagine users using a variety of key management systems, so I don't 
see how a single tool would work.  But it's something we can think about 
in the future.

> ---
> Is there any convenient way to load a large amount of test data to the
> encrypted columns? I tried to use generate_series() but it seems not
> to work as it generates the data on the server side:

No, that doesn't work, by design.  You'd have to write a client program 
to generate the data.

> I've also tried to load the data from a file on the client by using
> \copy command, but it seems not to work:
> 
> postgres(1:80556)=# copy (select generate_series(1, 1000)::text) to
> '/tmp/tmp.dat';
> COPY 1000
> postgres(1:80556)=# \copy a from '/tmp/tmp.dat'
> COPY 1000
> postgres(1:80556)=# select * from a;
> out out memory

This was a bug that I have fixed.

> ---
> I got SEGV in the following two situations:

I have fixed these.