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.vondra@xxxxxxxxxxxxxxx> 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
