Re: WIP: Data at rest encryption

On Tue, Jun 7, 2016 at 11:56 PM, Ants Aasma <ants(dot)aasma(at)gmail(dot)com> wrote:
> Hi all,
>
> I have been working on data-at-rest encryption support for PostgreSQL.
> In my experience this is a common request that customers make. The
> short of the feature is that all PostgreSQL data files are encrypted
> with a single master key and are decrypted when read from the OS. It
> does not provide column level encryption which is an almost orthogonal
> feature, arguably better done client side.
>
> Similar things can be achieved with filesystem level encryption.
> However this is not always optimal for various reasons. One of the
> better reasons is the desire for HSM based encryption in a storage
> area network based setup.
>
> Attached to this mail is a work in progress patch that adds an
> extensible encryption mechanism. There are some loose ends left to tie
> up, but the general concept and architecture is at a point where it's
> ready for some feedback, fresh ideas and bikeshedding.

Yes, encryption is really a nice and wanted feature.
Following are my thoughts regarding the approach.

1. Instead of doing the entire database files encryption, how about
providing user an option to protect only some particular tables that
wants the encryption at table/tablespace level. This not only provides
an option to the user, it reduces the performance impact on tables
that doesn't need any encryption. The problem with this approach
is that every xlog record needs to validate to handle the encryption
/decryption, instead of at page level.

2. Instead of depending on a contrib module for the encryption, how
about integrating pgcrypto contrib in to the core and add that as a
default encryption method. And also provide an option to the user
to use a different encryption methods if needs.

3. Currently entire xlog pages are encrypted and stored in the file.
can pg_xlogdump works with those files?

4. For logical decoding, how about the adding a decoding behavior
based on the module to decide whether data to be encrypted/decrypted.

5. Instead of providing passphrase through environmental variable,
better to provide some options to pg_ctl etc.

6. I don't have any idea whether is it possible to integrate the checksum
and encryption in a single shot to avoid performance penalty.

> I would also like to incorporate some database identifier as a salt in
> key setup. However, system identifier stored in control file doesn't
> fit this role well. It gets initialized somewhat too late in the
> bootstrap process, and more importantly, gets changed on pg_upgrade.
> This will make link mode upgrades impossible, which seems like a no
> go. I'm torn whether to add a new value for this purpose (perhaps
> stored outside the control file) or allow setting of system identifier
> via initdb. The first seems like a better idea, the file could double
> as a place to store additional encryption parameters, like key length
> or different cipher primitive.

I feel separate file is better to include the key data instead of pg_control
file.

Regards,
Hari Babu
Fujitsu Australia