Re: New types for transparent encryption

In response to Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>:

> Our manual says we can use pgcrypto functions or encrypted filesystems
> for data encryption.
> http://www.postgresql.org/docs/8.4/static/encryption-options.html
>
> However, they are not always the best approaches in some cases.
>
> For pgcrypto functions, user's SQL must contain keyword strings
> and they need to consider which column is encrypted. Users complaint
> that that they want to treat encrypted values as if not-encrypted.
>
> For encrypted filesystems, all of database will be encrypted
> and thare are considerable overheads. In addition, encrypted
> filesystems are not well-maintained on some platforms.
>
>
> I'd like to submit a proposal to add types that encryped or
> decrypted transparently to contrib/pgcrypto. It is a simple
> wrapper type of bytea. The pseudo code could be:
>
> CREATE TYPE encrypted_text (
> INPUT = pgp_sym_encrypt_text(textin($1), passward(), options()),
> OUTPUT = textout(pgp_sym_decrypt_text($1, passward(), options())),
> LIKE bytea
> );
>
> passward() and options() are SQL functions and we can re-define them
> if needed. The default implementations are to refer custom GUC variables
> (pgcrypto.password and pgcrypto.options) so that encryption are done
> only in database server and applications don't have to know the details.
>
>
> I hope this will be an enhancement of contrib/pgcrypto.
> Comments welcome.

As others have noted, I doubt that the overall protection ability would
be very great with such a feature.

What I'd _really_ like to see is native PKI encryption implemented, so
roles could have keys associated with them, and roles could encrypt data
for other roles and sign data, thus allowing some really powerful data
protection schemes.

Just coming up with a methodology for this is non-trivial, though.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/