Re: New types for transparent encryption

Itagaki Takahiro wrote:
> 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.

What kind of attacks would this protect against? Seems a bit pointless
to me if the password is being sent to the server anyway. If the
attacker has superuser access to the server, he can harvest the
passwords as the clients send them in. If he doesn't, the usual access
controls with GRANT/REVOKE would be enough.

I would see some value in this if the encryption was done in the client,
and the server never saw any decrypted values. That would reduce the
damage of a compromised server. A driver extension to do that
transparently would be neat.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com