Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Storage Encryption

The pgcrypto module provides two types of data encryption algorithms: one-way encryption and two-way encryption, enabling data encryption at the database level to ensure secure data usage.

  • One-way encryption is irreversible; plaintext cannot be recovered from ciphertext. It is suitable for data validation, such as password authentication during login. Common one-way encryption algorithms include MD5, SHA, HMAC, etc.

  • Two-way encryption is reversible; plaintext can be decrypted from ciphertext using a key. It is suitable for secure data transmission scenarios such as electronic payments and digital signatures. Common two-way encryption algorithms include AES, DES, RSA, ECC, SM4, etc.

One-Way Encryption

Standard Hash Functions

  • The digest() function generates a binary hash value for data using different algorithms. This type of algorithm produces the same result when encrypting identical data.
# Syntax
digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea
# 'type' specifies the algorithm to use. Standard options are md5, sha1, sha224, sha256, sha384, and sha512.

# Example usage

INSERT INTO users(username, password) 
VALUES ('tony', encode(digest('123456','md5'), 'hex'));

INSERT INTO users(username, password) 
VALUES ('anne', encode(digest('123456','md5'), 'hex'));

SELECT * FROM users;
id|username|password                        |
--|--------|--------------------------------|
 1|tony    |e10adc3949ba59abbe56e057f20f883e|
 2|anne    |e10adc3949ba59abbe56e057f20f883e|


# During comparison, use the encode() function to convert to hexadecimal
SELECT id FROM users
WHERE username = 'tony' AND password = encode(digest('123456','md5'), 'hex');
id|
--|
 1|
  • The hmac() function generates a binary hash value for data with a secret key (psw) using various algorithms. Similar to digest(), but the hash can only be reproduced if the same key is used.
# Syntax
hmac(data text, psw text, type text) returns bytea
hmac(data bytea, psw text, type text) returns bytea
# 'type' specifies the algorithm to use. Standard options are md5, sha1, sha224, sha256, sha384, and sha512.

Two-Way Encryption

PGP Encryption Functions

The PGP encryption functions implement encryption features defined in the OpenPGP standard (RFC4880), including symmetric-key encryption (private-key encryption) and asymmetric-key encryption (public-key encryption).

# Encryption
pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea

# Decryption
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea


# Example usage

select pgp_sym_encrypt('abc','key_value');
                                                                 pgp_sym_encrypt                                                               
  ----------------------------------------------------------------------------------------------------------------------------------------------
   \xc30d04070302f93fbd59b40bf7fd71d2340175c19d234d275f5b8ae668fecbbdfd80f0e94185f07dee15cb6d2b0dfbfdf08c98648e07da8f3d8902bb3dd349fdb36860a1ff
  (1 row)

  select pgp_sym_decrypt(pgp_sym_encrypt('abc','key_value'),'key_value');             
   pgp_sym_decrypt
  -----------------
   abc
  (1 row)

  # Common options:
  # cipher-algo: encryption algorithm to use; possible values are bf, aes128 (default), aes192, aes256
  # compress-algo: compression algorithm; available only if PostgreSQL was compiled with zlib support. Possible values: 0 (no compression, default), 1 (ZIP), 2 (ZLIB, which includes metadata and CRC)

SM4 Encryption Functions

SM4 is a symmetric encryption algorithm and falls under two-way encryption. It is a commercial cryptographic algorithm standard published by the Chinese State Cryptography Administration. SM4 uses 128-bit block size and key length, achieving efficient encryption and decryption through multiple rounds of iteration. It is widely used in mobile communications, IoT, finance, and other fields.

# Encryption
encrypt(data text, psw text [, options text ]) returns bytea;

# Decryption
decrypt(msg bytea, psw text [, options text ]) returns bytea;

# Example usage
create table t1 (c1 bytea);

insert into t1 select encrypt('123456', '1462846455', 'sm4');

select * from t1;
                   c1                 
  ------------------------------------
   \xaf8aac27b4721d733852c18e9a6dd126
  (1 row)

select convert_from(public.decrypt(c1, '1462846455', 'sm4'),'utf-8') from t1;
   convert_from 
  --------------
   123456
  (1 row)

# Common option:
# cipher-algo: the encryption algorithm used is sm4
← Previous
SSL Transmission
Next →
TDE