ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium_masks GRANT ALL ON TABLES TO pgsodium_keyiduser;
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium_masks GRANT ALL ON SEQUENCES TO pgsodium_keyiduser;
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium_masks GRANT ALL ON FUNCTIONS TO pgsodium_keyiduser;

-- pgsodium_keyiduser can use all tables and sequences (functions are granted individually)
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium REVOKE ALL ON TABLES FROM pgsodium_keyiduser;
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium REVOKE ALL ON SEQUENCES FROM pgsodium_keyiduser;

ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium GRANT ALL ON TABLES TO pgsodium_keyholder;
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium GRANT ALL ON SEQUENCES TO pgsodium_keyholder;

REVOKE ALL ON ALL TABLES IN SCHEMA pgsodium FROM pgsodium_keyiduser;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA pgsodium FROM pgsodium_keyiduser;

GRANT pgsodium_keyholder TO pgsodium_keymaker;  -- deprecating keyholder

GRANT ALL ON ALL TABLES IN SCHEMA pgsodium TO pgsodium_keymaker;
GRANT ALL ON ALL SEQUENCES IN SCHEMA pgsodium TO pgsodium_keymaker;

GRANT SELECT ON pgsodium.valid_key TO pgsodium_keyiduser;

DROP VIEW pgsodium.valid_key;
DROP FUNCTION pgsodium.create_key(text, pgsodium.key_type, bigint, bytea, timestamp, jsonb);
DELETE FROM pgsodium.key where status = 'default';

ALTER TYPE pgsodium.key_type ADD VALUE 'hmacsha512';
ALTER TYPE pgsodium.key_type ADD VALUE 'hmacsha256';
ALTER TYPE pgsodium.key_type ADD VALUE 'auth';
ALTER TYPE pgsodium.key_type ADD VALUE 'shorthash';
ALTER TYPE pgsodium.key_type ADD VALUE 'generichash';
ALTER TYPE pgsodium.key_type ADD VALUE 'kdf';
ALTER TYPE pgsodium.key_type ADD VALUE 'secretbox';
ALTER TYPE pgsodium.key_type ADD VALUE 'secretstream';
ALTER TYPE pgsodium.key_type ADD VALUE 'stream_xchacha20';

ALTER TABLE pgsodium.key ADD COLUMN raw_key bytea;
ALTER TABLE pgsodium.key ADD COLUMN raw_key_nonce bytea;
ALTER TABLE pgsodium.key ADD COLUMN parent_key uuid REFERENCES pgsodium.key(id);
ALTER TABLE pgsodium.key RENAME comment TO name;
ALTER TABLE pgsodium.key RENAME user_data TO associated_data;
ALTER TABLE pgsodium.key ALTER COLUMN associated_data TYPE text USING '';
ALTER TABLE pgsodium.key ALTER COLUMN associated_data SET DEFAULT 'associated';
ALTER TABLE pgsodium.key ALTER COLUMN key_id DROP NOT NULL;
ALTER TABLE pgsodium.key ALTER COLUMN key_context DROP NOT NULL;
ALTER TABLE pgsodium.key ALTER COLUMN expires TYPE timestamptz USING expires at time zone 'utc';
ALTER TABLE pgsodium.key ALTER COLUMN created TYPE timestamptz USING created at time zone 'utc';

ALTER TABLE pgsodium.key ADD CONSTRAINT pgsodium_key_unique_name UNIQUE (name);
ALTER TABLE pgsodium.key ADD CONSTRAINT pgsodium_raw CHECK (
  CASE WHEN raw_key IS NOT NULL
    THEN key_id IS NULL     AND key_context IS NULL     AND parent_key IS NOT NULL
    ELSE key_id IS NOT NULL AND key_context IS NOT NULL AND parent_key IS NULL
  END);

CREATE OR REPLACE VIEW pgsodium.valid_key AS
  SELECT id, name, status, key_type, key_id, key_context, created, expires, associated_data
    FROM pgsodium.key
   WHERE  status IN ('valid', 'default')
     AND CASE WHEN expires IS NULL THEN true ELSE expires < now() END;

GRANT SELECT ON pgsodium.valid_key TO pgsodium_keyiduser;

CREATE FUNCTION pgsodium.create_key(
  key_type pgsodium.key_type = 'aead-det',
  name text = NULL,
  raw_key bytea = NULL,
  raw_key_nonce bytea = NULL,
  parent_key uuid = NULL,
  key_context bytea = 'pgsodium',
  expires timestamptz = NULL,
  associated_data text = ''
) RETURNS pgsodium.valid_key
AS $$
DECLARE
  new_key pgsodium.key;
  valid_key pgsodium.valid_key;
