Thread: encrypting data stored in PostgreSQL

Hi All;

We have a client with this requirement:

At rest data must be encrypted with a unique client key

Any thoughts on how to pull this off for PostgreSQL stored data?


Thanks in advance

On 4/9/2014 1:40 PM, CS_DBA wrote:
> Hi All;
>
> We have a client with this requirement:
>
> At rest data must be encrypted with a unique client key
>
> Any thoughts on how to pull this off for PostgreSQL stored data?

encrypt the data in the client application before sending it to the
database server, decrypt it in the client when you need it back.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 4/9/2014 1:40 PM, CS_DBA wrote:

Hi All;

We have a client with this requirement:

At rest data must be encrypted with a unique client key

Any thoughts on how to pull this off for PostgreSQL stored data?

encrypt the data in the client application before sending it to the database server, decrypt it in the client when you need it back.

On Wed, Apr 09, 2014 at 02:16:34PM -0700, Ken Tanzer wrote:
> > Any thoughts on how to pull this off for PostgreSQL stored data?
> >
> > I looked at this a while ago because I have clients who might require this
> in the future.  ISTM you should be able to have your PG data directory
> stored on an encrypted filesystem.  I believe this will decrease
> performance, but I have no idea by how much.

FWIW, I have several databases running on encrypted filesystems. The
performance difference is negligable *if* you have hardware
acceleration for your encryption, which most modern processors have.

Essentially, the processor can encrypt/decrypt data so much faster than
the cost of reading/writing to disk, you don't notice the difference.
There's surely a difference, but if this means you meet your
requirements it's an excellent solution.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

On 4/9/2014 2:16 PM, Ken Tanzer wrote:
> I looked at this a while ago because I have clients who might require
> this in the future.  ISTM you should be able to have your PG data
> directory stored on an encrypted filesystem.  I believe this will
> decrease performance, but I have no idea by how much.
>
> Does anyone else have experience with such a setup, or knowledge of
> how bad the performance hit might be?  Or other factors to take into
> consideration?  Thanks.

whats the threat model this encryption is supposed to solve ?

a encrypted file system has to be mounted and readable as long as the
file system is operational, this implies that any data in it can be read
by anyone with access to that system.

now, if you just need a checkbox saying its encrypted, then whatever, it
hardly matters.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

encrypt the data in the client application before sending it to the database server, decrypt it in the client when you need it back.

How does that affect backend sql reporting?\

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Wed, Apr  9, 2014 at 01:52:51PM -0700, John R Pierce wrote:
> On 4/9/2014 1:40 PM, CS_DBA wrote:
> >Hi All;
> >
> >We have a client with this requirement:
> >
> >At rest data must be encrypted with a unique client key
> >
> >Any thoughts on how to pull this off for PostgreSQL stored data?
>
> encrypt the data in the client application before sending it to the
> database server, decrypt it in the client when you need it back.

I have a presentation that covers some of this:

    Securing PostgreSQL From External Attack
    http://momjian.us/main/presentations/features.html#securing

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

On 9.4.2014 23:28, Martijn van Oosterhout wrote:
> On Wed, Apr 09, 2014 at 02:16:34PM -0700, Ken Tanzer wrote:
>>> Any thoughts on how to pull this off for PostgreSQL stored data?
>>>
>>> I looked at this a while ago because I have clients who might
>> require this in the future. ISTM you should be able to have your PG
>> data directory stored on an encrypted filesystem. I believe this
>> will decrease performance, but I have no idea by how much.
>
> FWIW, I have several databases running on encrypted filesystems. The
> performance difference is negligable *if* you have hardware
> acceleration for your encryption, which most modern processors have.
>
> Essentially, the processor can encrypt/decrypt data so much faster
> than the cost of reading/writing to disk, you don't notice the
> difference. There's surely a difference, but if this means you meet
> your requirements it's an excellent solution.

We're running a number of rather busy PostgreSQL boxes with encryption
at filesystem (or more precisely dm-crypt/LUKS with LVM, IIRC).

Support for encryption acceleration (AES-NI [1]) is an absolute must.

The other thing that is essential for good performance is reasonably
recent kernel. 2.6.x kernels have a single-threaded kcryptd, which means
you can't get more than ~150 MB/s AES-256 (per partition). With other
algorithms it's not much better (say, 170MB/s with AES-128, IIRC).

Somewhere in 3.x (or maybe very late 2.6.x) kcryptd was improved to use
multiple threads - that's a significant improvement, both for throughput
and latencies.

Clearly, it's going to eat (part of) your CPUs, but that's expected. The
encryption still has impact on latencies, but with the multi-threaded
kcryptd it's pretty-much negligible.


regards
Tomas

[1] http://en.wikipedia.org/wiki/AES_instruction_set

On Apr 9, 2014, at 3:40 PM, CS_DBA <cs_dba@consistentstate.com> wrote:

> Hi All;
>
> We have a client with this requirement:
>
> At rest data must be encrypted with a unique client key
>
> Any thoughts on how to pull this off for PostgreSQL stored data?

Does there happen to be a Postgresql proxy, such as a modified pgbouncer, that implements column-wise and/or row-wise
encryptionand decryption using keys specific to the authenticated user? It seems like a reasonable way to implement an
encryptionlayer that would provide protection against a number of threats without requiring modification to the higher
layersof the application or to the Postgresql server. 

Guy

On 09/04/2014 22:40, CS_DBA wrote:
> Hi All;
>
> We have a client with this requirement:
>
> At rest data must be encrypted with a unique client key
>
> Any thoughts on how to pull this off for PostgreSQL stored data?

Some time ago I did this, mostly as an experiment but IIRC it works
decently:
https://bitbucket.org/ivoras/pgenctypes