pgcrypto encode decode encrypt decrypt convert_from-Hologres(Hologres)-阿里云帮助中心

Overview

The pgcrypto extension provides functions for data encryption, decryption, and encoding to protect sensitive data. Before using these functions, you must create the extension by running the following command: CREATE EXTENSION IF NOT EXISTS pgcrypto;

Limitations

The pgcrypto functions described in this topic are supported only in Hologres V4.0 and later versions.
The pgcrypto functions run only in the PQE engine. Do not use them in the same SQL statement with functions that run exclusively in the HQE engine, such as Remote UDFs. Mixing these functions causes an execution engine conflict and returns the following error:
```
ERROR: ORCA failed to produce a plan : No plan has been computed for required properties
```
To use pgcrypto functions and Remote UDFs together, split the operations into separate SQL statements.

Supported functions

Hologres supports the following pgcrypto functions:

Function	Description
encode(data, format)	Encodes binary data into a text representation based on a specified format.
decode(str, format)	Decodes a string from a specified format into binary data.
encrypt(data, key, algorithm)	Encrypts data using a specified algorithm and key.
decrypt(data, key, algorithm)	Decrypts data using a specified algorithm and key.
convert_from(data, encoding)	Converts binary data from a specified character encoding to the database's internal representation, such as UTF-8.

Function details

encode(data, format)

Description: Encodes binary data (type bytea) into a text string based on the specified format.
Syntax
```
encode(data bytea, format text)
```
Parameters
- data: The binary data to be encoded.
- format: The encoding format, which can be hex, base64, or escape.
Return value: The encoded text string.

Example

-- Encode binary data as hex
SELECT encode('\xDEADBEEF'::bytea, 'hex');
-- Result: deadbeef

-- Encode binary data as Base64
SELECT encode('\x12345678'::bytea, 'base64');
-- Result: EjRWeA==

decode(str, format)

Description: Decodes a text string from a specified format into binary data (bytea).
Syntax
```
decode(str text, format text)
```
Parameters
- str: The string to be decoded.
- format: The decoding format. It supports hex, base64, and escape.
Return value: The decoded binary data (bytea).

Example

-- Decode a hex string into binary data
SELECT decode('deadbeef', 'hex');
-- Result: \xdeadbeef

-- Decode a Base64 string into binary data
SELECT decode('EjRWeA==', 'base64');
-- Result: \x12345678

encrypt(data, key, algorithm)

Description: Encrypts data using a specified key and algorithm.

Syntax

encrypt(data bytea, key bytea, algorithm text)

Parameters
- data: The raw data to be encrypted.
- key: The encryption key.
- algorithm: The encryption algorithm, such as aes or aes-ecb/pad:pkcs.
Return value: The encrypted binary data (bytea).

Example

-- Encrypt data using the AES algorithm and output the result as a Base64 string
SELECT encode(encrypt('hello world'::bytea, 'mysecretpassword'::bytea, 'aes-ecb/pad:pkcs'), 'base64');
-- Result: The encrypted Base64 string. The output varies with each execution due to cryptographic randomness.

decrypt(data, key, algorithm)

Description: Decrypts data with a specified key and algorithm. The key and algorithm must match those used for encryption.

Syntax

decrypt(data bytea, key bytea, algorithm text)

Parameters
- data: The data to decrypt.
- key: The decryption key (the same key used for encryption).
- algorithm: The decryption algorithm (the same as the one used for encryption).
Return value: The decrypted binary data (bytea), which is often used with convert_from to obtain plaintext.

Example

-- Decrypt the Base64 ciphertext and convert it to UTF-8 text. 
-- Replace <encrypted_base64_string> with the actual ciphertext.
SELECT convert_from(
    decrypt(
        decode('<encrypted_base64_string>', 'base64'),
        'mysecretpassword'::bytea,
        'aes-ecb/pad:pkcs'
    ),
    'utf8'
);
-- Result: The decrypted plaintext

convert_from(data, encoding)

Description: Converts binary data from a specified character encoding to the database's internal representation, such as UTF-8.
Syntax
```
convert_from(data bytea, encoding name)
```
Parameters
- data: The binary data with a specific encoding.
- encoding: The character encoding of the source data, such as utf8, latin1, and gbk.
Return value: The converted text string.

Example

-- Convert GBK-encoded binary data to text
SELECT convert_from('\xb0\xc5\xba\xcd'::bytea, 'gbk');
-- Result: Chinese characters

Practical example

This example shows how to use pgcrypto functions to encrypt sensitive fields (such as ID card numbers) for storage and decrypt them for queries.

-- Enable the pgcrypto extension.
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create a user information table that contains sensitive data.
CREATE TABLE mf_user_info (
    id bigint,
    name text,
    gender text,
    id_card_no text,
    tel text
);

INSERT INTO mf_user_info VALUES
(1, 'bob', 'male', '0001', '13900001234'),
(2, 'allen', 'male', '0011', '13900001111'),
(3, 'kate', 'female', '0111', '13900002222'),
(4, 'annie', 'female', '1111', '13900003333');

-- Create a table for encrypted storage.
CREATE TABLE mf_user_info_encrypt (
    id bigint,
    name text,
    gender text,
    id_card_no_encrypt text,
    tel text
);

-- Encrypt the sensitive data and write it to the new table.
INSERT INTO mf_user_info_encrypt
SELECT
    id, name, gender,
    encode(encrypt(id_card_no::text::bytea, 'myencryptionkey'::bytea, 'aes-ecb/pad:pkcs'), 'base64') AS id_card_no_encrypt,
    tel
FROM mf_user_info;

-- Decrypt and query the ID card numbers.
SELECT id, name, gender,
    convert_from(decrypt(decode(id_card_no_encrypt, 'base64'), 'myencryptionkey'::bytea, 'aes-ecb/pad:pkcs'), 'utf8') AS id_card_no,
    tel
FROM mf_user_info_encrypt;

Usage notes

Security: Choose an encryption algorithm and key length that meet your security requirements.
Performance: Encryption and decryption consume additional computing resources. Consider the trade-off between performance and security.
Key management: Manage your encryption keys securely. Avoid hard-coding keys in your application code.
Encoding: When working with multi-byte characters, ensure consistent character encoding. For example, convert data to bytea before encryption and specify the correct encoding with convert_from after decryption.

References

For more information about pgcrypto, see the official PostgreSQL pgcrypto Documentation.

Compatibility

Hologres's implementation of pgcrypto is compatible with PostgreSQL. However, Hologres supports only the functions listed in this topic, not the complete set of functions from the PostgreSQL pgcrypto extension.