Thread: pgcrypto - real life examples to encrypt / decrypt

pgcrypto - real life examples to encrypt / decrypt

From
Vikas Sharma
Date:
Dear Experts,

Could you please share some real life examples of using pgcrypto in production?

I am planning to use it in our environment and wondering what could be the best practice for its use.

Thank you. Regards
Vikas S

Re: pgcrypto - real life examples to encrypt / decrypt

From
Adrian Klaver
Date:
On 8/2/21 2:14 PM, Vikas Sharma wrote:
> Dear Experts,
> 
> Could you please share some real life examples of using pgcrypto in 
> production?
> 
> I am planning to use it in our environment and wondering what could be 
> the best practice for its use.

I would start by doing a search on 'using pgcrypto', that will return 
articles/blogs with pointers.

> 
> Thank you. Regards
> Vikas S


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: pgcrypto - real life examples to encrypt / decrypt

From
Vikas Sharma
Date:
Thanks Adrian, 

I will check them out.

Regards
Vikas S

On Mon, 2 Aug 2021 at 22:22, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 8/2/21 2:14 PM, Vikas Sharma wrote:
> Dear Experts,
>
> Could you please share some real life examples of using pgcrypto in
> production?
>
> I am planning to use it in our environment and wondering what could be
> the best practice for its use.

I would start by doing a search on 'using pgcrypto', that will return
articles/blogs with pointers.

>
> Thank you. Regards
> Vikas S


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: pgcrypto - real life examples to encrypt / decrypt

From
Luca Ferrari
Date:
On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma <shavikas@gmail.com> wrote:
>
> Dear Experts,
>
> Could you please share some real life examples of using pgcrypto in production?
>
> I am planning to use it in our environment and wondering what could be the best practice for its use.

It is not clear what you are going to do and which kind of encryption
you are going to use.
For a symmetric encryption this could be a starting point:

UPDATE secret
                SET secret_text = pgp_sym_encrypt( clear_text,
                                                   'A-Strong-Secret-Password' );

I do remember there was an extension made to overtake pgcrypto, but
currently I don't remember the name.

Luca



Re: pgcrypto - real life examples to encrypt / decrypt

From
Vikas Sharma
Date:
Thank you Luca,

For now I have seen the below:

pgp_pub_encrypt -- using public gpg key
pgp_pub_decrypt -- using secret gpg key

Select crypt('test', gen_salt('md5'));

Select PGP_SYM_DECRYPT(PGP_SYM_ENCRYPT('Some data','Secret password','compress-algo=1, cipher-algo=aes256'),'Secret password');

My question is, can I use the gpg public/secret key instead of the 'Secret password' in above PGP_Sym_encrypt/decrypt?  I can create a wrapper function to read the public/secret keys to hide it from appearing as clear text.

still researching how to encrypt a column with sensitive data as a best practice to use in OLTP production with minimal impact on performance.

Regards
Vikas S

On Tue, 3 Aug 2021 at 11:03, Luca Ferrari <fluca1978@gmail.com> wrote:
On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma <shavikas@gmail.com> wrote:
>
> Dear Experts,
>
> Could you please share some real life examples of using pgcrypto in production?
>
> I am planning to use it in our environment and wondering what could be the best practice for its use.

It is not clear what you are going to do and which kind of encryption
you are going to use.
For a symmetric encryption this could be a starting point:

UPDATE secret
                SET secret_text = pgp_sym_encrypt( clear_text,
                                                   'A-Strong-Secret-Password' );

I do remember there was an extension made to overtake pgcrypto, but
currently I don't remember the name.

Luca

Re: pgcrypto - real life examples to encrypt / decrypt

From
Luca Ferrari
Date:
On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma <shavikas@gmail.com> wrote:
> My question is, can I use the gpg public/secret key instead of the 'Secret password' in above
PGP_Sym_encrypt/decrypt? I can create a wrapper function to read the public/secret keys to hide it from appearing as
cleartext.
 

I think you are looking for something like:

