Rebased patch, no new functionality.
On 29.06.22 01:29, Peter Eisentraut wrote:
> Here is a new version of this patch. See also the original description
> quoted below. I have done a significant amount of work on this over the
> last few months. Some important news include:
>
> - The cryptography has been improved. It now uses an AEAD scheme, and
> for deterministic encryption a proper SIV construction.
>
> - The OpenSSL-specific parts have been moved to a separate file in
> libpq. Non-OpenSSL builds compile and work (without functionality, of
> course).
>
> - libpq handles multiple CEKs and CMKs, including changing keys on the fly.
>
> - libpq supports a mode to force encryption of certain values.
>
> - libpq supports a flexible configuration system for looking up CMKs,
> including support for external key management systems.
>
> - psql has a new \gencr command that allows passing in bind parameters
> for (potential) encryption.
>
> - There is some more pg_dump and psql support.
>
> - The new data types for storing encrypted data have been renamed for
> clarity.
>
> - Various changes to the protocol compared to the previous patch.
>
> - The patch contains full documentation of the protocol changes,
> glossary entries, and more new documentation.
>
> The major pieces that are still missing are:
>
> - DDL support for registering keys
>
> - Protocol versioning or feature flags
>
> Other than that it's pretty complete in my mind.
>
> For interested reviewers, I have organized the patch so that you can
> start reading it top to bottom: The documentation comes first, then the
> tests, then the code changes. Even some feedback on the first or first
> two aspects would be valuable to me.
>
> Old news follows:
>
> On 03.12.21 22:32, Peter Eisentraut wrote:
>> I want to present my proof-of-concept patch for the transparent column
>> encryption feature. (Some might also think of it as automatic
>> client-side encryption or similar, but I like my name.) This feature
>> enables the {automatic,transparent} encryption and decryption of
>> particular columns in the client. The data for those columns then
>> only ever appears in ciphertext on the server, so it is protected from
>> the "prying eyes" of DBAs, sysadmins, cloud operators, etc. The
>> canonical use case for this feature is storing credit card numbers
>> encrypted, in accordance with PCI DSS, as well as similar situations
>> involving social security numbers etc. Of course, you can't do any
>> computations with encrypted values on the server, but for these use
>> cases, that is not necessary. This feature does support deterministic
>> encryption as an alternative to the default randomized encryption, so
>> in that mode you can do equality lookups, at the cost of some
>> security.
>>
>> This functionality also exists in other SQL database products, so the
>> overall concepts weren't invented by me by any means.
>>
>> Also, this feature has nothing to do with the on-disk encryption
>> feature being contemplated in parallel. Both can exist independently.
>>
>> The attached patch has all the necessary pieces in place to make this
>> work, so you can have an idea how the overall system works. It
>> contains some documentation and tests to help illustrate the
>> functionality. But it's missing the remaining 90% of the work,
>> including additional DDL support, error handling, robust memory
>> management, protocol versioning, forward and backward compatibility,
>> pg_dump support, psql \d support, refinement of the cryptography, and
>> so on. But I think obvious solutions exist to all of those things, so
>> it isn't that interesting to focus on them for now.
>>
>> ------
>>
>> Now to the explanation of how it works.
>>
>> You declare a column as encrypted in a CREATE TABLE statement. The
>> column value is encrypted by a symmetric key called the column
>> encryption key (CEK). The CEK is a catalog object. The CEK key
>> material is in turn encrypted by an assymmetric key called the column
>> master key (CMK). The CMK is not stored in the database but somewhere
>> where the client can get to it, for example in a file or in a key
>> management system. When a server sends rows containing encrypted
>> column values to the client, it first sends the required CMK and CEK
>> information (new protocol messages), which the client needs to record.
>> Then, the client can use this information to automatically decrypt the
>> incoming row data and forward it in plaintext to the application.
>>
>> For the CMKs, the catalog object specifies a "provider" and generic
>> options. Right now, libpq has a "file" provider hardcoded, and it
>> takes a "filename" option. Via some mechanism to be determined,
>> additional providers could be loaded and then talk to key management
>> systems via http or whatever. I have left some comments in the libpq
>> code where the hook points for this could be.
>>
>> The general idea would be for an application to have one CMK per area
>> of secret stuff, for example, for credit card data. The CMK can be
>> rotated: each CEK can be represented multiple times in the database,
>> encrypted by a different CMK. (The CEK can't be rotated easily, since
>> that would require reading out all the data from a table/column and
>> reencrypting it. We could/should add some custom tooling for that,
>> but it wouldn't be a routine operation.)
>>
>> The encryption algorithms are mostly hardcoded right now, but there
>> are facilities for picking algorithms and adding new ones that will be
>> expanded. The CMK process uses RSA-OAEP. The CEK process uses
>> AES-128-CBC right now; a more complete solution should probably
>> involve some HMAC thrown in.
>>
>> In the server, the encrypted datums are stored in types called
>> encryptedr and encryptedd (for randomized and deterministic
>> encryption). These are essentially cousins of bytea. For the rest of
>> the database system below the protocol handling, there is nothing
>> special about those. For example, encryptedr has no operators at all,
>> encryptedd has only an equality operator. pg_attribute has a new
>> column attrealtypid that stores the original type of the data in the
>> column. This is only used for providing it to clients, so that
>> higher-level clients can convert the decrypted value to their
>> appropriate data types in their environments.
>>
>> Some protocol extensions are required. These should be guarded by
>> some _pq_... setting, but this is not done in this patch yet. As
>> mentioned above, extra messages are added for sending the CMKs and
>> CEKs. In the RowDescription message, I have commandeered the format
>> field to add a bit that indicates that the field is encrypted. This
>> could be made a separate field, and there should probably be
>> additional fields to indicate the algorithm and CEK name, but this was
>> easiest for now. The ParameterDescription message is extended to
>> contain format fields for each parameter, for the same purpose.
>> Again, this could be done differently.
>>
>> Speaking of parameter descriptions, the trickiest part of this whole
>> thing appears to be how to get transparently encrypted data into the
>> database (as opposed to reading it out). It is required to use
>> protocol-level prepared statements (i.e., extended query) for this.
>> The client must first prepare a statement, then describe the statement
>> to get parameter metadata, which indicates which parameters are to be
>> encrypted and how. So this will require some care by applications
>> that want to do this, but, well, they probably should be careful
>> anyway. In libpq, the existing APIs make this difficult, because
>> there is no way to pass the result of a describe-statement call back
>> into execute-statement-with-parameters. I added new functions that do
>> this, so you then essentially do
>>
>> res0 = PQdescribePrepared(conn, "");
>> res = PQexecPrepared2(conn, "", 2, values, NULL, NULL, 0, res0);
>>
>> (The name could obviously be improved.) Other client APIs that have a
>> "statement handle" concept could do this more elegantly and probably
>> without any API changes.
>>
>> Another challenge is that the parse analysis must check which
>> underlying column a parameter corresponds to. This is similar to
>> resorigtbl and resorigcol in the opposite direction. The current
>> implementation of this works for the test cases, but I know it has
>> some problems, so I'll continue working in this. This functionality
>> is in principle available to all prepared-statement variants, not only
>> protocol-level. So you can see in the tests that I expanded the
>> pg_prepared_statements view to show this information as well, which
>> also provides an easy way to test and debug this functionality
>> independent of column encryption.
>>
>> And also, psql doesn't use prepared statements, so writing into
>> encrypted columns currently doesn't work at all via psql. (Reading
>> works no problem.) All the test code currently uses custom libpq C
>> programs. We should think about a way to enable prepared statements
>> in psql, perhaps something like
>>
>> INSERT INTO t1 VALUES ($1, $2) \gg 'val1' 'val2'
>>
>> (\gexec and \gx are already taken.)
>>
>> ------
>>
>> This is not targeting PostgreSQL 15. But I'd appreciate some feedback
>> on the direction. As I mentioned above, a lot of the remaining work
>> is arguably mostly straightforward. Some closer examination of the
>> issues surrounding the libpq API changes and psql would be useful.
>> Perhaps there are other projects where that kind of functionality
>> would also be useful.