Thread: Page-Level Encryption
I'm not sure if this is the right list for this message; if it's not, let me know and I'll take it up elsewhere. I found this thread today: <http://groups.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/4587283b3b3a5aec> I would be very interested if it's possible to encrypt data in Postgres, at a lower level than individual columns but not as low as the filesystem. I.e., either be able to encrypt a single database or a single table but still be able to use normal SQL against it. I'm designing an IMAP server that will be using Peter Gutmann's Cryptlib to encrypt the message bodies using different keys for each user, and storing it as a binary large object in Postgres. However, I still would like to do full-text indexing of the mail. I would index the message, then encrypt it and store it in the database. This leaves the fulltext index open to attack, however. While the complete message would probably not be reproducible (someone correct me?), a significant portion of it probably could. Having the table containing the index, or the database object, encrypted would protect against system admins, or admins of the postgres installation snooping through the table. Ideally, you would specify a passphrase on startup of the daemon to allow it to initialize that database. This would protect the data from access while the database was shutdown, but the system is still running. Or, it could be tied to the user accounts in Postgres. For example, in my server I'm going to implement it so that when the user is created, a public/private key pair is generated with their passphrase. Then when a message is received for them, encrypt it with their public key. When they log in, their passphrase unlocks their private key enabling the server to decrypt their messages and send them along. Maybe Postgres users could be modified to act similarly: any objects the user creates get encrypted with their public key, and only when they log in can they be decrypted. Anyway, I would like some discussion about the possibilites of adding this to Postgres. David
On Fri, 2006-01-20 at 14:24, David Blewett wrote: > I'm not sure if this is the right list for this message; if it's not, > let me know and I'll take it up elsewhere. I found this thread today: > <http://groups.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/4587283b3b3a5aec> > > I would be very interested if it's possible to encrypt data in > Postgres, at a lower level than individual columns but not as low as > the filesystem. I.e., either be able to encrypt a single database or a > single table but still be able to use normal SQL against it. > > I'm designing an IMAP server that will be using Peter Gutmann's > Cryptlib to encrypt the message bodies using different keys for each > user, and storing it as a binary large object in Postgres. However, I > still would like to do full-text indexing of the mail. I would index > the message, then encrypt it and store it in the database. This leaves > the fulltext index open to attack, however. While the complete message > would probably not be reproducible (someone correct me?), a significant > portion of it probably could. > > Having the table containing the index, or the database object, > encrypted would protect against system admins, IF they've got root, and the unencrypted data or the password / key is on the machine or in memory on it, you've lost. It may make it harder for them to get it, but they can.
> IF they've got root, and the unencrypted data or the password / key is > on the machine or in memory on it, you've lost. It may make it harder > for them to get it, but they can. > This is true but in answer to your question you can use something like cryptfs. Note that you will loose performance. Joshua D. Drake > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
Quoting Scott Marlowe <smarlowe@g2switchworks.com>: >> Having the table containing the index, or the database object, >> encrypted would protect against system admins, > > IF they've got root, and the unencrypted data or the password / key is > on the machine or in memory on it, you've lost. It may make it harder > for them to get it, but they can. The password shouldn't be saved anywhere, it should be entered manually when the application starts. Or, only store it on secure removable media. But it would be better than the options that exist today. You're right; there is no perfect security, especially when the box has been rooted. They would have to get root while the machine is powered on, the database engine is running, and the user was authenticated and logged in. It might be possible to implement a "kill" switch, where upon receipt of a signal the user would be logged out and the memory scrubbed of the private key data. David
Quoting "Joshua D. Drake" <jd@commandprompt.com>: > >> IF they've got root, and the unencrypted data or the password / key is >> on the machine or in memory on it, you've lost. It may make it harder >> for them to get it, but they can. > This is true but in answer to your question you can use something like > cryptfs. Note that you will loose performance. > > Joshua D. Drake I'm looking for something that runs *inside* of Postgres, at a higher level than a loop-back encrypted volume. This way, it would only be available when the database engine was running, and ideally only accessible to an authenticated/logged in user. David
>> This is true but in answer to your question you can use something like >> cryptfs. Note that you will loose performance. >> >> Joshua D. Drake > > > I'm looking for something that runs *inside* of Postgres, at a higher > level than a loop-back encrypted volume. This way, it would only be > available when the database engine was running, and ideally only > accessible to an authenticated/logged in user. Nothing that I know of that would work without custom development. There are of course plenty of libraries. Sincerely, Joshua D. Drake > > David > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
Quoting "Joshua D. Drake" <jd@commandprompt.com>: > >>> This is true but in answer to your question you can use something like >>> cryptfs. Note that you will loose performance. >>> >>> Joshua D. Drake >> >> >> I'm looking for something that runs *inside* of Postgres, at a >> higher level than a loop-back encrypted volume. This way, it would >> only be available when the database engine was running, and ideally >> only accessible to an authenticated/logged in user. > Nothing that I know of that would work without custom development. > There are of course plenty of libraries. > > Sincerely, > > Joshua D. Drake > Just as an aside, this paper is rather interesting: <http://people.csail.mit.edu/akiezun/encrypted-search-report.pdf> Instead of needing the table to be decrypted for ordinary access via SQL, they suggest a new method of indexing encrypted material. In it, they talk about a system (Mofdet) they've developed, but I was unable to locate anything online regarding it. David
On Fri, 2006-01-20 at 14:47, David Blewett wrote: > Quoting Scott Marlowe <smarlowe@g2switchworks.com>: > > >> Having the table containing the index, or the database object, > >> encrypted would protect against system admins, > > > > IF they've got root, and the unencrypted data or the password / key is > > on the machine or in memory on it, you've lost. It may make it harder > > for them to get it, but they can. > > > The password shouldn't be saved anywhere, it should be entered manually > when the application starts. Or, only store it on secure removable > media. But it will be in memory, and root can get anything out of memory that they want to. The only way to keep root on this box from getting it is to put the key on another box that the administrator doesn't have access to. I.e. on the client side of postgresql. If you want to keep root out of your data on your server, you encrypt it before you give it to the server. Then, you can set up a different box with the keys on it that encrypts / decrypts the data there. Now, if you can trust root, then you can do the encryption / decryption on the same box as postgresql. Now, having to enter a password by hand onto that box will keep the data secure should the box itself be stolen. I'd suggest defining the threat model well first, then defining the encryption you'll need based on that. > But it would be better than the options that exist today. There is secure, and there are levels of insecure. Changing which level of insecure you have doesn't give you secure. > You're > right; there is no perfect security, especially when the box has been > rooted. They would have to get root while the machine is powered on, > the database engine is running, and the user was authenticated and > logged in. True. If you trust the REAL sysadmins, but are afraid of the box being stolen, then your methodology is secure. Because you can trust the real admins. If you don't trust your real sysadmins on that box, then you need to do your encryption elsewhere.
On Fri, 2006-01-20 at 14:58, David Blewett wrote: > Quoting "Joshua D. Drake" <jd@commandprompt.com>: > > > > >>> This is true but in answer to your question you can use something like > >>> cryptfs. Note that you will loose performance. > >>> > >>> Joshua D. Drake > >> > >> > >> I'm looking for something that runs *inside* of Postgres, at a > >> higher level than a loop-back encrypted volume. This way, it would > >> only be available when the database engine was running, and ideally > >> only accessible to an authenticated/logged in user. > > Nothing that I know of that would work without custom development. > > There are of course plenty of libraries. > > > > Sincerely, > > > > Joshua D. Drake > > > > Just as an aside, this paper is rather interesting: > <http://people.csail.mit.edu/akiezun/encrypted-search-report.pdf> > > Instead of needing the table to be decrypted for ordinary access via > SQL, they suggest a new method of indexing encrypted material. In it, > they talk about a system (Mofdet) they've developed, but I was unable > to locate anything online regarding it. Please note that there's an awful lot of snake oil for sale in the encryption market. Not saying that's what this is, I haven't read it. And mit tends to be pretty spot on, so I'm not talking about this particular encryption program. just be careful about unsubstantiated claims, as there are plenty of systems that are little more than three card monty games out there.
Quoting Scott Marlowe <smarlowe@g2switchworks.com>: > On Fri, 2006-01-20 at 14:47, David Blewett wrote: >> Quoting Scott Marlowe <smarlowe@g2switchworks.com>: >> >> >> Having the table containing the index, or the database object, >> >> encrypted would protect against system admins, >> > >> > IF they've got root, and the unencrypted data or the password / key is >> > on the machine or in memory on it, you've lost. It may make it harder >> > for them to get it, but they can. >> >> >> The password shouldn't be saved anywhere, it should be entered manually >> when the application starts. Or, only store it on secure removable >> media. > > But it will be in memory, and root can get anything out of memory that > they want to. > > The only way to keep root on this box from getting it is to put the key > on another box that the administrator doesn't have access to. I.e. on > the client side of postgresql. If you want to keep root out of your > data on your server, you encrypt it before you give it to the server. > > Then, you can set up a different box with the keys on it that encrypts / > decrypts the data there. > In reading the documentation of Peter Gutmann's Cryptlib, I came across this section: "The use of crypto devices can also complicate key management, since keys generated or loaded into the device usually can't be extracted again afterwards. This is a security feature that makes external access to the key impossible, and works in the same way as cryptlib's own storing of keys inside it's security perimeter. This means that if you have a crypto device that supports (say) DES and RSA encryption, then to export an encrypted DES key from a context stored in the device, you need to use an RSA context also stored inside the device, since a context located outside the device won't have access to the DES context's key." I'm not familiar with how his library protects keys, but this suggests that it would be possible to use it as a basis for transparent encryption. He later writes, "There can be a significant difference between theoretical and effective security. In theory, we should all be using smart cards and PKI for authentication. However, these measures are so painful to deploy and use that they're almost never employed, making them far less effectively secure than basic usernames and passwords. Security experts tend to focus exclusively on the measures that provide the best (theoretical) security, however sometimes these measures provide very little effective security because they end up being misused, or turned off, or bypassed." David
David Blewett <david@dawninglight.net> writes: > In reading the documentation of Peter Gutmann's Cryptlib, I came > across this section: > "The use of crypto devices can also complicate key management, since > keys generated or loaded into the device usually can't be extracted > again afterwards. This is a security feature that makes external > access to the key impossible, and works in the same way as cryptlib's > own storing of keys inside it's security perimeter. This means that if > you have a crypto device that supports (say) DES and RSA encryption, > then to export an encrypted DES key from a context stored in the > device, you need to use an RSA context also stored inside the device, > since a context located outside the device won't have access to the > DES context's key." > > I'm not familiar with how his library protects keys, but this suggests > that it would be possible to use it as a basis for transparent > encryption. He's talking about hardware crypto devices, which most systems don't have (though they're certainly available). If you don't have one of those, then the key has to be stored in system memory. -Doug
Doug McNaught wrote:
> David Blewett <david@dawninglight.net> writes:
>
> > In reading the documentation of Peter Gutmann's Cryptlib, I came
> > across this section:
> > "The use of crypto devices can also complicate key management, since
> > keys generated or loaded into the device usually can't be extracted
> > again afterwards. This is a security feature that makes external
> > access to the key impossible, and works in the same way as cryptlib's
> > own storing of keys inside it's security perimeter. This means that if
> > you have a crypto device that supports (say) DES and RSA encryption,
> > then to export an encrypted DES key from a context stored in the
> > device, you need to use an RSA context also stored inside the device,
> > since a context located outside the device won't have access to the
> > DES context's key."
> >
> > I'm not familiar with how his library protects keys, but this suggests
> > that it would be possible to use it as a basis for transparent
> > encryption.
>
> He's talking about hardware crypto devices, which most systems don't
> have (though they're certainly available). If you don't have one of
> those, then the key has to be stored in system memory.
FYI, we do have a general encryption documentation section:
http://www.postgresql.org/docs/8.1/static/encryption-options.html
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
I would highly recommend taking a look at how Oracle is handling encryption in the database in 10.2 (or whatever they're calling it). They've done a good job of thinking out how to handle things like managing the keys. I know that Oracle magazine did an article on it recently; you should be able to find that online somewhere. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > I would highly recommend taking a look at how Oracle is handling > encryption in the database in 10.2 (or whatever they're calling it). > They've done a good job of thinking out how to handle things like > managing the keys. > > I know that Oracle magazine did an article on it recently; you should be > able to find that online somewhere. This link? http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html
On 1/21/06, Bricklen Anderson <banderson@presinet.com> wrote: > Jim C. Nasby wrote: > > I would highly recommend taking a look at how Oracle is handling > > encryption in the database in 10.2 (or whatever they're calling it). > > They've done a good job of thinking out how to handle things like > > managing the keys. > > > > I know that Oracle magazine did an article on it recently; you should be > > able to find that online somewhere. > > This link? > http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html Two points about it: 1) Their threat model is very clear - someone gets the backup. 2) They have focused on usbility from inside the database. Thats all good, but IMHO such threat is more profitable to solve by simply feeding pg_dump output to GnuPG. This has one important advantage over Oracle solution - no secret key is needed for regular operation. It is only needed for restore operation. -- marko
jd@commandprompt.com ("Joshua D. Drake") writes:
>> IF they've got root, and the unencrypted data or the password / key is
>> on the machine or in memory on it, you've lost. It may make it harder
>> for them to get it, but they can.
> This is true but in answer to your question you can use something like
> cryptfs. Note that you will loose performance.
cryptfs doesn't forcibly help, because Someone Nefarious who has root
can connect to the box, and get access to the unencrypted mount point
that the postmaster is connected to.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/spreadsheets.html
When you awake, you will remember nothing of what I have told you.
On 1/20/06, David Blewett <david@dawninglight.net> wrote: > I'm not sure if this is the right list for this message; if it's not, > let me know and I'll take it up elsewhere. I found this thread today: > <http://groups.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/4587283b3b3a5aec> > > I would be very interested if it's possible to encrypt data in > Postgres, at a lower level than individual columns but not as low as > the filesystem. I.e., either be able to encrypt a single database or a > single table but still be able to use normal SQL against it. > > I'm designing an IMAP server that will be using Peter Gutmann's > Cryptlib to encrypt the message bodies using different keys for each > user, and storing it as a binary large object in Postgres. However, I > still would like to do full-text indexing of the mail. I would index > the message, then encrypt it and store it in the database. This leaves > the fulltext index open to attack, however. While the complete message > would probably not be reproducible (someone correct me?), a significant > portion of it probably could. First two general points: - If your threat model includes database superusers and machine root, forget server-side encryption. You need to encrypt at the client side or get a trusted box. - If you solution goes into direction of using one key over a whole table, use cryptoloop or similar. Now your concrete proposal: - Why giving restrictive permissions and using views where user can see only own data, does not work for you? - Full text index is going to be pain - you need to restrict users from seeing full table. Ah, one more: - Page-level and per-user do not mix, you need to make up your mind. > Having the table containing the index, or the database object, > encrypted would protect against system admins, or admins of the > postgres installation snooping through the table. Ideally, you would > specify a passphrase on startup of the daemon to allow it to initialize > that database. This would protect the data from access while the > database was shutdown, but the system is still running. Or, it could be > tied to the user accounts in Postgres. Don't give admin rights to untrusted people. > For example, in my server I'm going to implement it so that when the > user is created, a public/private key pair is generated with their > passphrase. Then when a message is received for them, encrypt it with > their public key. When they log in, their passphrase unlocks their > private key enabling the server to decrypt their messages and send them > along. Maybe Postgres users could be modified to act similarly: any > objects the user creates get encrypted with their public key, and only > when they log in can they be decrypted. > > Anyway, I would like some discussion about the possibilites of adding > this to Postgres. Well, starting from 8.1, contrib/pgcrypto does public-private key encryption, including password-protected private keys (OpenPGP). No keygen though, so you need to create keys externally. You could build something on it. -- marko
On Fri, Jan 20, 2006 at 02:06:18PM -0800, Bricklen Anderson wrote: > Jim C. Nasby wrote: > >I would highly recommend taking a look at how Oracle is handling > >encryption in the database in 10.2 (or whatever they're calling it). > >They've done a good job of thinking out how to handle things like > >managing the keys. > > > >I know that Oracle magazine did an article on it recently; you should be > >able to find that online somewhere. > > This link? > http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html Yup. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461