On 1/20/06, David Blewett <david@xxxxxxxxxxxxxxxx> 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
