Is it possible to do something like this to encrypt using a gnupg
public key

insert into junk Values (1, 'fred smith', pgp_pub_encrypt ('A secret',
'/home/postgres/public.test.key'));

I would like the public key to sit on the server .... and the private
key to sit on the client on a USB jumpdrive or on a CD ... That way
the data in the database is secure. Is there anyway to do this?

muHaarib <muHaarib (AT) gmail (DOT) com> wrote:
There is the 'pgcrypto' contrib module which contains functions
pgp_pub_encrypt and pgp_pub_encrypt_bytea which do something very similar
to what you want.

You can easily write code around that to achieve your described goal.

If you want to access the file system on the server, your function will
have to run with superuser privileges.
Why don't you store the public keys in a database table?

Yours,
Laurenz Albe

On May 2, 1:24 am, Laurenz Albe <inv... (AT) spam (DOT) to.invalid> wrote:
You are correct I can write code in Perl,Python,Java whatever ... I
was just being lazy. I was hoping to be able to actually use the
pgp_pub_encrypt and have the key away from the DB. This way if the DB
gets compromised the data does not.

muHaarib <muHaarib (AT) gmail (DOT) com> wrote:
I don't think that you actually mistrust the database, else you wouldn't
consider using it :^)

It's certainly some added effort to import the public key into the database,
but I think that once this is done, it should never change, else
your encrypted data are lost anyway.

I am not sure what you mean by 'compromised'.

If you want the key on the server file system, that's ok. It's not so hard to
write such a function. You can use pg_file_read() to read the contents
of the file into a text variable.

Yours,
Laurenz Albe

On May 2, 8:29 am, Laurenz Albe <inv... (AT) spam (DOT) to.invalid> wrote:
I have it ...
To encrypt I can do this

pgsql test
test# \set pubkey '\ `cat /home/postgres/public.test.ascii.key` '\
test# insert into junk Values (1, 'fred smith', pgp_pub_encrypt ('A
secret', dearmor(ubkey)));

Then I can go to a different machine

pgsql -h machineA -U postgres test
test# \set privkey '\ `cat /cdrom/cdrom0/private.test.ascii.key` '\
test# select num,data,pgp_pub_decrypt(encrypted,dearmor(rivke y),
'password') from junk where data like '%smith%';
num | data | pgp_pub_decrypt
-----+------------+-----------------
1 | fred smith | A secret
(1 row)

Now I can remove my cdrom with the private key when I am done working
and if my db ever gets compromised ... the data is encrypted the key
is physically separated and I sleep at night.