-
-
Save kodekracker/3aca5bee52b8cfabaa75bddd2cbd4ce8 to your computer and use it in GitHub Desktop.
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; |
-- 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; |
The cool part: string generation
CREATE OR REPLACE FUNCTION public.int_to_string(n int)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
DECLARE
alphabet text:='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
base int:=length(alphabet);
output text:='';
BEGIN
LOOP
output := output || substr(alphabet, 1+(n%base)::int, 1);
n := n / base;
EXIT WHEN n=0;
END LOOP;
RETURN output;
END $function$;
Usage
psql=# SELECT int_to_string(feistel_crypt(1)), int_to_string(feistel_crypt(2)), int_to_string(feistel_crypt(3)), int_to_string(feistel_crypt(4));
int_to_string | int_to_string | int_to_string | int_to_string
---------------+---------------+---------------+---------------
5409L | t8hJD | Tj1aN | NTySG
(1 row)
Time: 0.473 ms
You can tune that character set as needed, of course. Maybe jumble it up a bit if you’re super paranoid about someone reverse engineering it. Your character set could be anything you wanted. A purely emoji set could be fun, or perhaps set it to an array of words to concatenate together instead of individual letters.
psql=# -- alphabet in above function instead set to 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
psql=# SELECT int_to_string(feistel_crypt(1)), int_to_string(feistel_crypt(2)), int_to_string(feistel_crypt(3)), int_to_string(feistel_crypt(4));
int_to_string | int_to_string | int_to_string | int_to_string
---------------+---------------+---------------+---------------
33FKKJ | XK9DIH | L79HTJ | 7TQ39H
(1 row)
Time: 0.681 ms
Bonus
At some point I ended up porting this to Python. It’s still super simple, and works just the same. But maybe seeing it in another form will help you port it to whatever other language you might need it for.
def pseudo_encrypt(value):
# A simple self-inverse Feistel cipher for ID obfuscation
l1 = (value >> 16) & 65535
r1 = value & 65535
for i in range(3):
key = (((1366 * r1 + 150889) % 714025) / 714025.0)
l2 = r1
r2 = l1 ^ int(key * 32767)
l1 = l2
r1 = r2
return (r1 << 16) + l1
def stringify_integer(value):
# Take an integer and encode it as a base(len(alphabet)) string
alphabet = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
base = len(alphabet)
output = ''
while value > 0:
output += alphabet[value%base]
value //= base
return output
Reference: https://www.endpointdev.com/blog/2020/07/random-strings-and-integers-that-actually-arent/
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.
/*
Encrypts an integer (4 bytes) with the Skip32 block cipher
based on Skipjack.
Arguments:
- int4 value to encrypt/decrypt
- bytea encryption key, 10 bytes long
- direction: true to encrypt, false to decrypt
Encrypt usage:
select skip32(1234, bytea '\xC0ffeeFaceC0ffeeFeed', true);
Decrypt usage:
select skip32(783287961, bytea '\xC0ffeeFaceC0ffeeFeed', false);
As each value encrypts into another unique value (given an encryption
key), this may be used to obfuscate an int4 primary key without loosing
the unicity property.
plpgsql implementation by Daniel Vérité.
Based on C code from:
SKIP32 -- 32 bit block cipher based on SKIPJACK.
Written by Greg Rose, QUALCOMM Australia, 1999/04/27.
See also:
http://search.cpan.org/~esh/Crypt-Skip32-0.17/
*/
create or replace function skip32(val int4, cr_key bytea, encrypt bool) returns int4
as $$
declare
kstep int;
k int;
wl int4;
wr int4;
g1 int4;
g2 int4;
g3 int4;
g4 int4;
g5 int4;
g6 int4;
ftable bytea:='\xa3d70983f848f6f4b321157899b1aff9e72d4d8ace4cca2e5295d91e4e3844280adf02a017f1606812b77ac3e9fa3d5396846bbaf2639a197caee5f5f7166aa239b67b0fc193811beeb41aead0912fb855b9da853f41bfe05a58805f660bd89035d5c0a733066569450094566d989b7697fcb2c2b0fedb20e1ebd6e4dd474a1d42ed9e6e493ccd4327d207d4dec7671889cb301f8dc68faac874dcc95d5c31a47088612c9f0d2b8750825464267d0340344b1c73d1c4fd3bccfb7fabe63e5ba5ad04239c145122f02979717eff8c0ee20cefbc72756f37a1ecd38e628b8610e8087711be924f24c532369dcff3a6bbac5e6ca9135725b5e3bda83a0105592a46';
begin
if (octet_length(cr_key)!=10) then
raise exception 'The encryption key must be exactly 10 bytes long.';
end if;
if (encrypt) then
kstep := 1;
k := 0;
else
kstep := -1;
k := 23;
end if;
wl := (val & -65536) >> 16;
wr := val & 65535;
for i in 0..11 loop
g1 := (wl>>8) & 255;
g2 := wl & 255;
g3 := get_byte(ftable, g2 # get_byte(cr_key, (4*k)%10)) # g1;
g4 := get_byte(ftable, g3 # get_byte(cr_key, (4*k+1)%10)) # g2;
g5 := get_byte(ftable, g4 # get_byte(cr_key, (4*k+2)%10)) # g3;
g6 := get_byte(ftable, g5 # get_byte(cr_key, (4*k+3)%10)) # g4;
wr := wr # (((g5<<8) + g6) # k);
k := k + kstep;
g1 := (wr>>8) & 255;
g2 := wr & 255;
g3 := get_byte(ftable, g2 # get_byte(cr_key, (4*k)%10)) # g1;
g4 := get_byte(ftable, g3 # get_byte(cr_key, (4*k+1)%10)) # g2;
g5 := get_byte(ftable, g4 # get_byte(cr_key, (4*k+2)%10)) # g3;
g6 := get_byte(ftable, g5 # get_byte(cr_key, (4*k+3)%10)) # g4;
wl := wl # (((g5<<8) + g6) # k);
k := k + kstep;
end loop;
return (wr << 16) | (wl & 65535);
end
$$ immutable strict language plpgsql;
Sample output:
SELECT
x,
encx AS encrypted,
skip32(encx, 'nooneknows'::bytea,false) AS decrypted
FROM (SELECT x, skip32(x, 'nooneknows'::bytea, true) AS encx
FROM generate_series(-10,10) AS x
) AS s;
x | encrypted | decrypted
-----+-------------+-----------
-10 | -487745093 | -10
-9 | -2112342827 | -9
-8 | 1303049886 | -8
-7 | -1084841580 | -7
-6 | 560956799 | -6
-5 | 82237967 | -5
-4 | 425659720 | -4
-3 | -2105383591 | -3
-2 | -1511018704 | -2
-1 | -1020536589 | -1
0 | 1500550465 | 0
1 | 1203450477 | 1
2 | 1404417409 | 2
3 | -495049695 | 3
4 | -1940533399 | 4
5 | -121441692 | 5
6 | 1957824249 | 6
7 | -1677320563 | 7
8 | 21505071 | 8
9 | -1867910739 | 9
10 | -1277220617 | 10
(21 rows)
Reference: https://wiki.postgresql.org/wiki/Skip32_(crypt_32_bits)
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;
Pseudo encrypt
pseudo_encrypt(int) can be used as a pseudo-random generator of unique values. It produces an integer output that is uniquely associated to its integer input (by a mathematical permutation), but looks random at the same time, with zero collision. This is useful to communicate numbers generated sequentially without revealing their ordinal position in the sequence (for ticket numbers, URLs shorteners, promo codes...)
The permutation property is a consequence of the function being a Feistel network; see http://en.wikipedia.org/wiki/Feistel_cipher
It performs a very simple encryption, without a key (in a way, the key is hardcoded in the algorithm).
To encrypt 32-bit values with a key, see Skip32 (still in plpgsql).
To encrypt 64-bit values with a key, see XTEA
To constrain the outputs to smaller, arbitrary ranges, such a numbers with N decimal digits, see Pseudo_encrypt_constrained_to_an_arbitrary_range
Notes:
r1
, which we can call it a key:key = ((1366 * r1 + 150889) % 714025) / 714025.0)
(inspired from random generators) with your own "secret sauce". The key must be a function in the mathematical sense (that is, if x=y then f(x)=f(y) ) and produce a value between 0 and 1.
Sample output:
Refrence: https://wiki.postgresql.org/wiki/Pseudo_encrypt