pg_enigma

pig build pkg pg_enigma         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

CREATE EXTENSION pg_enigma;

CREATE EXTENSION IF NOT EXISTS pg_enigma;

-- Create a table with an encrypted column (key slot 2)
CREATE TABLE test_pgp (
    id SERIAL,
    val Enigma(2)
);

-- Load the public key for encryption
SELECT set_public_key_from_file(2, '/path/to/public-key.asc');

-- Insert data (automatically encrypted with the public key)
INSERT INTO test_pgp (val) VALUES ('A secret value'::Text);

-- Without private key, SELECT returns encrypted PGP message
SELECT * FROM test_pgp;

-- Load private key to enable decryption
SELECT set_private_key_from_file(2, '/path/to/private-key.asc', 'passphrase');

-- Now SELECT returns decrypted plaintext
SELECT * FROM test_pgp;
-- id |      val
-- ----+----------------
--   1 | A secret value

-- Remove private key from memory
SELECT forget_private_key(2);
-- Subsequent SELECTs return encrypted data again

CREATE TABLE test_rsa (
    id SERIAL,
    val Enigma(3)
);

SELECT set_public_key_from_file(3, '/path/to/alice_public.pem');
INSERT INTO test_rsa (val) VALUES ('Another secret value'::Text);

SELECT set_private_key_from_file(3, '/path/to/alice_private.pem', 'passphrase');
SELECT * FROM test_rsa;

SELECT forget_private_key(3);