Re: pgcrypto - real life examples to encrypt / decrypt

[Vikas Sharma <shavikas@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma <shavikas@xxxxxxxxx> 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