Feedback-ID: i131946ab:Fastmail
Message-ID: <d26a2144-9b77-7d0b-1206-467d83db87a0@enterprisedb.com>
Date: Tue, 30 Aug 2022 13:40:43 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.13.0
Subject: Re: Transparent column encryption
Content-Language: en-US
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: 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>
 <CAD21AoDs9tOe=OeWWh50v-PG8mydh5YMjS1ZzSyAnTiwuVQYcA@mail.gmail.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: 
 <CAD21AoDs9tOe=OeWWh50v-PG8mydh5YMjS1ZzSyAnTiwuVQYcA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/d26a2144-9b77-7d0b-1206-467d83db87a0%40enterprisedb.com>
Precedence: bulk

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.