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
