PostgreSQL 和 Greenplum pgcrypto 加解密bytea处理差异

PostgreSQL , pgcrypto , decode , encode , convert , convert_from , convert_to

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

<a href="https://www.postgresql.org/docs/devel/static/pgcrypto.html">https://www.postgresql.org/docs/devel/static/pgcrypto.html</a>

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

加密举例

解密举例

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)

Convert string to dest_encoding.

convert_to('some text', 'UTF8')

some text represented in the UTF8 encoding

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

解密举例, HEX格式需要去除<code>\x</code>。

greenplum用到了decode来转换bytea和text

decode(string text, format text)

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)

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格式。

<a href="https://github.com/digoal/blog/blob/master/201612/20161205_01.md">《PostgreSQL 按拼音排序 - convert to GBK/EUC_CN coding》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160921_02.md">《PostgreSQL Oracle 兼容性之 - 字符编码转换 CONVERT》</a>

<a href="https://github.com/digoal/blog/blob/master/201607/20160727_02.md">《固若金汤 - PostgreSQL pgcrypto加密插件》</a>