Re: [GENERAL] PG and database encryption

[Stephen Frost]

Greetings,

* Scott Marlowe (scott.marl...@gmail.com) wrote:
> On Tue, Aug 22, 2017 at 3:13 PM, PT  wrote:
> > On Tue, 22 Aug 2017 12:48:13 -0700 (MST)
> > rakeshkumar464  wrote:
> >> We have a requirement to encrypt the entire database.  What is the best 
> >> tool
> >> to accomplish this. Our primary goal is that it should be transparent to 
> >> the
> >> application, with no change in the application, as compared to un-encrypted
> >> database. Reading about pgcrypto module, it seems it is good for few 
> >> columns
> >> only and using it to encrypt entire database is not a good use-case.
> >>
> >> Is this which can be done best by file level encryption?  What are the good
> >> tools on Linux (RHES), preferably open-source.

There's been discussion and work to add the ability to have encryption
at the database instance level instead requiring on filesystem-level or
harddisk-level encryption, but nothing has gone into PG yet to support
this.  I'm hopeful that there will be progress towards this in the next
round of PG development, ideally landing in PG11.

> > On multiple occasions, I demonstrated that an unecrypted database was the
> > least likely disclosure vector for sensative data, and that we shouldn't
> > waste any time on it until we had ensured that all other breach vectors had
> > been fixed.  Over the course of 4 years at that job, we never managed to get
> > all the other (more likely) breach vectors secured.

While that's interesting, I don't find it particularly relevant- fix the
breach vectors you can reasonably.  If you're worried that backups or
hard drives might not be properly handled and may fall into an
attacker's hands and adding encryption to address that is
straight-forward and simple to do then it's entirely reasonable to do
so, even if your application isn't as secure as you'd like it to be.

> > As a result, my opinion would be that you use filesystem encryption. It's
> > very efficient, low management overhead, and proven technology that doesn't
> > interfere with anything else you're doing. You can then check that box on
> > whatever form you have to fill out and the beaurocrats will leave you alone.
> > On top of that, it effectivley protects againts possible breach vectors that
> > don't require changing the application.

If PG-level encryption was also efficient, low management overhead and
based on proven technology that didn't interfere with anything else then
it would be an entirely good thing to consider doing.  We don't have
that today, but that doesn't make it unreasonable to ask for.

> > Real security will require changing the application. But take my word for 
> > it,
> > nobody wants to hear the list of breach vectors that can only be fixed by
> > modifying the application. Because people aren't interested in real 
> > security,
> > they're just interested in checking boxes on a form.

If it's trivial to address a certain set of attack vectors, then it
makes sense to do so, even if it's a relatively unlikely vector to be
hit with.  I agree that non-trivial efforts to address unlikely attack
vectors is a bad idea when there are more likely vectors to consider
addressing first.

> This. Without a much stricter definition of the attack vectors you're
> trying to defeat "encrypt the whole database" is a very hand-wavy
> proposition. Are you protecting against people getting into the data
> center and stealing your hard drives? Rogue applications getting
> access to the db? Someone sniffing the passwords or unencrypting them
> on the servers etc etc.

This continues to come up in discussions which outline the attack
vectors that database-level encryption addresses.  We know the vectors
which database/filesystem-level encryption addresses, we don't need to
beat up people asking for it by telling them that they don't know- let's
just make it clear that there's only a subset of vectors addressed by
database of filesystem encryption and ensure that everyone understands
what those are and then individuals can weigh the choice themselves as
to if it makes sense for their particular situation to address those
vectors through database/filesystem-level encryption or not.

> OP: It's just generic a requirement to take seriously. Sit down, come
> up with possible attack vectors and possible ways to thwart them.

This is certainly a good exercise, but "someone stole the backups" is
absolutely an entirely reasonable attack vector to consider and
database-level encryption is one approach which could (if implemented
properly) address that vector.  There are certainly other approaches to
address that vector as well, of course, such as using backup technology
which provides its own encryption, though that requires managing a
different set of keys possibly, or run the backup through GPG or similar
but that gets painful quickly, et al.

> Security isn't something you do one time and you're done, it's a
> constant process of design, 

Re: [GENERAL] PG and database encryption

[Condor]

On 22-08-2017 22:48, rakeshkumar464 wrote:
We have a requirement to encrypt the entire database.  What is the best 
tool
to accomplish this. Our primary goal is that it should be transparent 
to the
application, with no change in the application, as compared to 
un-encrypted
database. Reading about pgcrypto module, it seems it is good for few 
columns

only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the 
good

tools on Linux (RHES), preferably open-source.

Thanks



--
View this message in context:
http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.



Hello,
I also was interesting about this topic. My research take me to two 
problems that can be happened
if you encrypt your hard drive partition where is stored postgresql 
data. Postgresql does not support

