Re: pgcrypto question

Good idea for "psycopg". It would be easy for a POC, but I think the
only meaningful layer to operate at would be a libpq drop-in
replacement that intercepts PQgetvalue, PQprepare, PQexecParams,
PQexecPrepared ... etc. That way odbc, python, node, etc would "just
work".... as long as you used LD_PRELOAD appropriately.

I never like building things that way though... it would be super cool
if libpq optionally supported client-side plugins too, but maybe some
day if there are more use cases.

On Mon, Oct 7, 2019 at 2:08 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> 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