Feedback-ID: i131946ab:Fastmail
Message-ID: <8a1ccf22-2a86-f002-bbd8-49f56729a5f3@enterprisedb.com>
Date: Tue, 31 Jan 2023 14:25:55 +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>
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>
 <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>
 <00b0c4f3-0d9f-dcfd-2ba0-eee5109b4963@enterprisedb.com>
 <CAAWbhmibyvZXu3P7vU8FjtUN-Jx86udDVjkt=fNQLuQgOBniXQ@mail.gmail.com>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
In-Reply-To: <CAAWbhmibyvZXu3P7vU8FjtUN-Jx86udDVjkt=fNQLuQgOBniXQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/8a1ccf22-2a86-f002-bbd8-49f56729a5f3%40enterprisedb.com>
Precedence: bulk

On 30.01.23 23:30, Jacob Champion wrote:
>>> 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.
> 
> Which systems let you mix and match keys and algorithms this way? I'd
> like to take a look at them.

See here for example: 
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

>>> 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.
> 
> Do you think people would rather set encryption for all parameters at
> once -- something like \encbind -- or have the ability to mix
> encrypted and unencrypted parameters?

For pg_dump, I'd like a mode that makes all values parameters of an 
INSERT statement.  But obviously not all of those will be encrypted.  So 
I think we'd want a per-parameter syntax.

> More concretely: should psql allow you to push arbitrary text into an
> encrypted \bind parameter, like it does now?

We don't have any data type awareness like that now in psql or libpq. 
It would be quite a change to start now.  How would that deal with data 
type extensibility, is an obvious question to start with.  Don't know.