Обсуждение: pgcrypto question
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. 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? - Erik
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
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@2ndquadrant.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
On Mon, Oct 07, 2019 at 02:51:30PM -0400, Erik Aronesty wrote: >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. > It's not clear to me how would that know which columns are encrypted, with what key, etc. Because those encrypted columns are essentially just regular bytea columns, so there's no easy way to distinguish them. I'm no psycopg2 expert, but it does have some infrastructure for casting PostgreSQL types to Python types, and I guess that could be used for the encryption. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Actually I found a nice open source product "Acra" ... seems to do the whole thing via a proxy. Now I need to see if I can customize the encryption enough using a plugin (but at least I can fork it and start from there). A proxy encryption system seems to be the right call, then all my client apps can stay the same. On Mon, Oct 7, 2019 at 3:49 PM Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote: > > On Mon, Oct 07, 2019 at 02:51:30PM -0400, Erik Aronesty wrote: > >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. > > > > It's not clear to me how would that know which columns are encrypted, > with what key, etc. Because those encrypted columns are essentially just > regular bytea columns, so there's no easy way to distinguish them. > > I'm no psycopg2 expert, but it does have some infrastructure for casting > PostgreSQL types to Python types, and I guess that could be used for the > encryption. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services