pgp_pub_encrypt( clear_text,
               dearmor( '-----BEGIN PGP PUBLIC KEY BLOCK-----
                        ...
                        -----END PGP PUBLIC KEY BLOCK-----' ) );


>
> still researching how to encrypt a column with sensitive data as a best practice to use in OLTP production with
minimalimpact on performance.
 

Clearly, as you add more stuff to do, performances will be lower. I
strongly recommend you to analyze if column encryption is really what
you need for your purposes, because in my little experience it is
often too much work with regard to other approaches (e.g., disk and
backup encryption).

Luca



Re: pgcrypto - real life examples to encrypt / decrypt

From
Joe Conway
Date:
On 8/3/21 8:43 AM, Luca Ferrari wrote:
> On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma <shavikas@gmail.com> wrote:
>> My question is, can I use the gpg public/secret key instead of the 'Secret password' in above
PGP_Sym_encrypt/decrypt? I can create a wrapper function to read the public/secret keys to hide it from appearing as
cleartext.
 
> 
> I think you are looking for something like:
> 
> pgp_pub_encrypt( clear_text,
>                 dearmor( '-----BEGIN PGP PUBLIC KEY BLOCK-----
>                          ...
>                          -----END PGP PUBLIC KEY BLOCK-----' ) );
> 
> 
>>
>> still researching how to encrypt a column with sensitive data as a best practice to use in OLTP production with
minimalimpact on performance.
 
> 
> Clearly, as you add more stuff to do, performances will be lower. I
> strongly recommend you to analyze if column encryption is really what
> you need for your purposes, because in my little experience it is
> often too much work with regard to other approaches (e.g., disk and
> backup encryption).

Generally agreed. This topic is vast and complex and probably beyond 
what most people want to discuss by typing (at least for me) ;-)

That said, you might find this extension written by Bruce Momjian useful:

https://momjian.us/download/pgcryptokey/

HTH,

Joe
-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



Re: pgcrypto - real life examples to encrypt / decrypt

From
Vikas Sharma
Date:
Thanks you Guys, 

These are very helpful pointers. I will go away and see how much depth I do need.

Regards
Vikas S.

On Tue, 3 Aug 2021 at 14:36, Joe Conway <mail@joeconway.com> wrote:
On 8/3/21 8:43 AM, Luca Ferrari wrote:
> On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma <shavikas@gmail.com> wrote:
>> My question is, can I use the gpg public/secret key instead of the 'Secret password' in above PGP_Sym_encrypt/decrypt?  I can create a wrapper function to read the public/secret keys to hide it from appearing as clear text.
>
> I think you are looking for something like:
>
> pgp_pub_encrypt( clear_text,
>                 dearmor( '-----BEGIN PGP PUBLIC KEY BLOCK-----
>                          ...
>                          -----END PGP PUBLIC KEY BLOCK-----' ) );
>
>
>>
>> still researching how to encrypt a column with sensitive data as a best practice to use in OLTP production with minimal impact on performance.
>
> Clearly, as you add more stuff to do, performances will be lower. I
> strongly recommend you to analyze if column encryption is really what
> you need for your purposes, because in my little experience it is
> often too much work with regard to other approaches (e.g., disk and
> backup encryption).

Generally agreed. This topic is vast and complex and probably beyond
what most people want to discuss by typing (at least for me) ;-)

That said, you might find this extension written by Bruce Momjian useful:

https://momjian.us/download/pgcryptokey/

HTH,

Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: pgcrypto - real life examples to encrypt / decrypt

From
Reid Thompson
Date:
someone mentioned an alternative earlier that they couldn't remember -- i think it may have been pgsodium that they
werethinking of https://github.com/michelp/pgsodium
 


Re: pgcrypto - real life examples to encrypt / decrypt

From
"Theodore M Rolle, Jr."
Date:
How do those of us who compile PostgreSQL from source get libsodium into the mix?

On Fri, Aug 6, 2021 at 2:27 PM Reid Thompson <Reid.Thompson@omnicell.com> wrote:
someone mentioned an alternative earlier that they couldn't remember -- i think it may have been pgsodium that they were thinking of https://github.com/michelp/pgsodium



--
 GnuPG/PGP key: 0xDD4276BA
 +-----------------------------------------------------------------------------------------------------+
 | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510          |
 |   58209 74944[59230 78164]06286 20899 86280 +----------------------------------|
 |   34825 34211 70679*82148 08651 32823 06647 |    May the spirit                |
 |   09384 46095 50582 23172 53594 08128 48111  |      of π spread                |
 |   74502 84102 70193 85211 05559 64462 29489 |    around the world.         |
 |   54930 38196 44288 10975 66593 34461 28475 |      PI VOBISCUM!          |
 |   38196 44288 10975 66593 34461 28475 64823 +---------------------------------|
 |   37867 83165 27120 19091 45648 56692 34603 48610 45432 6648...         |
 +----------------------------------------------------------------------------------------------------+

Re: pgcrypto - real life examples to encrypt / decrypt

From
Adrian Klaver
Date:
On 8/6/21 12:23 PM, Theodore M Rolle, Jr. wrote:
> How do those of us who compile PostgreSQL from source get libsodium into 
> the mix?

Have your package manager install it.

OR

https://doc.libsodium.org/



> 
> On Fri, Aug 6, 2021 at 2:27 PM Reid Thompson <Reid.Thompson@omnicell.com 
> <mailto:Reid.Thompson@omnicell.com>> wrote:
> 
>     someone mentioned an alternative earlier that they couldn't remember
>     -- i think it may have been pgsodium that they were thinking of
>     https://github.com/michelp/pgsodium
>     <https://github.com/michelp/pgsodium>
> 
> 
> 
> -- 
>   GnuPG/PGP key: 0xDD4276BA
>   +-----------------------------------------------------------------------------------------------------+
>   | 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510          |
>   |   58209 74944[59230 78164]06286 20899 86280 
> +----------------------------------|
>   |   34825 34211 70679*82148 08651 32823 06647 |    May the spirit     
>             |
>   |   09384 46095 50582 23172 53594 08128 48111  |      of π spread     
>             |
>   |   74502 84102 70193 85211 05559 64462 29489 |    around the world.   
>        |
>   |   54930 38196 44288 10975 66593 34461 28475 |      PI VOBISCUM!     
>       |
>   |   38196 44288 10975 66593 34461 28475 64823 
> +---------------------------------|
>   |   37867 83165 27120 19091 45648 56692 34603 48610 45432 6648...     
>      |
>   +----------------------------------------------------------------------------------------------------+
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: pgcrypto - real life examples to encrypt / decrypt

From
Luca Ferrari
Date:
On Fri, Aug 6, 2021 at 8:27 PM Reid Thompson <Reid.Thompson@omnicell.com> wrote:
>
> someone mentioned an alternative earlier that they couldn't remember -- i think it may have been pgsodium that they
werethinking of https://github.com/michelp/pgsodium
 
>

Yes, thanks. For some reason I was remembering "selenium" something...

Luca