Re: pgcrypto question

On Mon, Oct 07, 2019 at 12:05:16PM -0400, Erik Aronesty wrote:
>Currently, it is my understanding that the pgcrypto library requires
>the user to send a password or private key up to the server for
>decryption.
>

Correct. In the naive case the key is included in each SQL query, which
does have various issues. Bruce Momjian has a nice extension that allows
you to fix that by loading the key into backend memory:

http://momjian.us/download/pgcryptokey/

>Is there a notion of a client-side encrypt/decrypt plugin when doing a
>postgres query?
>
>For example, a user could query postgres, get back data of type
>"encrypted", and a "libpq" plugin could decode/decrypt those columns
>that are of data type "encrypted".... in a manner transparent to the
>user of the client....
>
>Obviously I could write this by intercepting the relevant libpq calls
>using LD_PRELOAD or Microsoft's "Detours" ... but it there a better
>way to do that?
>

AFAIk that's usually done at the application level, i.e. the application
is sending/receiving encrypted data, and the database simply sees bytea
columns. I'm not aware of a driver doing that transparently, but it
seems like an interesting idea - I wonder if it could be done e.g. in
psycopg as an extension, or something like that.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services