Created
February 6, 2024 18:11
-
-
Save calvinbrewer/3c31fe06c39da08de533870b07026459 to your computer and use it in GitHub Desktop.
supabase-cipherstash-extension.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- PostgreSQL CipherStash Extension | |
-- | |
-- | |
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: - | |
-- | |
-- CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public; | |
-- | |
-- Name: ore_64_8_v1_term; Type: TYPE; Schema: public; | |
-- | |
CREATE TYPE public.ore_64_8_v1_term AS ( | |
bytes bytea | |
); | |
-- | |
-- Name: ore_64_8_v1; Type: TYPE; Schema: public; | |
-- | |
CREATE TYPE public.ore_64_8_v1 AS ( | |
terms public.ore_64_8_v1_term[] | |
); | |
-- | |
-- Name: compare_ore_64_8_v1(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.compare_ore_64_8_v1(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS integer | |
LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
cmp_result integer; | |
BEGIN | |
-- Recursively compare blocks bailing as soon as we can make a decision | |
RETURN compare_ore_array(a.terms, b.terms); | |
END | |
$$; | |
-- | |
-- Name: compare_ore_64_8_v1_term(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.compare_ore_64_8_v1_term(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS integer | |
LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
eq boolean := true; | |
unequal_block smallint := 0; | |
hash_key bytea; | |
target_block bytea; | |
left_block_size CONSTANT smallint := 16; | |
right_block_size CONSTANT smallint := 32; | |
right_offset CONSTANT smallint := 136; -- 8 * 17 | |
indicator smallint := 0; | |
BEGIN | |
IF a IS NULL AND b IS NULL THEN | |
RETURN 0; | |
END IF; | |
IF a IS NULL THEN | |
RETURN -1; | |
END IF; | |
IF b IS NULL THEN | |
RETURN 1; | |
END IF; | |
IF bit_length(a.bytes) != bit_length(b.bytes) THEN | |
RAISE EXCEPTION 'Ciphertexts are different lengths'; | |
END IF; | |
FOR block IN 0..7 LOOP | |
-- Compare each PRP (byte from the first 8 bytes) and PRF block (8 byte | |
-- chunks of the rest of the value). | |
-- NOTE: | |
-- * Substr is ordinally indexed (hence 1 and not 0, and 9 and not 8). | |
-- * We are not worrying about timing attacks here; don't fret about | |
-- the OR or !=. | |
IF | |
substr(a.bytes, 1 + block, 1) != substr(b.bytes, 1 + block, 1) | |
OR substr(a.bytes, 9 + left_block_size * block, left_block_size) != substr(b.bytes, 9 + left_block_size * BLOCK, left_block_size) | |
THEN | |
-- set the first unequal block we find | |
IF eq THEN | |
unequal_block := block; | |
END IF; | |
eq = false; | |
END IF; | |
END LOOP; | |
IF eq THEN | |
RETURN 0::integer; | |
END IF; | |
-- Hash key is the IV from the right CT of b | |
hash_key := substr(b.bytes, right_offset + 1, 16); | |
-- first right block is at right offset + nonce_size (ordinally indexed) | |
target_block := substr(b.bytes, right_offset + 17 + (unequal_block * right_block_size), right_block_size); | |
indicator := ( | |
get_bit( | |
encrypt( | |
substr(a.bytes, 9 + (left_block_size * unequal_block), left_block_size), | |
hash_key, | |
'aes-ecb' | |
), | |
0 | |
) + get_bit(target_block, get_byte(a.bytes, unequal_block))) % 2; | |
IF indicator = 1 THEN | |
RETURN 1::integer; | |
ELSE | |
RETURN -1::integer; | |
END IF; | |
END; | |
$$; | |
-- | |
-- Name: compare_ore_array(public.ore_64_8_v1_term[], public.ore_64_8_v1_term[]); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.compare_ore_array(a public.ore_64_8_v1_term[], b public.ore_64_8_v1_term[]) RETURNS integer | |
LANGUAGE plpgsql | |
AS $$ | |
DECLARE | |
cmp_result integer; | |
BEGIN | |
IF (array_length(a, 1) = 0 OR a IS NULL) AND (array_length(b, 1) = 0 OR b IS NULL) THEN | |
RETURN 0; | |
END IF; | |
IF array_length(a, 1) = 0 OR a IS NULL THEN | |
RETURN -1; | |
END IF; | |
IF array_length(b, 1) = 0 OR a IS NULL THEN | |
RETURN 1; | |
END IF; | |
cmp_result := compare_ore_64_8_v1_term(a[1], b[1]); | |
IF cmp_result = 0 THEN | |
-- Removes the first element in the array, and calls this fn again to compare the next element/s in the array. | |
RETURN compare_ore_array(a[2:array_length(a,1)], b[2:array_length(b,1)]); | |
END IF; | |
RETURN cmp_result; | |
END | |
$$; | |
-- | |
-- Name: ore_64_8_v1_eq(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_eq(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1(a, b) = 0 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_gt(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_gt(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1(a, b) = 1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_gte(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_gte(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1(a, b) != -1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_lt(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_lt(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1(a, b) = -1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_lte(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_lte(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1(a, b) != 1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_neq(public.ore_64_8_v1, public.ore_64_8_v1); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_neq(a public.ore_64_8_v1, b public.ore_64_8_v1) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1(a, b) <> 0 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_term_eq(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_term_eq(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1_term(a, b) = 0 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_term_gt(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_term_gt(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1_term(a, b) = 1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_term_gte(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_term_gte(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1_term(a, b) != -1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_term_lt(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_term_lt(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1_term(a, b) = -1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_term_lte(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_term_lte(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1_term(a, b) != 1 | |
$$; | |
-- | |
-- Name: ore_64_8_v1_term_neq(public.ore_64_8_v1_term, public.ore_64_8_v1_term); Type: FUNCTION; Schema: public; | |
-- | |
CREATE FUNCTION public.ore_64_8_v1_term_neq(a public.ore_64_8_v1_term, b public.ore_64_8_v1_term) RETURNS boolean | |
LANGUAGE sql | |
AS $$ | |
SELECT compare_ore_64_8_v1_term(a, b) <> 0 | |
$$; | |
-- | |
-- Name: <; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.< ( | |
FUNCTION = public.ore_64_8_v1_term_lt, | |
LEFTARG = public.ore_64_8_v1_term, | |
RIGHTARG = public.ore_64_8_v1_term, | |
COMMUTATOR = OPERATOR(public.>), | |
NEGATOR = OPERATOR(public.>=), | |
RESTRICT = scalarltsel, | |
JOIN = scalarltjoinsel | |
); | |
-- | |
-- Name: <; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.< ( | |
FUNCTION = public.ore_64_8_v1_lt, | |
LEFTARG = public.ore_64_8_v1, | |
RIGHTARG = public.ore_64_8_v1, | |
COMMUTATOR = OPERATOR(public.>), | |
NEGATOR = OPERATOR(public.>=), | |
RESTRICT = scalarltsel, | |
JOIN = scalarltjoinsel | |
); | |
-- | |
-- Name: <=; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.<= ( | |
FUNCTION = public.ore_64_8_v1_term_lte, | |
LEFTARG = public.ore_64_8_v1_term, | |
RIGHTARG = public.ore_64_8_v1_term, | |
COMMUTATOR = OPERATOR(public.>=), | |
NEGATOR = OPERATOR(public.>), | |
RESTRICT = scalarlesel, | |
JOIN = scalarlejoinsel | |
); | |
-- | |
-- Name: <=; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.<= ( | |
FUNCTION = public.ore_64_8_v1_lte, | |
LEFTARG = public.ore_64_8_v1, | |
RIGHTARG = public.ore_64_8_v1, | |
COMMUTATOR = OPERATOR(public.>=), | |
NEGATOR = OPERATOR(public.>), | |
RESTRICT = scalarlesel, | |
JOIN = scalarlejoinsel | |
); | |
-- | |
-- Name: <>; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.<> ( | |
FUNCTION = public.ore_64_8_v1_term_neq, | |
LEFTARG = public.ore_64_8_v1_term, | |
RIGHTARG = public.ore_64_8_v1_term, | |
NEGATOR = OPERATOR(public.=), | |
MERGES, | |
HASHES, | |
RESTRICT = eqsel, | |
JOIN = eqjoinsel | |
); | |
-- | |
-- Name: <>; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.<> ( | |
FUNCTION = public.ore_64_8_v1_neq, | |
LEFTARG = public.ore_64_8_v1, | |
RIGHTARG = public.ore_64_8_v1, | |
NEGATOR = OPERATOR(public.=), | |
MERGES, | |
HASHES, | |
RESTRICT = eqsel, | |
JOIN = eqjoinsel | |
); | |
-- | |
-- Name: =; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.= ( | |
FUNCTION = public.ore_64_8_v1_term_eq, | |
LEFTARG = public.ore_64_8_v1_term, | |
RIGHTARG = public.ore_64_8_v1_term, | |
NEGATOR = OPERATOR(public.<>), | |
MERGES, | |
HASHES, | |
RESTRICT = eqsel, | |
JOIN = eqjoinsel | |
); | |
-- | |
-- Name: =; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.= ( | |
FUNCTION = public.ore_64_8_v1_eq, | |
LEFTARG = public.ore_64_8_v1, | |
RIGHTARG = public.ore_64_8_v1, | |
NEGATOR = OPERATOR(public.<>), | |
MERGES, | |
HASHES, | |
RESTRICT = eqsel, | |
JOIN = eqjoinsel | |
); | |
-- | |
-- Name: >; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.> ( | |
FUNCTION = public.ore_64_8_v1_term_gt, | |
LEFTARG = public.ore_64_8_v1_term, | |
RIGHTARG = public.ore_64_8_v1_term, | |
COMMUTATOR = OPERATOR(public.<), | |
NEGATOR = OPERATOR(public.<=), | |
RESTRICT = scalargtsel, | |
JOIN = scalargtjoinsel | |
); | |
-- | |
-- Name: >; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.> ( | |
FUNCTION = public.ore_64_8_v1_gt, | |
LEFTARG = public.ore_64_8_v1, | |
RIGHTARG = public.ore_64_8_v1, | |
COMMUTATOR = OPERATOR(public.<), | |
NEGATOR = OPERATOR(public.<=), | |
RESTRICT = scalargtsel, | |
JOIN = scalargtjoinsel | |
); | |
-- | |
-- Name: >=; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.>= ( | |
FUNCTION = public.ore_64_8_v1_term_gte, | |
LEFTARG = public.ore_64_8_v1_term, | |
RIGHTARG = public.ore_64_8_v1_term, | |
COMMUTATOR = OPERATOR(public.<=), | |
NEGATOR = OPERATOR(public.<), | |
RESTRICT = scalarlesel, | |
JOIN = scalarlejoinsel | |
); | |
-- | |
-- Name: >=; Type: OPERATOR; Schema: public; | |
-- | |
CREATE OPERATOR public.>= ( | |
FUNCTION = public.ore_64_8_v1_gte, | |
LEFTARG = public.ore_64_8_v1, | |
RIGHTARG = public.ore_64_8_v1, | |
COMMUTATOR = OPERATOR(public.<=), | |
NEGATOR = OPERATOR(public.<), | |
RESTRICT = scalarlesel, | |
JOIN = scalarlejoinsel | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment