AnalyticDB:pgcrypto

digest()

digest() computes a binary hash of the input data.

digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea

Example: Hash the string ADBPG with SHA256.

SELECT digest('ADBPG', 'sha256');

                               digest
--------------------------------------------------------------------
 \x2d0525fa98e83619424dc1303ba107c59aabb389bbe94ceef885c742088560bc
(1 row)

hmac()

hmac() computes a keyed Hash-based Message Authentication Code (HMAC). Unlike digest(), it requires a secret key, so only parties that know the key can verify the hash. This prevents an attacker from both altering the data and updating the hash to match.

hmac(data text, key text, type text) returns bytea
hmac(data bytea, key bytea, type text) returns bytea

Use hmac() instead of digest() when you need both data integrity and authenticity—for example, to verify that a message was not tampered with in transit.

Choosing an algorithm

Use bf for new implementations. It is adaptive, so you can increase the iteration count as hardware gets faster without invalidating existing hashes.

gen_salt()

gen_salt(type text [, iter_count integer]) returns text

Generates a random salt string that also selects the algorithm for crypt().

Example: Generate salts for all supported algorithms.

SELECT gen_salt('des'), gen_salt('xdes'), gen_salt('md5'), gen_salt('bf');

 gen_salt | gen_salt  |  gen_salt   |           gen_salt
----------+-----------+-------------+-------------------------------
 qh       | _J9..uEUi | $1$SNgqyKAi | $2a$06$B/Etc3J8zYBV49LrDU97MO
(1 row)

crypt()

crypt(password text, salt text) returns text

Hashes password using the algorithm encoded in salt. Pass the output of gen_salt() as salt when storing a new password. Pass the previously stored hash as salt when verifying a password.

End-to-end example: secure password storage

1. Create a table to store hashed passwords.

   CREATE TABLE users (
       id   serial PRIMARY KEY,
       name text,
       pswhash text
   );

2. Store a new password.

   INSERT INTO users (name, pswhash)
   VALUES ('alice', crypt('my_password', gen_salt('bf', 8)));

3. Verify a password during login. Returns true if the password matches, false otherwise.

   SELECT (pswhash = crypt('entered_password', pswhash)) AS pswmatch
   FROM users
   WHERE name = 'alice';

4. Verify an incorrect password.

   SELECT (pswhash = crypt('wrong_password', pswhash)) AS pswmatch
   FROM users
   WHERE name = 'alice';

    pswmatch
   ----------
    f
   (1 row)

Symmetric-key encryption

Use pgp_sym_encrypt() and pgp_sym_decrypt() when both parties share the same key.

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

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

Use pgp_sym_decrypt_bytea() to decrypt data that was originally in text form. pgp_sym_decrypt() rejects bytea input to prevent returning invalid character data.

Public-key encryption

Use pgp_pub_encrypt() and pgp_pub_decrypt() when you want to encrypt with a public key and decrypt with the corresponding private key.

pgp_pub_encrypt(data text, key bytea [, options text]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text]) returns bytea

pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text]]) returns text
pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text]]) returns bytea

Use pgp_pub_decrypt_bytea() to decrypt data that was originally in text form. pgp_pub_decrypt() rejects bytea input to prevent returning invalid character data.

gen_random_bytes()

gen_random_bytes(count integer) returns bytea

Returns count cryptographically strong random bytes, suitable for use as salts, initialization vectors, or session tokens.

Example:

SELECT gen_random_bytes(16);

          gen_random_bytes
------------------------------------
 \x1f1eddc11153afdde0f9e1229f8f4caf
(1 row)

gen_random_uuid()

gen_random_uuid() returns uuid

Returns a version 4 (randomly generated) UUID.

Example:

SELECT gen_random_uuid();

           gen_random_uuid
--------------------------------------
 2bd664a2-b760-4859-8af6-8d09ccc5b830
(1 row)

Encrypt data

-- Encrypt TEXT data
sm4_encrypt_text(<data text>, <password text>) returns bytea

-- Encrypt VARCHAR data
sm4_encrypt_varchar(<data varchar>, <password text>) returns bytea

Example 1: Encrypt a text string.

SELECT sm4_encrypt_text('who am i', 'key');

      sm4_encrypt_text
------------------------------------
 \x308b71cc7fa0de7d720b2c394a3a83c2
(1 row)

Example 2: Encrypt an entire column and insert the ciphertext into another table.

CREATE TABLE t_plain (
    id            int,
    name          varchar(20),
    introduction  text
) DISTRIBUTED BY (id);

INSERT INTO t_plain VALUES (1, 11, 'aaa');

CREATE TABLE t_enc (
    id            int,
    name          varchar(20),
    introduction  bytea
) DISTRIBUTED BY (id);

INSERT INTO t_enc
SELECT id, name, sm4_encrypt_text(introduction, 'passwd') FROM t_plain;

SELECT * FROM t_enc;

 id | name |            introduction
----+------+------------------------------------
  1 | 11   | \x0ca8e6c45a83e98efe99dcde0510930e
(1 row)

Decrypt data

-- Decrypt TEXT data
sm4_decrypt_text(data bytea, password text) returns text

-- Decrypt VARCHAR data
sm4_decrypt_varchar(data bytea, password text)

Example 1: Decrypt a previously encrypted value.

SELECT sm4_decrypt_text('\x308b71cc7fa0de7d720b2c394a3a83c2'::bytea, 'key');

 sm4_decrypt_text
------------------
 who am i
(1 row)

Example 2: Decrypt all rows from the encrypted table.

SELECT id, name, sm4_decrypt_text(introduction, 'passwd') FROM t_enc;

 id | name | sm4_decrypt_text
----+------+------------------
  1 | 11   | aaa
(1 row)