PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异-CSDN博客

背景

pgcrypto是一个加解密插件，可以对数据进行加解密，支持多种加密算法。

https://www.postgresql.org/docs/devel/static/pgcrypto.html

encrypt(data bytea, key bytea, type text) returns bytea  
decrypt(data bytea, key bytea, type text) returns bytea  
  
encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea  
decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea

在Greenplum和PostgreSQL中使用略有差异。

PostgreSQL 加解密

加密举例

postgres=# select encrypt('abcde'::bytea, 'hello_key'::bytea, 'aes');  
              encrypt                 
------------------------------------  
 \x670d356c4df5a5b6b6f37e0a0e5a8e93  
(1 row)

解密举例

postgres=# select decrypt('\x670d356c4df5a5b6b6f37e0a0e5a8e93', 'hello_key'::bytea, 'aes');  
   decrypt      
--------------  
 \x6162636465  
(1 row)  
  
postgres=# select convert_from(decrypt('\x670d356c4df5a5b6b6f37e0a0e5a8e93', 'hello_key'::bytea, 'aes'), 'sql_ascii');  
 convert_from   
--------------  
 abcde  
(1 row)

PostgreSQL用到了convert_from来转换bytea和text。

Function	Return Type	Description	Example	Result
convert(string bytea, src_encoding name, dest_encoding name)	bytea	Convert string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9.10 for available conversions.	convert('text_in_utf8', 'UTF8', 'LATIN1')	text_in_utf8 represented in Latin-1 encoding (ISO 8859-1)
convert_from(string bytea, src_encoding name)	text	Convert string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding.	convert_from('text_in_utf8', 'UTF8')	text_in_utf8 represented in the current database encoding
convert_to(string text, dest_encoding name)	bytea	Convert string to dest_encoding.	convert_to('some text', 'UTF8')	some text represented in the UTF8 encoding

Greenplum 加解密

Greenplum 早期的版本基于PostgreSQL 8.2改进而来，还没有convert_from函数。

加密举例

postgres=# select encrypt('abcde'::bytea, 'hello_key'::bytea, 'aes');  
                    encrypt                       
------------------------------------------------  
 g\0155lM\365\245\266\266\363~\012\016Z\216\223  
(1 row)

解密举例, HEX格式需要去除\x。

postgres=# select decrypt(decode('g\0155lM\365\245\266\266\363~\012\016Z\216\223', 'escape'), 'hello_key'::bytea, 'aes');  
 decrypt   
---------  
 abcde  
(1 row)  
  
postgres=# select decrypt(decode('670d356c4df5a5b6b6f37e0a0e5a8e93', 'hex'), 'hello_key'::bytea, 'aes');
 decrypt 
---------
 abcde
(1 row)

postgres=# select decrypt(decode(ltrim('\x670d356c4df5a5b6b6f37e0a0e5a8e93','\x'), 'hex'), 'hello_key'::bytea, 'aes');
 decrypt 
---------
 abcde
(1 row)

greenplum用到了decode来转换bytea和text

Function	Return Type	Description	Example	Result
decode(string text, format text)	bytea	Decode binary data from textual representation in string. Options for format are same as in encode.	decode('MTIzAAE=', 'base64')	\x3132330001
encode(data bytea, format text)	text	Encode binary data into a textual representation. Supported formats are: base64, hex, escape. escape converts zero bytes and high-bit-set bytes to octal sequences (\nnn) and doubles backslashes.	encode(E'123\000\001', 'base64')	MTIzAAE=

对于decrypt转换后得到的中文字符，需要增加greenplum的有convert_from 函数支持，否则只能显示为bytea格式。

-- PostgreSQL 8.3+

-- PostgreSQL 8.3+可以设置BYTEA类型的输出格式，HEX或ESCAPE格式。
postgres=# set bytea_output ='hex';
SET
postgres=# select '你'::bytea;
  bytea   
----------
 \xe4bda0
(1 row)

postgres=# set bytea_output ='escape';
SET
postgres=# select '你'::bytea;
    bytea     
--------------
 \344\275\240
(1 row)

postgres=# select convert_from(decrypt(ENCRYPT('你'::bytea, 'xm'::bytea,'aes'::text), 'xm'::bytea,'aes'::text), 'utf8');
 convert_from 
--------------
 你
(1 row)

-- Greenplum 早期版本, 没有convert_from函数，多字节字符经过decrypt后，输出BYTEA。  

postgres=# select decrypt(ENCRYPT('你'::bytea, 'xm'::bytea,'aes'::text), 'xm'::bytea,'aes'::text);
   decrypt    
--------------
 \344\275\240
(1 row)

postgres=# select encode(decrypt(ENCRYPT('你'::bytea, 'xm'::bytea,'aes'::text), 'xm'::bytea,'aes'::text), 'escape');
    encode    
--------------
 \344\275\240
(1 row)