encryption like oracle or mssql.

The problems that I mentored is two and they are connected with 
filesystem:


1. If some is happened on your filesystem and encrypted drive need to be 
force checked. It's can damage your files.
2. If LURKS is used, if problem is happened (bad sector, cluster 
problem) and that problem / bad sector is
there where is stored your LURKS header encryption data, you cannot 
mount your encrypted partition and

if you does not have experiences what to do, your data is lost forever.

My data is too important and because I don't have much time to make more 
researches, I get decision not to use encryption.
I think there is hardware named TDS or was IDS but may be is deprecated 
but Im not sure.


If you realize encryption somehow, drop us or me email with information.



Regards,
Hristo S.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[Scott Marlowe]

On Tue, Aug 22, 2017 at 3:13 PM, PT  wrote:
> On Tue, 22 Aug 2017 12:48:13 -0700 (MST)
> rakeshkumar464  wrote:
>
>> We have a requirement to encrypt the entire database.  What is the best tool
>> to accomplish this. Our primary goal is that it should be transparent to the
>> application, with no change in the application, as compared to un-encrypted
>> database. Reading about pgcrypto module, it seems it is good for few columns
>> only and using it to encrypt entire database is not a good use-case.
>>
>> Is this which can be done best by file level encryption?  What are the good
>> tools on Linux (RHES), preferably open-source.
>
> "encrypt the database" is bullshit wank terminology for "we're a government
> agency and don't know what we're talking about"
>
> On multiple occasions, I demonstrated that an unecrypted database was the
> least likely disclosure vector for sensative data, and that we shouldn't
> waste any time on it until we had ensured that all other breach vectors had
> been fixed.  Over the course of 4 years at that job, we never managed to get
> all the other (more likely) breach vectors secured.
>
> While it's possible that you've already fixed all other breach
> vectors, I'd be willing to bet actual money that you have not.
> The very fact that you ask for something that "is transparent to the
> application" tells me that you're not going to actually implement it
> effectively anyway.
>
> As a result, my opinion would be that you use filesystem encryption. It's
> very efficient, low management overhead, and proven technology that doesn't
> interfere with anything else you're doing. You can then check that box on
> whatever form you have to fill out and the beaurocrats will leave you alone.
> On top of that, it effectivley protects againts possible breach vectors that
> don't require changing the application.
>
> Real security will require changing the application. But take my word for it,
> nobody wants to hear the list of breach vectors that can only be fixed by
> modifying the application. Because people aren't interested in real security,
> they're just interested in checking boxes on a form.

This. Without a much stricter definition of the attack vectors you're
trying to defeat "encrypt the whole database" is a very hand-wavy
proposition. Are you protecting against people getting into the data
center and stealing your hard drives? Rogue applications getting
access to the db? Someone sniffing the passwords or unencrypting them
on the servers etc etc.

OP: It's just generic a requirement to take seriously. Sit down, come
up with possible attack vectors and possible ways to thwart them.
Security isn't something you do one time and you're done, it's a
constant process of design, review, updates, and education.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[PT]

On Tue, 22 Aug 2017 12:48:13 -0700 (MST)
rakeshkumar464  wrote:

> We have a requirement to encrypt the entire database.  What is the best tool
> to accomplish this. Our primary goal is that it should be transparent to the
> application, with no change in the application, as compared to un-encrypted
> database. Reading about pgcrypto module, it seems it is good for few columns
> only and using it to encrypt entire database is not a good use-case.
> 
> Is this which can be done best by file level encryption?  What are the good
> tools on Linux (RHES), preferably open-source.

"encrypt the database" is bullshit wank terminology for "we're a government
agency and don't know what we're talking about"

On multiple occasions, I demonstrated that an unecrypted database was the
least likely disclosure vector for sensative data, and that we shouldn't
waste any time on it until we had ensured that all other breach vectors had
been fixed.  Over the course of 4 years at that job, we never managed to get
all the other (more likely) breach vectors secured.

While it's possible that you've already fixed all other breach
vectors, I'd be willing to bet actual money that you have not.
The very fact that you ask for something that "is transparent to the
application" tells me that you're not going to actually implement it
effectively anyway.

As a result, my opinion would be that you use filesystem encryption. It's
very efficient, low management overhead, and proven technology that doesn't
interfere with anything else you're doing. You can then check that box on
whatever form you have to fill out and the beaurocrats will leave you alone.
On top of that, it effectivley protects againts possible breach vectors that
don't require changing the application.

Real security will require changing the application. But take my word for it,
nobody wants to hear the list of breach vectors that can only be fixed by
modifying the application. Because people aren't interested in real security,
they're just interested in checking boxes on a form.

-- 
PT 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[John McKown]

On Tue, Aug 22, 2017 at 3:27 PM, rakeshkumar464
 wrote:
