Re: 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 04/09/2014 02:52 PM, 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.
>
>
>
How does that affect backend sql reporting?\

>
> On 04/09/2014 02:52 PM, 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?
>
> 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.

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.

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(at)svana(dot)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

On 4/9/2014 2:07 PM, Rob Sargent wrote:
>> 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?\

does this backend sql reporting system need access to the contents of
this encrypted data (presumably credit card numbers or some such ?) if
so, then it too would need to be able to decrypt the data and would have
to possess the decryption key(s).

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

On Wed, Apr 9, 2014 at 2:32 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> 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
>

Well the needing to check a box on a checklist was the starting point for
me looking into this. I think the scenario would be "what if someone stole
your hard disks?" (Or stole Rackspace's hard disk, in my case.) I didn't
dig too deep, but it seemed that there was/is a basic tradeoff--either the
encryption key is accessible from the server and thus the filesystem can be
conveniently and automatically mounted,but providing little extra security,
or 2) the encryption key is user supplied at boot time, providing a good
deal extra security but way less convenience.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing
list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
to
learn more about AGENCY or
follow the discussion.

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(at)momjian(dot)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(at)consistentstate(dot)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 encryption and decryption using keys specific to the authenticated user? It seems like a reasonable way to implement an encryption layer that would provide protection against a number of threats without requiring modification to the higher layers of 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