Database Reference
In-Depth Information
pubkey_bin bytea;
BEGIN
-- text version of public key needs to be passed through function
dearmor() to get to raw key
pubkey_bin := dearmor(get_my_public_key());
ciphertext := pgp_pub_encrypt(cleartext, pubkey_bin);
END;
$$ language plpgsql security definer;
revoke all on function encrypt_using_my_public_key(text) from public;
grant execute on function encrypt_using_my_public_key(text) to bob;
And the decryption function is as follows:
create or replace function decrypt_using_my_secret_key(
ciphertext bytea,
cleartext out text
)
AS $$
DECLARE
secret_key_bin bytea;
BEGIN
-- text version of secret key needs to be passed through function
dearmor() to get to raw binary key
secret_key_bin := dearmor(get_my_secret_key());
cleartext := pgp_pub_decrypt(ciphertext, secret_key_bin);
END;
$$ language plpgsql security definer;
revoke all on function decrypt_using_my_secret_key(bytea) from public;
grant execute on function decrypt_using_my_secret_key(bytea) to bob;
And now, test the encryption:
test2=# select encrypt_using_my_public_key('X marks the spot!');
returns a byte result that looks something like the following:
encrypt_using_my_public_key | \301\301N\003\22
3o\215\2125\203\252;\020\007\376-z\233\211H...
To see that it actually works both ways:
test2=# select decrypt_using_my_secret_key(encrypt_using_my_public_
key('X marks the spot!'));
decrypt_using_my_secret_key
-----------------------------
X marks the spot!
(1 row)
Yes, we got back our initial string.
