Thread: [GENERAL] PG and database encryption
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.
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. *****
On Tue, Aug 22, 2017 at 2:48 PM, rakeshkumar464 <rakeshkumar464@outlook.com> 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
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
On 08/22/2017 01:08 PM, John McKown wrote: > On Tue, Aug 22, 2017 at 2:48 PM, rakeshkumar464 > <rakeshkumar464@outlook.com> 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
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.
On Tue, Aug 22, 2017 at 3:27 PM, rakeshkumar464 <rakeshkumar464@outlook.com> 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
On Tue, 22 Aug 2017 12:48:13 -0700 (MST) rakeshkumar464 <rakeshkumar464@outlook.com> 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 <wmoran@potentialtech.com>
On Tue, Aug 22, 2017 at 3:13 PM, PT <wmoran@potentialtech.com> wrote: > On Tue, 22 Aug 2017 12:48:13 -0700 (MST) > rakeshkumar464 <rakeshkumar464@outlook.com> 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.