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