pgcrypto.sql

--
-- Column DB encryption with indexing using pgcrypto
--

-- Demo encrypt/decrypt identity with pgcrypto
select convert_from(
  decrypt_iv(
    encrypt_iv(
      'test', 
      digest('superkey', 'sha256'), 
      digest('12_surname', 'sha256'), 
      'aes-cbc/pad:pkcs'
    ), 
    digest('superkey', 'sha256'), 
    digest('12_surname', 'sha256'), 
    'aes-cbc/pad:pkcs'
    ), 
  'utf8'
);

-- Testing table
CREATE TABLE clients(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           bytea    NULL
);

--
-- Inserting testing record to the database, manually.
-- Structure: HMAC(ciphertext, sha256) 32B || HMAC(plaintext, sha256) 32B || ciphertext
--   First HMAC is to protect data from modification.
--   Second HMAC is for exact match lookup. Can be either in separate column or in the name as we have here.
--   You can have more exact match HMAC indices, e.g., another one strtolower(trim(name))
--
-- Note: Hack with the digest('1_name'), this will be generated in the application. 1 is the primary key
-- You have to either know primary key when inserting / updating encrypted value OR define another
-- random nonce column in the table - random number generated in the application when inserting a new record. 
-- IV will be computed from the nonce then.
--
-- The HMAC on ciphertext would be also computed differently - encrypt first, then HMAC, no double encryption is needed.
--

INSERT INTO clients VALUES (1,
    hmac(
            encrypt_iv(
                'ph4r05 super long name so it has more than one AES block',
                digest('superkey', 'sha256'),
                digest('1_name', 'sha256'),
                'aes-cbc/pad:pkcs'
            ), digest('superkey_hmac', 'sha256'), 'sha256'
        )

    || hmac('ph4r05 super long name so it has more than one AES block',
            digest('superkey_hmac_exact', 'sha256'), 'sha256'
       )

    || encrypt_iv(
        'ph4r05 super long name so it has more than one AES block',
        digest('superkey', 'sha256'),
        digest('1_name', 'sha256'),
        'aes-cbc/pad:pkcs'
    )
);

-- Select decrypted value.
-- Demonstrates usage of the composite name column with HMACs
SELECT id, convert_from(
  decrypt_iv(
    SUBSTRING(name, 65),
    digest('superkey', 'sha256'),
    digest(id || '_name', 'sha256'),
    'aes-cbc/pad:pkcs'
  ), 'utf8') from clients;


-- Exact match search
SELECT id from clients
WHERE SUBSTRING(name, 33, 32)
      = hmac('ph4r05 super long name so it has more than one AES block',
           digest('superkey_hmac_exact', 'sha256'), 'sha256'
       );