How do I decrypt data originally encrypted with `pgp_pub_encrypt` with the `gpg` command line?

For reasons, I have data that was originally encrypted with the pgp_pub_encrypt function in PostgreSQL, using public/private keys that I have, like this:

INSERT INTO testtable(testdata)
VALUES(pgp_pub_encrypt('random string', dearmor(
'-----BEGIN PGP PUBLIC KEY BLOCK-----

mQINBGahWCYBEADk3B3HsMtpNrQC0JGbcrV9BrYuYZPiOjq0Z+NgQRJMz2Z6pRsj
...
-----END PGP PUBLIC KEY BLOCK-----')));

I can decrypt it with my secret key inside Postgres:

SELECT pgp_pub_decrypt(cc, dearmor(
'-----BEGIN PGP PRIVATE KEY BLOCK-----

lQcYBGah2323YB2323D23blablalbalblblaetc...
...
-----END PGP PRIVATE KEY BLOCK-----'
), 'testdata') AS testdata FROM testtable;

But what I need to do is export the data from Postgres and decrypt it with gpg. How can I do that?

I've tried several iterations and formats exporting as text and data, for example:

COPY (SELECT testdata FROM testtable) TO '/Users/jonathanbuys/Desktop/encrypted_data.dat' WITH (FORMAT BINARY);

and wrapping the export with gpg headers, but so far no luck.

Any pointers in the right direction are greatly appreciated.

Solution

The COPY BINARY format is optimized for efficient use between PostgreSQL servers of the same version and architecture, not for maximal interoperability. So that is the opposite of what you want.

You could use one of the text formats (CSV or TEXT) to send out the output of the armor() function, but that is still going to lead to some characters (newlines only, as far as I know) which will be quoted or escaped which you have to deal with.

So instead of using armor, I would use encode(.,'hex') to escape the stored binary into a format which we know doesn't have those special characters. Based somewhat on this answer, that gives this following psql metacommand:

\COPY (SELECT encode(testdata,'hex') FROM testtable) TO program 'xxd -p -r|gpg -d -q > decrypted'

This depends on testtable having exactly one row, or on you adding a WHERE clause to the query so it only outputs one row.

Of course there other ways you could piece this together, using intermediate files rather than pipelines, or maybe using base64 rather than hex. And with sufficient server privs, you could use COPY rather than \COPY.