本期分享两个案例：

• 跨平台本地化兼容：Locale=C
• AES标准算法加解密：从Hive到PG无缝衔接

跨平台OS与Locale问题

数据目录初始化时推荐设置locale为C：

$ initdb -U postgres -D D:\postgresql\pg13.8\data -E UTF8 --locale=C

• 初始化时Locale的设置会影响两个排序相关GUC参数：lc_collate和lc_ctype参数
• 15+这俩参数已无实质作用，因为db级可设置ICU Locale，从此Locale非全局
• 16+已废弃这两全局只读参数，否则与ICU设置有歧义
• 大版本升级到16+，如果Locale为非C，可能会升级失败：例如从Windows到Linux

例如下面的实例是从Windows使用pg_basebackup拷贝过来，但数据库服务启动则报错，因为初始化时没有使用C

FATAL: database locale is incompatible with operating system
DETAIL: The database was initialized with LC_COLLATE "Chinese (Simplified)_China.936", which is not recognized by setlocale().

下面是技术群里的老郑测试locale为C时，Windows与Linux异构平台流复制的截图。

从Hive到PG：AES算法的开发衔接

使用hive sql 2.3版本的加密语句，如下所示:

select base64(aes_encrypt('hell world','a_long_secret_0123456789')) encrypted_data;

生成密文为：

M2u7fyoGR9eIFJ0ff/L4Eg==

迁移到PG，需要先安装pgcrypto插件

create extension pgcrypto ;

先测试正向明文AES + BASE64加密，看看密文是否一致

=# SELECT encode(encrypt('hell world', 'a_long_secret_0123456789', 'aes') ,'base64') AS encryptstr;
        encryptstr        
--------------------------
 M2u7fyoGR9eIFJ0ff/L4Eg==
(1 row)

验证成功，接着正式开始对密文串：M2u7fyoGR9eIFJ0ff/L4Eg== 进行解密：

首先脱掉BASE64的外衣，使用decode函数还原出AES加密的二进制裸数据：

=# SELECT decode('M2u7fyoGR9eIFJ0ff/L4Eg==', 'base64');
               decode               
------------------------------------
 \x336bbb7f2a0647d788149d1f7ff2f812
(1 row)

如上，二进制数据默认显示为hex格式，使用decrypt进行AES解密密文输入时，注意这里要使用两个转义符

=# select decrypt(E'\\x336bbb7f2a0647d788149d1f7ff2f812','a_long_secret_0123456789', 'aes');
        decrypt         
------------------------
 \x68656c6c20776f726c64
(1 row)

上面解密出来为二进制，hex格式的数据其实就是明文的hello world。

只需设置二进制的输出格式为转义模式即可：

=# set bytea_output to 'escape';
SET
=# select decrypt(E'\\x336bbb7f2a0647d788149d1f7ff2f812','a_long_secret_0123456789', 'aes');
  decrypt   
------------
 hell world
(1 row

不过set设置参数的方式对业务开发使用人员不太友好，更好的方式是使用encode函数进行处理：

=# reset bytea_output;
RESET
=# select encode(E'\\x68656c6c20776f726c64', 'escape');
   encode   
------------
 hell world
(1 row)

于是，最终版本为：

=# select encode(decrypt(decode('M2u7fyoGR9eIFJ0ff/L4Eg==', 'base64'),'a_long_secret_0123456789', 'aes'), 'escape');
   encode   
------------
 hell world
(1 row)