Pseudo encrypt

From PostgreSQL wiki
Jump to navigationJump to 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 (in a way, the key is hardcoded in the algorithm).


The first iteration of this code 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

C Version

Here's an equivalent function implemented in C:

#include "postgres.h"
#include "fmgr.h"
#include <math.h>

PG_FUNCTION_INFO_V1(simple_feistel_self_inverse);

Datum
simple_feistel_self_inverse(PG_FUNCTION_ARGS)
{
	int32 val = PG_GETARG_INT32(0);
	int32 l1 = (val >> 16) & 0xffff;
	int32 r1 = val & 0xffff;
	int32 l2, r2;
	int i;

	for (i = 0; i < 3; i++)
	{
		l2 = r1;
		/* round() is used to produce the same values as the
		   plpgsql implementation that does an SQL cast to INT */
		r2 = l1 ^ (int32)round((((1366*r1 + 150889) % 714025) / 714025.0) * 32767);
		l1 = l2;
		r1 = r2;
	}

	PG_RETURN_INT32((r1 << 16) + l1);
}

Create in SQL with:

CREATE OR REPLACE FUNCTION simple_feistel_self_inverse(int4)
RETURNS int4
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

See Also