如何在PostgreSQL中将字节数组编码为Base32

Question

如何在PostgreSQL中将字节数组编码为Base32

postgresql

3

我有一个Java应用程序，通过将加密随机字节编码为base32（RFC4648）来生成主键的id。

如何在SQL脚本中使用PostgreSQL实现相同的功能？

gen_random_bytes似乎可以生成随机字节，但似乎没有可用于将它们编码为base32的任何内容。

- b0gusb

2个回答

2

好奇是否可以将任意长度的 bytea 转换为 base32。以下是带有几个辅助函数以便于理解的 SQL 代码。所有使用的内置函数似乎都被标记为 immutable，因此这些辅助函数应该很容易地内联。

在 Postgres 14 上进行了测试：

create or replace function public.bytea_to_varbit(p_bytes bytea)
returns varbit as $$
-- https://dev59.com/9bPma4cB1Zd3GeqPua_C#56008861
select right(p_bytes::text, -1)::varbit;
$$ language sql immutable;

create or replace function public.varbit40_to_base32(p_num varbit(40))
returns text as $$
                    -- https://www.rfc-editor.org/rfc/rfc4648#section-6
    select string_agg(('{"A","B","C","D","E","F","G","H"
                        ,"I","J","K","L","M","N","O","P"
                        ,"Q","R","S","T","U","V","W","X"
                        ,"Y","Z","2","3","4","5","6","7"}'::text[])
                     -- shift right to 5 lsb and mask off the rest
                        [(p_num >> s & 31::bit(40))::bigint::int + 1]
                    -- concatenate resulting characters in order from left to right
                        , '' order by o)
        -- generate bit-shifts to move all five-bit positions to the right
      from generate_series(35, 0, -5) with ordinality m(s, o)
$$ language sql immutable;

create or replace function public.bytea_to_base32(p_val bytea, p_omit_padding bool = true)
returns text as $$
with v(val, padding, trm) as (
    select string_agg(varbit40_to_base32(bytea_to_varbit(substring (padded from b for 5))), '' order by o)
         -- https://www.rfc-editor.org/rfc/rfc4648#section-6 (1)-(5):
         , ('{"","======","====","===","="}'::text[])[rem + 1]
         , ('{0,6,4,3,1}'::int[])[rem + 1]
      from (values(length(p_val), length(p_val) % 5 
        -- pad length to be divisible by 5
         , p_val || substring(bytea '\x0000000000' from 1 for (5 - length(p_val) % 5) % 5)) 
           ) v(len, rem, padded)
        -- rfc 4648 6: breakpoints for breaking the byte string into 40-bit groups
         , generate_series(1, len + (5 - rem) % 5, 5) with ordinality c(b,o)
     group by p_val, rem
)                
-- rfc4648 3.2
select case when p_omit_padding then
            case when trm > 0 then left(val, -trm) else val end
       else
            case when trm > 0 then overlay(val placing padding from length(val)-trm+1) else val end
       end
       from v

       union all
       -- https://www.rfc-editor.org/rfc/rfc4648#section-10: empty input returns empty string
       select ''
 limit 1;
$$ language sql immutable strict;

/*
https://www.rfc-editor.org/rfc/rfc4648#section-10
select inp, expected, bytea_to_base32(inp::bytea, omit_padding)
  from (values
         ('', '', true)
       , ('f', 'MY', true)
       , ('fo', 'MZXQ', true)
       , ('foo', 'MZXW6', true)
       , ('foob', 'MZXW6YQ', true)
       , ('fooba', 'MZXW6YTB', true)
       , ('foobar', 'MZXW6YTBOI', true)
       , ('', '', false)
       , ('f', 'MY======', false)
       , ('fo', 'MZXQ====', false)
       , ('foo', 'MZXW6===', false)
       , ('foob', 'MZXW6YQ=', false)
       , ('fooba', 'MZXW6YTB', false)
       , ('foobar', 'MZXW6YTBOI======', false)
       ) t(inp, expected, omit_padding)
  where bytea_to_base32(inp::bytea, omit_padding) <> expected
;
*/

基本上，您需要将输入分解为5字节组，然后从左到右将其分解为八个5位字符串。

最简单的方法是使用generate_series生成断点位置，然后使用substring(x from b for desired_len)来划分字节字符串。

奇怪的双余数（(5-len()%5)%5）是为了正确地夹紧填充长度（否则对于余数为0的情况，您会得到额外的5字节填充，因为5-0=5，但实际上您希望该情况下的填充长度为0）。

我发现数组文字查找（即（{'A', ... }::text[])[position_calculation]部分）使代码更加明显，尽管有些嘈杂。

要按RFC4648 section 7生成base_32_hex，只需在varbit_to_base32中替换字母表数组。或者，您可以将字母表作为varbit40_to_base32的输入，并从调用者传递正确的字母表。

- Mr. Curious

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- pyramation · Accepted Answer

我几年前搜索了这个问题，最终构建了一个纯plpgsql模式和处理函数来处理它，并想在这里留下它，以便任何人遇到时可以使用。感谢这个视频和RFC：https://www.youtube.com/watch?v=Va8FLD-iuTg，https://www.rfc-editor.org/rfc/rfc4648。这是扩展程序：https://github.com/pyramation/base32/blob/master/packages/base32/sql/launchql-base32--0.0.1.sql。安装后，您可以执行以下操作：

select base32.encode('foo');
-- MZXW6===


select base32.decode('MZXW6===');
-- foo