To ensure that the secret keys are never visible in database logs, write a wrapper function to get the keys from the file. You need to do it in an untrusted embedded language, such as PL/PythonU, as only untrusted languages can access the filesystem. You need to be a PostgreSQL superuser in order to create functions in untrusted languages. It's not difficult to write a PostgreSQL function that reads a text file. For convenience, here is an example that requires PL/PythonU:
create or replace function get_my_public_key() returns text as $$
return open('/home/pguser/public.key').read()
$$
language plpythonu;
revoke all on function get_my_public_key() from public;
create or replace function get_my_secret_key() returns text as $$
return open('/home/pguser/secret.key').read()
$$
language plpythonu;
revoke all on function get_my_secret_key() from public;
This can also be fully implemented in PL/pgSQL using the built-in PostgreSQL system function, pg_read_file (filename), and you don't have to bother with PL/PythonU at all. However, to use this function, you must place the files in the data directory as required by that function for additional security, so the database superuser is not allowed to access the rest of the filesystem directly.
If you don't want other database users to be able to see the keys, you also need to write wrapper functions for encryption and decryption, and then give access to these wrapper functions to end users.
The encryption function can be like this:
create or replace function encrypt_using_my_public_key(
cleartext text,
ciphertext out bytea
)
AS $$
DECLARE
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;
The decryption function can be 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;
Finally, we test the encryption:
test2=# select encrypt_using_my_public_key(‘X marks the spot!’);
This function returns a bytea (that is, raw binary) result that looks something like the following:
encrypt_using_my_public_key |
\301\301N\003\223o\215\2125\203\252;\020\007\376-z\233\211H...
To see that it actually works, you must go 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!