Hologres:pgcrypto

Prerequisites

Before you begin, ensure that you have:

• A Hologres instance running V4.0 or later

• The pgcrypto extension enabled by running the following statement:

  CREATE EXTENSION IF NOT EXISTS pgcrypto;

Supported functions

Hologres V4.0 and later support the following pgcrypto functions. Hologres supports only the functions listed here, not all functions in the PostgreSQL pgcrypto extension.

Function reference

encode(data bytea, format text)

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

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

decode(str text, format text)

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

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

encrypt(data bytea, key bytea, algorithm text)

-- Encrypt data using AES-ECB and encode the result as Base64
SELECT encode(
    encrypt('hello world'::bytea, 'mysecretpassword'::bytea, 'aes-ecb/pad:pkcs'),
    'base64'
);
-- Result: An encrypted Base64 string

decrypt(data bytea, key bytea, algorithm text)

-- Decrypt a Base64-encoded ciphertext and convert it to a UTF-8 string
-- 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 bytea, encoding name)

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

Encrypt and store sensitive fields

The following example shows a complete workflow for encrypting sensitive fields — such as ID card numbers — before storing them, and decrypting them on read.

Step 1: Create a table with plaintext sensitive data

Create a source table and insert sample user records with plaintext ID card numbers and phone numbers.

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');

Step 2: Create a table for encrypted data

Create a separate table to store the encrypted version of the sensitive field.

CREATE TABLE mf_user_info_encrypt (
    id bigint,
    name text,
    gender text,
    id_card_no_encrypt text,  -- Stores Base64-encoded AES ciphertext
    tel text
);

Step 3: Encrypt the sensitive field and write to the encrypted table

Use encrypt() to encrypt each ID card number and encode() to store it as a Base64 string. Replace 'myencryptionkey' with a securely managed key — do not hard-code keys in application code.

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;

Step 4: Decrypt and query the sensitive field

Use decode() to reverse the Base64 encoding, decrypt() to recover the binary plaintext, and convert_from() to get a readable text string.

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;

The query returns the original plaintext ID card numbers alongside the other fields.

Usage notes

Security

• Choose encryption algorithms and key lengths that provide enough strength for your security requirements.

Key management

• Store and rotate encryption keys using a dedicated secrets manager. Never hard-code keys in application code.

Performance

• Encryption and decryption consume additional compute resources. The overhead depends on the algorithm, data size, and frequency of operations. Evaluate performance in a staging environment before applying column-level encryption at scale.

Encoding

• When handling multi-byte characters (such as Chinese text in GBK encoding), convert data to bytea before encryption and use convert_from() with the correct encoding after decryption to ensure consistent results.

Compatibility

The Hologres implementation of pgcrypto is compatible with PostgreSQL. Hologres supports only the functions listed in this topic, not all functions in the PostgreSQL pgcrypto extension.

References

• PostgreSQL pgcrypto documentation