------------------------------------------------------------------------
-- Copyright (c) 2009
-- Ronald Peterson
-- (Y) Yellowbank
--
-- https://www.yellowbank.com/
--
-- There are no restrictions on the copying of this file.
------------------------------------------------------------------------


------------------------------------------------------------------------
-- RANDOM NUMBER GENERATION

-- NOTE: On Linux, this will block until /dev/random collects enough
--       entropy.   
SELECT * FROM y_bbs_random_seed( 32, 32 );

SELECT * FROM encode( y_bbs_random_octets( 32 ), 'hex' );

SELECT * FROM encode( y_bbs_random_range( 'ffffffffffffffff' ), 'hex');

SELECT * FROM encode( y_mt_random_range( 'ffffffffffffffff' ), 'hex');
------------------------------------------------------------------------


------------------------------------------------------------------------
-- HASH DIGESTS

CREATE TEMP TABLE hash_algorithms (
  algorithm TEXT UNIQUE NOT NULL
);

INSERT INTO hash_algorithms (algorithm) VALUES
('WHIRLPOOL'),
('TIGER'),
('TIGER160'),
('TIGER128'),
('RIPEMD320'),
('RIPEMD256'),
('RIPEMD128'),
('SHA512'),
('SHA384'),
('SHA256'),
('SHA224'),
('SHA1'),
('HAVAL256'),
('HAVAL224'),
('HAVAL192'),
('HAVAL160'),
('HAVAL128'),
('GOST'),
('SNEFRU256'),
('SNEFRU128'),
('MD5'),
('MD4'),
('CRC32'),
('ADLER32');

CREATE TEMP TABLE hash_test_data (
  data TEXT UNIQUE NOT NULL
);

INSERT INTO hash_test_data (data) VALUES
(''),
('squidgulous'),
('hashme');

SELECT
  algorithm,
  data as tohash,
  octet_length( y_mhash( data::bytea, algorithm ) ) as octets,
  encode( y_mhash( data::bytea, algorithm ), 'hex' ) as digest
FROM
  hash_algorithms, hash_test_data
ORDER BY
  algorithm, data;
------------------------------------------------------------------------


------------------------------------------------------------------------
-- RSA PKCS #1 v2.1

CREATE TABLE full_keys (
  key_id
    CHAR(64)
    PRIMARY KEY,
  key_type
    VARCHAR(7)
    NOT NULL
    CHECK ( key_type in ('sign', 'encrypt' ) ),
  n
    VARCHAR(4096)
    NOT NULL,
  e
    VARCHAR(4096)
    NOT NULL,
  d
    VARCHAR(4096)
    NOT NULL,
  p
    VARCHAR(4096)
    NOT NULL,
  q
    VARCHAR(4096)
    NOT NULL,
  a
    VARCHAR(4096)
    NOT NULL,
  b
    VARCHAR(4096)
    NOT NULL,
  c
    VARCHAR(4096)
    NOT NULL
);
COMMENT ON COLUMN full_keys.n IS 'public: modulus';
COMMENT ON COLUMN full_keys.e IS 'public: exponent';
COMMENT ON COLUMN full_keys.d IS 'private: exponent';
COMMENT ON COLUMN full_keys.p IS 'private: prime factor p';
COMMENT ON COLUMN full_keys.q IS 'private: prime factor q';
COMMENT ON COLUMN full_keys.a IS 'private: CRT exponent (dP)';
COMMENT ON COLUMN full_keys.b IS 'private: CRT exponent (dQ)';
COMMENT ON COLUMN full_keys.c IS 'private: CRT coefficient (qInv)';

CREATE TABLE encrypted (
  key_id
    CHAR(64)
    NOT NULL,
  cipherText
    TEXT
    NOT NULL        
);
CREATE INDEX encrypted__key_id_idx ON encrypted( key_id );

CREATE TABLE signed (
  key_id
    CHAR(64)
    NOT NULL,
  message
    BYTEA
    NOT NULL,
  signature
    TEXT
    NOT NULL        
);
CREATE INDEX signed__key_id_idx ON signed( key_id );

CREATE TABLE signed_sha256 (
  key_id
    CHAR(64)
    NOT NULL,
  digest
    CHAR(64)
    NOT NULL,
  signature
    TEXT
    NOT NULL        
);
CREATE INDEX signed_sha256__key_id_idx ON signed_sha256( key_id );

CREATE OR REPLACE FUNCTION
rsa_encrypt( IN message BYTEA,
             IN keyid TEXT,
             OUT ciphertext TEXT )
AS $$
   SELECT y_rsa_encrypt( $1, ROW( n, e ) )
   FROM
     full_keys
   WHERE
     key_id = $2;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION
rsa_sign( IN message BYTEA,
          IN keyid TEXT,
          OUT signature TEXT )
AS $$
   SELECT y_rsa_sign( $1, ROW( d, p, q, a, b, c ) )
   FROM
     full_keys
   WHERE
     key_id = $2;
$$ LANGUAGE SQL;

INSERT INTO
  full_keys
SELECT
  encode( y_mhash( decode( n, 'hex' ), 'SHA256' ), 'hex' ) AS key_id,
  'encrypt' AS key_type,
  n, e, d, p, q, a, b, c
FROM
  y_generate_rsa_keypair( 2048, 50 );

SELECT rsa_encrypt( '12345678abcdef', key_id ) FROM full_keys;

INSERT INTO
  encrypted( key_id, ciphertext )
SELECT
  key_id,
  rsa_encrypt( '123abc456def7890'::bytea, key_id )
FROM
  full_keys;

INSERT INTO
  encrypted( key_id, ciphertext )
SELECT
  key_id,
  rsa_encrypt( '123abc456', key_id )
FROM
  full_keys;

SELECT
  ciphertext,
  y_rsa_decrypt( ciphertext, ROW( d, p, q, a, b, c ) )
AS
  message
FROM
  encrypted, full_keys
WHERE
  encrypted.key_id = full_keys.key_id;

PREPARE sign_p( BYTEA ) AS
INSERT INTO
  signed( key_id, message, signature )
SELECT
  key_id,
  $1,
  rsa_sign( $1, key_id )
FROM
  full_keys;

EXECUTE sign_p( '123abc456def7890'::bytea );
EXECUTE sign_p( 'deadbeaf'::bytea );

SELECT
  message,
  signature,
  y_rsa_verify( message,
                signature,
                ROW( n, e ) ) AS verify
FROM
  signed, full_keys
WHERE
  signed.key_id = full_keys.key_id;

INSERT INTO signed_sha256( key_id, digest, signature )
SELECT key_id,
       encode( y_mhash( 'deadbeaf'::bytea, 'SHA256' ), 'hex' ),
       y_rsa_sign_sha256( encode( y_mhash( 'deadbeaf'::bytea, 'SHA256' ), 'hex' ),
                          ROW( d, p, q, a, b, c ) )
FROM full_keys;

SELECT digest,
       signature,
       y_rsa_verify_sha256( digest,
                            signature,
                            ROW( n, e ) )
FROM signed_sha256, full_keys
WHERE signed_sha256.key_id = full_keys.key_id;
------------------------------------------------------------------------