Secure Random Functions for PostgreSQL using PGCRYPTO

secure_random.sql

-- install the extension
CREATE EXTENSION pgcrypto;

CREATE OR REPLACE FUNCTION secure_random_bigint() RETURNS bigint AS $$
DECLARE
    v_bytes bytea;
    v_value bigint := 0;
    v_length integer := 8;
    i integer := 0;
BEGIN
	v_bytes := gen_random_bytes(v_length);
	FOR i IN 0..v_length-1 LOOP
		v_value := (v_value << 8) | get_byte(v_bytes, i);
	END LOOP;
    RETURN v_value::bigint;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION secure_random_int() RETURNS int AS $$
DECLARE
    v_bytes bytea;
    v_value int := 0;
    v_length integer := 4;
    i integer := 0;
BEGIN
	v_bytes := gen_random_bytes(v_length);
	FOR i IN 0..v_length-1 LOOP
		v_value := (v_value << 8) | get_byte(v_bytes, i);
	END LOOP;
    RETURN v_value::int;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION secure_random_double() RETURNS double precision AS $$
DECLARE
    v_bytes bytea;
    v_value double precision := 0; -- SUM(1, n=7) { x[n] / 256^n }
    v_factor double precision := 2^(-8); -- or 1/256 or 0.00390625
    v_length integer := 7; -- the IEEE754's mantissa needs 52 bits
    i integer := 0;
    -- Example with 2 bytes: arr[2] = { 111, 222 }
    -- equals to: (111.0 + (222.0 / 256)) / 256 = 0.43698120117187500000
    -- or (111.0 / 256) + ((222.0 / 256) / 256) = 0.43698120117187500000
BEGIN
	v_bytes := gen_random_bytes(v_length);
	FOR i IN REVERSE v_length-1..0 LOOP
		v_value := v_factor * (v_value + get_byte(v_bytes, i));
	END LOOP;
    RETURN v_value::double precision;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------------------
-- TEST
------------------------------------------------------------------
-- 
-- SELECT secure_random_bigint(), secure_random_int(), secure_random_double(), random();
-- 
-- |secure_random_bigint     |secure_random_int|secure_random_double|random      |
-- |-------------------------|-----------------|--------------------|------------|
-- |4.291.733.499.421.060.443|-1.598.898.611   |0,6776865484        |0,9446577135|

------------------------------------------------------------------
-- BENCHMARK: generate 1 million UUIDs and print the time taken
------------------------------------------------------------------
-- 
-- DO $$
-- DECLARE
-- v_start double precision;
-- v_end   double precision;
-- v_count bigint;   
-- BEGIN
-- 	v_start := extract(epoch from clock_timestamp());
-- 	with x as (select random() random from generate_series(1, 1000000))
-- 	select count(random) into v_count from x;
-- 	v_end := extract(epoch from clock_timestamp());
-- 	raise notice 'Time taken: %s', v_end - v_start;
-- END
-- $$ language plpgsql;
-- 
-- DO $$
-- DECLARE
-- v_start double precision;
-- v_end   double precision;
-- v_count bigint;   
-- BEGIN
-- 	v_start := extract(epoch from clock_timestamp());
-- 	with x as (select secure_random_bigint() random from generate_series(1, 1000000))
-- 	select count(random) into v_count from x;
-- 	v_end := extract(epoch from clock_timestamp());
-- 	raise notice 'Time taken: %s', v_end - v_start;
-- END
-- $$ language plpgsql;
--