A pseudo_encrypt function implementation for int and bigint

pseudo_encrypt.sql

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;

pseudo_encrypt_bigint.sql

-- 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;

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;