XTEA（加密 64 位）
来自 PostgreSQL wiki
（从 XTEA 重定向）
跳转到导航跳转到搜索
xtea 使用长度为 16 个字节（128 位）的 bytea 类型密钥或 4 个 int4 值的等效数组，对单个 int8（64 位）值执行加密或解密操作。
它可以用来生成序列的看似随机的唯一大值，或混淆 BIGSERIAL 主键而不会丢失其唯一性属性。
XTEA 算法是密码学中使用的一种分组密码，请访问 https://en.wikipedia.org/wiki/XTEA
还可以在 PGXN 上通过 cryptint 分机获得 C 实现。它的运行速度比这里提出的 plpgsql 版本快得多，但需要超级用户进行编译和安装。
/*
Encrypts a bigint/int8 (8 bytes) with the XTEA block cipher.
Arguments:
- int8 (bigint) value to encrypt/decrypt
- bytea encryption key, 16 bytes long
OR array of four integers: int4[4]
- direction: true to encrypt, false to decrypt
- Encrypt usage first option (with encryption key as bytea):
select xtea(1234, bytea '\x1234567890ABC0ffeeFaceC0ffeeFeed', true);
- Corresponding decrypt usage:
select xtea(-7937660076067879872, bytea '\x1234567890ABC0ffeeFaceC0ffeeFeed', false);
- Encrypt usage second option (with encryption key as int4[4]):
select xtea(1234,
array[305419896,-1867792129,-285552960,-1114387]::int[],
true);
- Corresponding decrypt usage:
select xtea(-7937660076067879872,
array[305419896,-1867792129,-285552960,-1114387]::int[],
false);
As each value encrypts into another unique value (given an encryption
key), this may be used to obfuscate an int8 primary key without loosing
the unicity property.
The binary encryption key is equivalent to the big-endian representation
of 4 consecutive signed integers in the int4[] array.
plpgsql implementation by Daniel Vérité.
Based on C code from David Wheeler and Roger Needham.
source: https://en.wikipedia.org/wiki/XTEA
The plpgsql code is more complex than its C counterpart because it emulates
unsigned 32 bits integers and modulo 32-bit arithmetic with the bigint type.
*/
create or replace function xtea(val bigint, cr_key bytea, encrypt boolean)
returns bigint as $$
declare
bk int[4];
b bigint; -- unsigned 32 bits
begin
if octet_length(cr_key)<>16 then
raise exception 'XTEA crypt key must be 16 bytes long.';
end if;
for i in 1..4 loop
b:=0;
for j in 0..3 loop
-- interpret cr_key as 4 big-endian signed 32 bits numbers
b:= (b<<8) | get_byte(cr_key, (i-1)*4+j);
end loop;
bk[i] := case when b>2147483647 then b-4294967296 else b end;
end loop;
return xtea(val, bk, encrypt);
end
$$ immutable language plpgsql;
create or replace function xtea(val bigint, key128 int4[4], encrypt boolean)
returns bigint as $$
declare
-- we use bigint (int8) to implement unsigned 32 bits with modulo 32 arithmetic
-- (in C, uint32_t is used but pg's int4 is signed and would overflow).
-- the most significant halves of v0,v1,_sum must always be zero
-- they're AND'ed with 0xffffffff after every operation
v0 bigint;
v1 bigint;
_sum bigint:=0;
cr_key bigint[4]:=array[
case when key128[1]<0 then key128[1]+4294967296 else key128[1] end,
case when key128[2]<0 then key128[2]+4294967296 else key128[2] end,
case when key128[3]<0 then key128[3]+4294967296 else key128[3] end,
case when key128[4]<0 then key128[4]+4294967296 else key128[4] end
];
begin
v0 := (val>>32)&4294967295;
v1 := val&4294967295;
IF encrypt THEN
FOR i in 0..63 LOOP
v0 := (v0 + ((
((v1<<4)&4294967295 # (v1>>5))
+ v1)&4294967295
#
(_sum + cr_key[1+(_sum&3)::int])&4294967295
))&4294967295;
_sum := (_sum + 2654435769) & 4294967295;
v1 := (v1 + ((
((v0<<4)&4294967295 # (v0>>5))
+ v0)&4294967295
#
(_sum + cr_key[1+((_sum>>11)&3)::int])&4294967295
))&4294967295;
END LOOP;
ELSE
_sum := (2654435769 * 64)&4294967295;
FOR i in 0..63 LOOP
v1 := (v1 - ((
((v0<<4)&4294967295 # (v0>>5))
+ v0)&4294967295
#
(_sum + cr_key[1+((_sum>>11)&3)::int])&4294967295
))&4294967295;
_sum := (_sum - 2654435769)& 4294967295;
v0 := (v0 - ((
((v1<<4)&4294967295 # (v1>>5))
+ v1)&4294967295
#
(_sum + cr_key[1+(_sum&3)::int])&4294967295
))&4294967295;
END LOOP;
END IF;
return (v0<<32)|v1;
end
$$ immutable strict language plpgsql;
样本输出
SELECT
x,
encx AS encrypted,
xtea(encx, 'nooneknowsthekey'::bytea,false) AS decrypted
FROM (SELECT x, xtea(x, 'nooneknowsthekey'::bytea, true) AS encx
FROM generate_series(-10,10) AS x
) AS s;
x | encrypted | decrypted
-----+----------------------+-----------
-10 | 4385243210905785209 | -10
-9 | 8069258762620289669 | -9
-8 | 3926559087555398168 | -8
-7 | -8988258197004549588 | -7
-6 | 3551076798823338680 | -6
-5 | 7365416518795732112 | -5
-4 | 136212175735208317 | -4
-3 | 3098188211073624918 | -3
-2 | 5824967969120338177 | -2
-1 | -463468193554373329 | -1
0 | -7485772404085155809 | 0
1 | -1311071933951566764 | 1
2 | -4708675461424073238 | 2
3 | -6865005668390999818 | 3
4 | 5578000650960353108 | 4
5 | -3219674686933841021 | 5
6 | -6469229889308771589 | 6
7 | -606871692563545028 | 7
8 | -8199987422425699249 | 8
9 | -463287495999648233 | 9
10 | 7675955260644241951 | 10
(21 rows)
