Generate random bytea:
CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
SET search_path = 'pg_catalog';
INSERT INTO customers ( username, email, cc )
VALUES
(
'steven',
pgp_pub_encrypt (
'[email protected]',
dearmor ( '-----BEGIN PGP PUBLIC KEY BLOCK-----
this is where you paste your public key
-----END PGP PUBLIC KEY BLOCK-----
' )),
pgp_pub_encrypt (
'4114423232323332',
dearmor ( '-----BEGIN PGP PUBLIC KEY BLOCK-----
this is where you paste your public key
-----END PGP PUBLIC KEY BLOCK-----
' )));
SELECT
username,
pgp_pub_decrypt (
email :: bytea,
dearmor ( '-----BEGIN PGP PRIVATE KEY BLOCK-----
this is where you paste your private key
-----END PGP PRIVATE KEY BLOCK-----
' )) AS email,
pgp_pub_decrypt (
cc :: bytea,
dearmor ( '-----BEGIN PGP PRIVATE KEY BLOCK-----
this is where you paste your private key
-----END PGP PRIVATE KEY BLOCK-----
' )) AS cc
FROM
customers
WHERE
ID = '1';
BLOWFISH from Postgres functions:
--
-- symmetric
select digest(pgp_sym_decrypt(
pgp_sym_encrypt(E'\r\n0\n1\r\r\n\n2\r', 'key', 'convert-crlf=1'),
'key', 'convert-crlf=1'), 'sha1') as result,
digest(E'\r\n0\n1\r\r\n\n2\r', 'sha1') as expect;
select encode(pgp_pub_decrypt_bytea(
pgp_pub_encrypt('Secret msg', dearmor(pubkey)),
dearmor(seckey)), 'escape')
from keytbl where keytbl.id=1;
encode
------------
Secret msg
(1 row)
select pgp_pub_decrypt(
pgp_pub_encrypt('Secret msg', dearmor(pubkey)),
dearmor(seckey))
from keytbl where keytbl.id=1;
pgp_pub_decrypt
-----------------
Secret msg
(1 row)
-- Blowfish cipher
CREATE TABLE ctest (data text, res text, salt text);
INSERT INTO ctest VALUES ('password', '', '');
UPDATE ctest SET salt = gen_salt('bf', 8);
UPDATE ctest SET res = crypt(data, salt);
SELECT res = crypt(data, res) AS "worked"
--
-- some standard Blowfish testvalues
SELECT encrypt('\x0000000000000000', '\x0000000000000000', 'bf-ecb/pad:none');
SELECT encrypt('\xffffffffffffffff', '\xffffffffffffffff', 'bf-ecb/pad:none');
SELECT encrypt('\x1000000000000001', '\x3000000000000000', 'bf-ecb/pad:none');
SELECT encrypt('\x1111111111111111', '\x1111111111111111', 'bf-ecb/pad:none');
SELECT encrypt('\x0123456789abcdef', '\xfedcba9876543210', 'bf-ecb/pad:none');
SELECT encrypt('\x01a1d6d039776742', '\xfedcba9876543210', 'bf-ecb/pad:none');
SELECT encrypt('\xffffffffffffffff', '\x0000000000000000', 'bf-ecb/pad:none');
-- setkey
SELECT encrypt('\xfedcba9876543210', '\xf0e1d2c3b4a5968778695a4b3c2d1e0f', 'bf-ecb/pad:none');
-- with padding
SELECT encrypt('\x01234567890123456789', '\x33443344334433443344334433443344', 'bf-ecb');
-- cbc
-- 28 bytes key
SELECT encrypt('\x6b77b4d63006dee605b156e27403979358deb9e7154616d959f1652bd5',
'\x37363534333231204e6f77206973207468652074696d6520666f7220',
'bf-cbc');
-- 29 bytes key
SELECT encrypt('\x6b77b4d63006dee605b156e27403979358deb9e7154616d959f1652bd5ff92cc',
'\x37363534333231204e6f77206973207468652074696d6520666f722000',
'bf-cbc');
-- blowfish-448
SELECT encrypt('\xfedcba9876543210',
'\xf0e1d2c3b4a5968778695a4b3c2d1e0f001122334455667704689104c2fd3b2f584023641aba61761f1f1f1f0e0e0e0effffffffffffffff',
'bf-ecb/pad:none');
-- empty data
select encrypt('', 'foo', 'bf');
-- 10 bytes key
select encrypt('foo', '0123456789', 'bf');
-- 22 bytes key
select encrypt('foo', '0123456789012345678901', 'bf');
-- decrypt
select encode(decrypt(encrypt('foo', '0123456', 'bf'), '0123456', 'bf'), 'escape');
-- iv
select encrypt_iv('foo', '0123456', 'abcd', 'bf');
select encode(decrypt_iv('\x95c7e89322525d59', '0123456', 'abcd', 'bf'), 'escape');
-- long message
select encrypt('Lets try a longer message.', '0123456789', 'bf');
select encode(decrypt(encrypt('Lets try a longer message.', '0123456789', 'bf'), '0123456789', 'bf'), 'escape');
-- PGP encrypt
--
select pgp_sym_decrypt(pgp_sym_encrypt('Secret.', 'key'), 'key');
-- check whether the defaults are ok
select pgp_sym_decrypt(pgp_sym_encrypt('Secret.', 'key'),
'key', 'expect-cipher-algo=aes128,
expect-disable-mdc=0,
expect-sess-key=0,
expect-s2k-mode=3,
expect-s2k-digest-algo=sha1,
expect-compress-algo=0
');
-- maybe the expect- stuff simply does not work
select pgp_sym_decrypt(pgp_sym_encrypt('Secret.', 'key'),
'key', 'expect-cipher-algo=bf,
expect-disable-mdc=1,
expect-sess-key=1,
expect-s2k-mode=0,
expect-s2k-digest-algo=md5,
expect-compress-algo=1
');
-- bytea as text
select pgp_sym_decrypt(pgp_sym_encrypt_bytea('Binary', 'baz'), 'baz');
-- text as bytea
select encode(pgp_sym_decrypt_bytea(pgp_sym_encrypt('Text', 'baz'), 'baz'), 'escape');
-- algorithm change
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'cipher-algo=bf'),
'key', 'expect-cipher-algo=bf');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'cipher-algo=aes'),
'key', 'expect-cipher-algo=aes128');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'cipher-algo=aes192'),
'key', 'expect-cipher-algo=aes192');
-- s2k change
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-mode=0'),
'key', 'expect-s2k-mode=0');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-mode=1'),
'key', 'expect-s2k-mode=1');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-mode=3'),
'key', 'expect-s2k-mode=3');
-- s2k count change
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-count=1024'),
'key', 'expect-s2k-count=1024');
-- s2k_count rounds up
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-count=65000000'),
'key', 'expect-s2k-count=65000000');
-- s2k digest change
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=md5'),
'key', 'expect-s2k-digest-algo=md5');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 's2k-digest-algo=sha1'),
'key', 'expect-s2k-digest-algo=sha1');
-- sess key
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'sess-key=0'),
'key', 'expect-sess-key=0');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'sess-key=1'),
'key', 'expect-sess-key=1');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'sess-key=1, cipher-algo=bf'),
'key', 'expect-sess-key=1, expect-cipher-algo=bf');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'sess-key=1, cipher-algo=aes192'),
'key', 'expect-sess-key=1, expect-cipher-algo=aes192');
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'sess-key=1, cipher-algo=aes256'),
'key', 'expect-sess-key=1, expect-cipher-algo=aes256');
-- no mdc
select pgp_sym_decrypt(
pgp_sym_encrypt('Secret.', 'key', 'disable-mdc=1'),
'key', 'expect-disable-mdc=1');
-- crlf
select pgp_sym_decrypt_bytea(
pgp_sym_encrypt(E'1\n2\n3\r\n', 'key', 'convert-crlf=1'),
'key');
-- conversion should be lossless
select digest(pgp_sym_decrypt(
pgp_sym_encrypt(E'\r\n0\n1\r\r\n\n2\r', 'key', 'convert-crlf=1'),
'key', 'convert-crlf=1'), 'sha1') as result,
digest(E'\r\n0\n1\r\r\n\n2\r', 'sha1') as expect;