Pseudo encrypt

From PostgreSQL wiki

Jump to: navigation, search

Library Snippets

pseudo_encrypt

Works with PostgreSQL

Any version

Written in

PL/pgSQL

Depends on

Nothing


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 (the key is hardcoded in the algorithm).

The first iteration was posted in http://archives.postgresql.org/pgsql-general/2009-05/msg00082.php by Daniel Vérité; below is an improved version, following comments by Jaka Jancar.

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;

Notes:

  • it returns a signed integer (postgres doesn't have unsigned integers anyway).
  • it's a self-inverse, that is: pseudo_encrypt(pseudo_encrypt(X)) = X
  • the output may be customized by changing this function of r1:

((1366 * r1 + 150889) % 714025) / 714025.0) (inspired from random generators) with your own "secret sauce". The replacement 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:

 select x,pseudo_encrypt(x) from generate_series(-10,10) as x;

  x  | pseudo_encrypt 
-----+----------------
 -10 |    -1270576520
  -9 |     -236348969
  -8 |    -1184061109
  -7 |      -25446276
  -6 |    -1507538963
  -5 |     -518858927
  -4 |    -1458116927
  -3 |     -532482573
  -2 |     -157973154
  -1 |    -1105881908
   0 |     1777613459
   1 |      561465857
   2 |      436885871
   3 |      576481439
   4 |      483424269
   5 |     1905133426
   6 |      971249312
   7 |     1926833684
   8 |      735327624
   9 |     1731020007
  10 |      792482838
Personal tools