BEGIN
  INSERT INTO pgsodium.key (key_id, key_context, key_type, raw_key,
  raw_key_nonce, parent_key, expires, name, associated_data)
      VALUES (
        CASE WHEN raw_key IS NULL THEN
            NEXTVAL('pgsodium.key_key_id_seq'::REGCLASS)
        ELSE NULL END,
        CASE WHEN raw_key IS NULL THEN
            key_context
        ELSE NULL END,
        key_type,
        raw_key,
        CASE WHEN raw_key IS NOT NULL THEN
            COALESCE(raw_key_nonce, pgsodium.crypto_aead_det_noncegen())
        ELSE NULL END,
        CASE WHEN parent_key IS NULL and raw_key IS NOT NULL THEN
            (pgsodium.create_key('aead-det')).id
        ELSE parent_key END,
        expires,
        name,
        associated_data)
    RETURNING * INTO new_key;
  SELECT * INTO valid_key FROM pgsodium.valid_key WHERE id = new_key.id;
  RETURN valid_key;
END;
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.create_key OWNER TO pgsodium_keymaker;
GRANT EXECUTE ON FUNCTION pgsodium.create_key TO pgsodium_keyiduser;

-- HMAC external key support

CREATE OR REPLACE FUNCTION pgsodium.crypto_auth_hmacsha256(message bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'hmacsha256';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_auth_hmacsha256(message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_auth_hmacsha256(message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_auth_hmacsha256(bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE OR REPLACE FUNCTION pgsodium.crypto_auth_hmacsha256_verify(signature bytea, message bytea, key_uuid uuid)
  RETURNS boolean AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'hmacsha256';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_auth_hmacsha256_verify(signature, message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_auth_hmacsha256_verify(signature, message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_auth_hmacsha256_verify(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE OR REPLACE FUNCTION pgsodium.crypto_auth_hmacsha512(message bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'hmacsha512';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_auth_hmacsha512(message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_auth_hmacsha512(message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_auth_hmacsha512(bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE OR REPLACE FUNCTION pgsodium.crypto_auth_hmacsha512_verify(signature bytea, message bytea, key_uuid uuid)
  RETURNS boolean AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'hmacsha512';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_auth_hmacsha512_verify(signature, message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_auth_hmacsha512_verify(signature, message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_auth_hmacsha512_verify(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

-- Auth

CREATE FUNCTION pgsodium.crypto_auth(message bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'auth';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_auth(message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_auth(message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_auth(bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE FUNCTION crypto_auth_verify(mac bytea, message bytea, key_uuid uuid)
  RETURNS boolean AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'auth';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_auth_verify(mac, message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_auth_verify(mac, message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_auth_verify(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

-- Hash

CREATE FUNCTION pgsodium.crypto_shorthash(message bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'shorthash';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_shorthash(message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_shorthash(message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_shorthash(bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE FUNCTION pgsodium.crypto_generichash(message bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'generichash';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_generichash(message, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_generichash(message, key.key_id, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_generichash(bytea, uuid) OWNER TO pgsodium_keymaker;

-- kdf

CREATE FUNCTION crypto_kdf_derive_from_key(subkey_size integer, subkey_id bigint, context bytea, primary_key uuid)
RETURNS bytea
AS $$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = primary_key AND key_type = 'kdf';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_kdf_derive_from_key(subkey_size, subkey_id, context, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.derive_key(key.key_id, subkey_size, key.key_context);
END;

$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_kdf_derive_from_key(integer, bigint, bytea, uuid) OWNER TO pgsodium_keymaker;

  -- secretbox

CREATE OR REPLACE FUNCTION pgsodium.crypto_secretbox(message bytea, nonce bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'secretbox';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_secretbox(message, nonce, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_secretbox(message, nonce, key.key_id, key.key_context);
END;
$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_secretbox(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE OR REPLACE FUNCTION pgsodium.crypto_secretbox_open(message bytea, nonce bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'secretbox';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_secretbox_open(message, nonce, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_secretbox_open(message, nonce, key.key_id, key.key_context);
END;
$$
LANGUAGE plpgsql
STRICT STABLE
SECURITY DEFINER
SET search_path = '';

ALTER FUNCTION pgsodium.crypto_secretbox_open(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

-- Ext key support for DET and IETF

CREATE OR REPLACE FUNCTION pgsodium.crypto_aead_det_encrypt(message bytea, additional bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'aead-det';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_aead_det_encrypt(message, additional, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_aead_det_encrypt(message, additional, key.key_id, key.key_context);
END;
  $$
  LANGUAGE plpgsql
  SECURITY DEFINER
  STABLE
  SET search_path=''
  ;

ALTER FUNCTION pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

CREATE OR REPLACE FUNCTION pgsodium.crypto_aead_det_decrypt(message bytea, additional bytea, key_uuid uuid)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'aead-det';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_aead_det_decrypt(message, additional, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_aead_det_decrypt(message, additional, key.key_id, key.key_context);
END;
  $$
  LANGUAGE plpgsql
  SECURITY DEFINER
  STABLE
  SET search_path='';

ALTER FUNCTION pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

--- AEAD det with nonce

CREATE OR REPLACE FUNCTION pgsodium.crypto_aead_det_encrypt(message bytea, additional bytea, key_uuid uuid, nonce bytea)
  RETURNS bytea AS
$$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'aead-det';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_aead_det_encrypt(message, additional, key.decrypted_raw_key, nonce);
  END IF;
  RETURN pgsodium.crypto_aead_det_encrypt(message, additional, key.key_id, key.key_context, nonce);
END;
  $$
  LANGUAGE plpgsql
  SECURITY DEFINER
  STABLE
  SET search_path=''
  ;

ALTER FUNCTION pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid, bytea) OWNER TO pgsodium_keymaker;

CREATE OR REPLACE FUNCTION pgsodium.crypto_aead_det_decrypt(message bytea, additional bytea, key_uuid uuid, nonce bytea)
  RETURNS bytea AS
  $$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'aead-det';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_aead_det_decrypt(message, additional, key.decrypted_raw_key, nonce);
  END IF;
  RETURN pgsodium.crypto_aead_det_decrypt(message, additional, key.key_id, key.key_context, nonce);
END;
  $$
  LANGUAGE plpgsql
  SECURITY DEFINER
  STABLE
  SET search_path='';

ALTER FUNCTION pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid, bytea) OWNER TO pgsodium_keymaker;

-- IETF AEAD functions by key uuid

CREATE OR REPLACE FUNCTION pgsodium.crypto_aead_ietf_encrypt(message bytea, additional bytea, nonce bytea, key_uuid uuid)
  RETURNS bytea AS
  $$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'aead-ietf';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_aead_ietf_encrypt(message, additional, nonce, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_aead_ietf_encrypt(message, additional, nonce, key.key_id, key.key_context);
END;
  $$
  LANGUAGE plpgsql
  SECURITY DEFINER
  STRICT
  STABLE
  SET search_path=''
;

ALTER FUNCTION pgsodium.crypto_aead_ietf_encrypt(bytea, bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

GRANT EXECUTE ON FUNCTION
  pgsodium.crypto_aead_ietf_encrypt(message bytea, additional bytea, nonce bytea, key_uuid uuid)
  TO pgsodium_keyiduser;

CREATE OR REPLACE FUNCTION pgsodium.crypto_aead_ietf_decrypt(message bytea, additional bytea, nonce bytea, key_uuid uuid)
  RETURNS bytea AS
  $$
DECLARE
  key pgsodium.decrypted_key;
BEGIN
  SELECT * INTO STRICT key
    FROM pgsodium.decrypted_key v
  WHERE id = key_uuid AND key_type = 'aead-ietf';

  IF key.decrypted_raw_key IS NOT NULL THEN
    RETURN pgsodium.crypto_aead_ietf_decrypt(message, additional, nonce, key.decrypted_raw_key);
  END IF;
  RETURN pgsodium.crypto_aead_ietf_decrypt(message, additional, nonce, key.key_id, key.key_context);
END;
  $$
  LANGUAGE plpgsql
  SECURITY DEFINER
  STRICT
  STABLE
  SET search_path='';

ALTER FUNCTION pgsodium.crypto_aead_ietf_decrypt(bytea, bytea, bytea, uuid) OWNER TO pgsodium_keymaker;

GRANT EXECUTE ON FUNCTION
  pgsodium.crypto_aead_ietf_decrypt(message bytea, additional bytea, nonce bytea, key_uuid uuid)
  TO pgsodium_keyiduser;

-- bytea and extended associated data support for encrypted columns

DROP VIEW pgsodium.masking_rule CASCADE;

CREATE OR REPLACE VIEW pgsodium.masking_rule AS
  WITH const AS (
    SELECT
      'encrypt +with +key +id +([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})'
        AS pattern_key_id,
      'encrypt +with +key +column +(\w+)'
        AS pattern_key_id_column,
      '(?<=associated) +\(([\w, ]+)\)'
        AS pattern_associated_columns,
      '(?<=nonce) +(\w+)'
        AS pattern_nonce_column,
      '(?<=decrypt with view) +(\w+\.\w+)'
        AS pattern_view_name
  ),
  rules_from_seclabels AS (
    SELECT
      sl.objoid AS attrelid,
      sl.objsubid  AS attnum,
      c.relnamespace::regnamespace,
      c.relname,
      a.attname,
      pg_catalog.format_type(a.atttypid, a.atttypmod),
      sl.label AS col_description,
      (regexp_match(sl.label, k.pattern_key_id_column, 'i'))[1] AS key_id_column,
      (regexp_match(sl.label, k.pattern_key_id, 'i'))[1] AS key_id,
      (regexp_match(sl.label, k.pattern_associated_columns, 'i'))[1] AS associated_columns,
      (regexp_match(sl.label, k.pattern_nonce_column, 'i'))[1] AS nonce_column,
      coalesce((regexp_match(sl2.label, k.pattern_view_name, 'i'))[1],
               c.relnamespace::regnamespace || '.' || quote_ident('decrypted_' || c.relname)) AS view_name,
      100 AS priority
      FROM const k,
           pg_catalog.pg_seclabel sl
           JOIN pg_catalog.pg_class c ON sl.classoid = c.tableoid AND sl.objoid = c.oid
           JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid AND sl.objsubid = a.attnum
           LEFT JOIN pg_catalog.pg_seclabel sl2 ON sl2.objoid = c.oid AND sl2.objsubid = 0
     WHERE a.attnum > 0
       AND c.relnamespace::regnamespace != 'pg_catalog'::regnamespace
       AND NOT a.attisdropped
       AND sl.label ilike 'ENCRYPT%'
       AND sl.provider = 'pgsodium'
  )
  SELECT
    DISTINCT ON (attrelid, attnum) *
    FROM rules_from_seclabels
   ORDER BY attrelid, attnum, priority DESC;

CREATE VIEW pgsodium.mask_columns AS SELECT
  a.attname,
  a.attrelid,
  m.key_id,
  m.key_id_column,
  m.associated_columns,
  m.nonce_column,
  m.format_type
  FROM pg_attribute a
  LEFT JOIN  pgsodium.masking_rule m
  ON m.attrelid = a.attrelid
  AND m.attname = a.attname
  WHERE  a.attnum > 0 -- exclude ctid, cmin, cmax
  AND    NOT a.attisdropped
  ORDER BY a.attnum;

CREATE OR REPLACE FUNCTION pgsodium.quote_assoc(text, boolean = false)
RETURNS text
AS $$
    WITH a AS (SELECT array_agg(CASE WHEN $2 THEN
                                    'new.' || quote_ident(trim(v))
                                ELSE quote_ident(trim(v)) END) as r
               FROM regexp_split_to_table($1, '\s*,\s*') as v)
    SELECT array_to_string(a.r, '::text || ') || '::text' FROM a;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgsodium.encrypted_columns(relid OID)
RETURNS TEXT AS
$$
DECLARE
    m RECORD;
    expression TEXT;
    comma TEXT;
BEGIN
  expression := '';
  comma := E'        ';
  FOR m IN SELECT * FROM pgsodium.mask_columns where attrelid = relid LOOP
    IF m.key_id IS NULL AND m.key_id_column is NULL THEN
      CONTINUE;
    ELSE
      expression := expression || comma;
      IF m.format_type = 'text' THEN
          expression := expression || format(
            $f$%s = CASE WHEN %s IS NULL THEN NULL ELSE pg_catalog.encode(
              pgsodium.crypto_aead_det_encrypt(
                pg_catalog.convert_to(%s, 'utf8'),
                pg_catalog.convert_to((%s)::text, 'utf8'),
                %s::uuid,
                %s
              ),
                'base64') END$f$,
                'new.' || quote_ident(m.attname),
                COALESCE('new.' || quote_ident(m.key_id_column), quote_literal(m.key_id)),
                'new.' || quote_ident(m.attname),
                COALESCE(pgsodium.quote_assoc(m.associated_columns, true), quote_literal('')),
                COALESCE('new.' || quote_ident(m.key_id_column), quote_literal(m.key_id)),
                COALESCE('new.' || quote_ident(m.nonce_column), 'NULL')
          );
      ELSIF m.format_type = 'bytea' THEN
          expression := expression || format(
            $f$%s = CASE WHEN %s IS NULL THEN NULL ELSE
                        pgsodium.crypto_aead_det_encrypt(%s::bytea, pg_catalog.convert_to((%s)::text, 'utf8'),
                %s::uuid,
                %s
              ) END
              $f$,
                'new.' || quote_ident(m.attname),
                COALESCE('new.' || quote_ident(m.key_id_column), quote_literal(m.key_id)),
                'new.' || quote_ident(m.attname),
                COALESCE(pgsodium.quote_assoc(m.associated_columns, true), quote_literal('')),
                COALESCE('new.' || quote_ident(m.key_id_column), quote_literal(m.key_id)),
                COALESCE('new.' || quote_ident(m.nonce_column), 'NULL')
          );
      END IF;
    END IF;
    comma := E';\n        ';
  END LOOP;
  RETURN expression;
END
$$
  LANGUAGE plpgsql
  VOLATILE
  SET search_path=''
  ;

CREATE OR REPLACE FUNCTION pgsodium.decrypted_columns(relid OID)
RETURNS TEXT AS
$$
DECLARE
  m RECORD;
  expression TEXT;
  comma TEXT;
  padding text = '        ';
BEGIN
  expression := E'\n';
  comma := padding;
  FOR m IN SELECT * FROM pgsodium.mask_columns where attrelid = relid LOOP
    expression := expression || comma;
    IF m.key_id IS NULL AND m.key_id_column IS NULL THEN
      expression := expression || padding || quote_ident(m.attname);
    ELSE
      expression := expression || padding || quote_ident(m.attname) || E',\n';
      IF m.format_type = 'text' THEN
          expression := expression || format(
            $f$
            CASE WHEN %s IS NULL THEN NULL ELSE pg_catalog.convert_from(
              pgsodium.crypto_aead_det_decrypt(
                pg_catalog.decode(%s, 'base64'),
                pg_catalog.convert_to((%s)::text, 'utf8'),
                %s::uuid,
                %s
              ),
                'utf8') END AS %s$f$,
                coalesce(quote_ident(m.key_id_column), quote_literal(m.key_id)),
                quote_ident(m.attname),
                coalesce(pgsodium.quote_assoc(m.associated_columns), quote_literal('')),
                coalesce(quote_ident(m.key_id_column), quote_literal(m.key_id)),
                coalesce(quote_ident(m.nonce_column), 'NULL'),
                'decrypted_' || quote_ident(m.attname)
          );
      ELSIF m.format_type = 'bytea' THEN
          expression := expression || format(
            $f$
            CASE WHEN %s IS NULL THEN NULL ELSE pgsodium.crypto_aead_det_decrypt(
                %s::bytea,
                pg_catalog.convert_to((%s)::text, 'utf8'),
                %s::uuid,
                %s
              ) END AS %s$f$,
                coalesce(quote_ident(m.key_id_column), quote_literal(m.key_id)),
                quote_ident(m.attname),
                coalesce(pgsodium.quote_assoc(m.associated_columns), quote_literal('')),
                coalesce(quote_ident(m.key_id_column), quote_literal(m.key_id)),
                coalesce(quote_ident(m.nonce_column), 'NULL'),
                'decrypted_' || quote_ident(m.attname)
          );
      END IF;
    END IF;
    comma := E',       \n';
  END LOOP;
  RETURN expression;
END
$$
  LANGUAGE plpgsql
  VOLATILE
  SET search_path=''
;

CREATE OR REPLACE FUNCTION pgsodium.mask_role(masked_role regrole, source_name text, view_name text)
  RETURNS void AS
  $$
BEGIN
  EXECUTE format('GRANT pgsodium_keyiduser TO %s', masked_role);
  EXECUTE format('GRANT ALL ON %s TO %s', view_name, masked_role);
  RETURN;
END
$$
LANGUAGE plpgsql
SET search_path=''
;

CREATE OR REPLACE FUNCTION pgsodium.create_mask_view(relid oid, subid integer, debug boolean = false) RETURNS void AS
  $$
DECLARE
  body text;
  source_name text;
  rule pgsodium.masking_rule;
BEGIN
  SELECT * INTO STRICT rule FROM pgsodium.masking_rule WHERE attrelid = relid and attnum = subid ;

  source_name := relid::regclass;

  body = format(
    $c$
    DROP VIEW IF EXISTS %s;
    CREATE VIEW %s AS SELECT %s
    FROM %s;
    $c$,
    rule.view_name,
    rule.view_name,
    pgsodium.decrypted_columns(relid),
    source_name
  );
  IF debug THEN
    RAISE NOTICE '%', body;
  END IF;
  EXECUTE body;

  body = format(
    $c$
    CREATE OR REPLACE FUNCTION %s.%s_encrypt_secret()
      RETURNS TRIGGER
      LANGUAGE plpgsql
      AS $t$
    BEGIN
    %s;
    RETURN new;
    END;
    $t$;

    DROP TRIGGER IF EXISTS %s_encrypt_secret_trigger ON %s.%s;

    CREATE TRIGGER %s_encrypt_secret_trigger
      BEFORE INSERT ON %s
      FOR EACH ROW
      EXECUTE FUNCTION %s.%s_encrypt_secret ();
      $c$,
    rule.relnamespace,
    rule.relname,
    pgsodium.encrypted_columns(relid),
    rule.relname,
    rule.relnamespace,
    rule.relname,
    rule.relname,
    source_name,
    rule.relnamespace,
    rule.relname
  );
  if debug THEN
    RAISE NOTICE '%', body;
  END IF;
  EXECUTE body;

  PERFORM pgsodium.mask_role(oid::regrole, source_name, rule.view_name)
  FROM pg_roles WHERE pgsodium.has_mask(oid::regrole, source_name);

  RETURN;
END
  $$
  LANGUAGE plpgsql
  VOLATILE
  SET search_path='pg_catalog'
;

CREATE OR REPLACE FUNCTION pgsodium.update_masks(debug boolean = false)
RETURNS void AS
  $$
BEGIN
  PERFORM pgsodium.create_mask_view(objoid, objsubid, debug)
    FROM (SELECT sl.objoid, sl.objsubid
            FROM pg_catalog.pg_seclabel sl, pg_catalog.pg_class cl, pg_catalog.pg_namespace ns
           WHERE sl.classoid = cl.oid
             AND cl.relnamespace = ns.oid
             AND cl.relkind IN ('r', 'p', 'f') -- table, partition, or foreign
             AND sl.label ilike 'ENCRYPT%'
             AND sl.provider = 'pgsodium') c;
  RETURN;
END
$$
  LANGUAGE plpgsql
  SECURITY DEFINER
  SET search_path=''
;

DO $$
DECLARE
  func text;
BEGIN
    FOREACH func IN ARRAY
      ARRAY[
        'pgsodium.crypto_auth_hmacsha256(bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha256_verify(bytea, bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha512(bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha512_verify(bytea, bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha256(bytea, uuid)',
        'pgsodium.crypto_auth_hmacsha256_verify(bytea, bytea, uuid)',
        'pgsodium.crypto_auth_hmacsha512(bytea, uuid)',
        'pgsodium.crypto_auth_hmacsha512_verify(bytea, bytea, uuid)',
        'pgsodium.crypto_auth(bytea, uuid)',
        'pgsodium.crypto_auth_verify(bytea, bytea, uuid)',
        'pgsodium.crypto_shorthash(bytea, uuid)',
        'pgsodium.crypto_generichash(bytea, uuid)',
        'pgsodium.crypto_kdf_derive_from_key(integer, bigint, bytea, uuid)',
        'pgsodium.crypto_secretbox(bytea, bytea, uuid)',
        'pgsodium.crypto_secretbox_open(bytea, bytea, uuid)',
        'pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid)',
        'pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid)',
        'pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid, bytea)',
        'pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid, bytea)',
        'pgsodium.crypto_aead_ietf_encrypt(bytea, bytea, bytea, uuid)',
        'pgsodium.crypto_aead_ietf_decrypt(bytea, bytea, bytea, uuid)'
    ]
    LOOP
        EXECUTE format($i$
            REVOKE ALL ON FUNCTION %s FROM PUBLIC;
            GRANT EXECUTE ON FUNCTION %s TO pgsodium_keyiduser;
        $i$, func, func);
    END LOOP;
END
$$;

SECURITY LABEL FOR pgsodium ON COLUMN pgsodium.key.raw_key
    IS 'ENCRYPT WITH KEY COLUMN parent_key ASSOCIATED (id, associated_data) NONCE raw_key_nonce';

ALTER EXTENSION pgsodium DROP VIEW pgsodium.decrypted_key;
GRANT SELECT ON pgsodium.decrypted_key TO pgsodium_keymaker;