Thread: pgcrypto question

pgcrypto question

From
Erik Aronesty
Date:
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



Re: pgcrypto question

From
Tomas Vondra
Date:
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



Re: pgcrypto question

From
Erik Aronesty
Date:
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



Re: pgcrypto question

From
Tomas Vondra
Date:
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



Re: pgcrypto question

From
Erik Aronesty
Date:
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