Security - PostgreSQL 9 Administration - page 142

Now export the keys:
pguser@laptop:~$ gpg -a --export "PostgreSQL User (test key for PG
Cookbook) <[e-mail]>" > public.key
pguser@laptop:~$ gpg -a --export-secret-keys " PostgreSQL User (test
key for PG Cookbook) <[e-mail]>" > secret.key
Make sure only you and postgres database users have access to the secret key.
pguser@laptop:~$ sudo chgrp postgres secret.key
pguser@laptop:~$ chmod 440 secret.key
pguser@laptop:~$ ls -l *.key
-rw-r--r-- 1 pguser pguser 1718 2010-03-26 13:53 public.key
-r--r----- 1 pguser postgres 1818 2010-03-26 13:54 secret.key
How to do it...
Encrypting
To ensure that the secret keys are never visible in database logs, write a wrapper function
for getting the keys from the file. You need to do it in a trusted embedded language, such
as pl/pythonu, as only trusted languages can access file system. You need to be postgresql
superuser in order to create functions in trusted languages.
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;
If you don't want other database users to actually 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 is as follows:
create or replace function encrypt_using_my_public_key(
cleartext text,
ciphertext out bytea
)
AS $$
DECLARE