> Thanks John and JD.
>
> John: Are you telling that the backup of a database has no protection?

If you use LUKS to encrypt a filesystem and then copy any file data on
that filesystem to another file on an unencrypted filesystem, then the
copy is not encrypted. You'd need to use something like gpg2 to
encrypt it before storing. The same if you dumped the database using a
utility such as pg_dump. I don't know of a way to encrypt a database
such that it is unencrypted transparently for the PostgreSQL server,
but not for some other application which can access the physical
files. And even if this were possible, the pg_dump would output
unencrypted data. This is basically due to your requirement that the
PostgreSQL client (application) not supply a password to PostgreSQL to
do the decryption. Of course, you could embed the
encryption/decryption into the application itself so that the data is
encrypted before it is passed to PostgreSQL to store. But I think that
violates your original requirements.


-- 
If you look around the poker table & don't see an obvious sucker, it's you.

Maranatha! <><
John McKown


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[rakeshkumar464]

Thanks John and JD.

John: Are you telling that the backup of a database has no protection?  



--
View this message in context: 
http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618p5979624.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[Paul Jungwirth]

On 08/22/2017 01:08 PM, John McKown wrote:

On Tue, Aug 22, 2017 at 2:48 PM, rakeshkumar464
 wrote:

We have a requirement to encrypt the entire database.


Personally, what I'd do (and actually do at work) is to us LUKS.


I second that, although I'll add that if you're on AWS you can also use 
encrypted EBS volumes. You get a very similar effect, except all you 
need to do is tick a checkbox (or set a CloudFormation attribute, etc.). 
Also you can get unattended reboots without storing the key somewhere 
vulnerable. There may be perf advantages too; I'm not sure.


Good luck!
Paul


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[Ron Johnson]

On 08/22/2017 02:55 PM, Joshua D. Drake wrote:

On 08/22/2017 12:48 PM, rakeshkumar464 wrote:

We have a requirement to encrypt the entire database.  What is the best tool
to accomplish this. Our primary goal is that it should be transparent to the
application, with no change in the application, as compared to un-encrypted
database. Reading about pgcrypto module, it seems it is good for few columns
only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the good
tools on Linux (RHES), preferably open-source.


If you are encrypting the entire database, use the filesystem.

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security_Guide/chap-Security_Guide-Encryption.html 



But that's protection against stolen laptops.

--
World Peace Through Nuclear Pacification



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[John McKown]

On Tue, Aug 22, 2017 at 2:48 PM, rakeshkumar464
 wrote:
> We have a requirement to encrypt the entire database.  What is the best tool
> to accomplish this. Our primary goal is that it should be transparent to the
> application, with no change in the application, as compared to un-encrypted
> database. Reading about pgcrypto module, it seems it is good for few columns
> only and using it to encrypt entire database is not a good use-case.
>
> Is this which can be done best by file level encryption?  What are the good
> tools on Linux (RHES), preferably open-source.
>
> Thanks

In addition to the link that Joshua gave you, there is this:
https://www.enterprisedb.com/blog/postgres-and-transparent-data-encryption-tde

Personally, what I'd do (and actually do at work) is to us LUKS. This
is a "full disk encryption". When the filesystem is mounted, the
system asks for the password. Unfortunately, this method allows all
users who have the proper authority (UNIX & SELinux) to read (maybe
write) the underlying files. Of course, a properly secured environment
would not allow this, but systems can be hacked. And it does not
address any off-filesystem backups, which would need to be separately
encrypted. LUKS is a good method, IMO, to protect the data if the
media is stolen, but not for protecting the individual files from
improper access. SELinux is pretty good at that.


-- 
If you look around the poker table & don't see an obvious sucker, it's you.

Maranatha! <><
John McKown


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PG and database encryption

[Joshua D. Drake]

On 08/22/2017 12:48 PM, rakeshkumar464 wrote:

We have a requirement to encrypt the entire database.  What is the best tool
to accomplish this. Our primary goal is that it should be transparent to the
application, with no change in the application, as compared to un-encrypted
database. Reading about pgcrypto module, it seems it is good for few columns
only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the good
tools on Linux (RHES), preferably open-source.


If you are encrypting the entire database, use the filesystem.

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security_Guide/chap-Security_Guide-Encryption.html

Thanks!

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, opinions are my own.   *


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PG and database encryption

[rakeshkumar464]

We have a requirement to encrypt the entire database.  What is the best tool
to accomplish this. Our primary goal is that it should be transparent to the
application, with no change in the application, as compared to un-encrypted
database. Reading about pgcrypto module, it seems it is good for few columns
only and using it to encrypt entire database is not a good use-case.

Is this which can be done best by file level encryption?  What are the good
tools on Linux (RHES), preferably open-source.

Thanks



--
View this message in context: 
http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general