Re: Page-Level Encryption

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