Thread: pgcrypto - real life examples to encrypt / decrypt
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
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
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
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
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');
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
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
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
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
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
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... |
+----------------------------------------------------------------------------------------------------+
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
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