Last active
April 27, 2022 19:00
-
-
Save kodekracker/3aca5bee52b8cfabaa75bddd2cbd4ce8 to your computer and use it in GitHub Desktop.
A pseudo_encrypt function implementation for int and bigint
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
CREATE OR REPLACE FUNCTION pseudo_encrypt(value int) returns int AS $$ | |
DECLARE | |
l1 int; | |
l2 int; | |
r1 int; | |
r2 int; | |
i int:=0; | |
BEGIN | |
l1:= (value >> 16) & 65535; | |
r1:= value & 65535; | |
WHILE i < 3 LOOP | |
l2 := r1; | |
r2 := l1 # ((((1366 * r1 + 150889) % 714025) / 714025.0) * 32767)::int; | |
l1 := l2; | |
r1 := r2; | |
i := i + 1; | |
END LOOP; | |
return ((r1 << 16) + l1); | |
END; | |
$$ LANGUAGE plpgsql strict immutable; |
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
-- https://stackoverflow.com/a/12761795/2026325 | |
CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE bigint) returns bigint AS $$ | |
DECLARE | |
l1 bigint; | |
l2 bigint; | |
r1 bigint; | |
r2 bigint; | |
i int:=0; | |
BEGIN | |
l1:= (VALUE >> 32) & 4294967295::bigint; | |
r1:= VALUE & 4294967295; | |
WHILE i < 3 LOOP | |
l2 := r1; | |
r2 := l1 # ((((1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767*32767)::int; | |
l1 := l2; | |
r1 := r2; | |
i := i + 1; | |
END LOOP; | |
RETURN ((l1::bigint << 32) + r1); | |
END; | |
$$ LANGUAGE plpgsql strict immutable; |
A random id generator
CREATE SEQUENCE global_id_sequence;
CREATE
OR REPLACE FUNCTION id_generator(OUT result bigint) AS $$
DECLARE our_epoch bigint := 1314220021721;
seq_id bigint;
now_millis bigint;
-- the id of this DB shard, must be set for each
-- schema shard you have - you could pass this as a parameter too
shard_id int := 1;
BEGIN
SELECT
nextval('global_id_sequence') % 1024 INTO seq_id;
SELECT
FLOOR(
EXTRACT(
EPOCH
FROM
clock_timestamp()
) * 1000
) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Skip32 (crypt 32 bits)
skip32 encrypts or decrypts a single int4 (32 bits) value with a 10 bytes (80 bits) key of bytea type.
It may be used to generate series of unique values that look random, or to obfuscate a SERIAL primary key without loosing its unicity property.
Skip32 is based on the Skipjack block cipher.
A C implementation is also available through the cryptint extension on PGXN. It runs much faster than the plpgsql version proposed here, but needs compilation and installation by a superuser.
Warning: algorithms that use short encryption keys like Skipjack are considered too easy to breach by a sufficiently motivated attacker with today's computing power and cryptanalysis knowledge. Don't use this for real cryptography.
Sample output:
Reference: https://wiki.postgresql.org/wiki/Skip32_(crypt_32_bits)