AnalyticDB:pgcrypto

Usage notes

The pgcrypto extension runs inside the database server. All data and passwords are transferred between the database server and your client in plaintext. To ensure optimal security, we recommend that you establish SSL connections between your client and AnalyticDB for PostgreSQL instances.

Install the pgcrypto extension

Before you use the pgcryptoto extension in an AnalyticDB for PostgreSQL instance, install the pgcryptoto extension on the Extensions page of the instance. For more information, see Install, update, and uninstall extensions.

General hash functions

The digest() function is used to calculate a binary hash value of data based on different algorithms.

Syntax

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

• data: the raw data for which you want to calculate a binary hash value.

• type: the encryption algorithm. Valid values: MD5, SHA1, SHA224, SHA256, SHA384, and SHA512.

Example

Use the digest() function to perform SHA256 encryption on the ADBPG string.

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

Sample result:

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

Password hash functions

The crypt() and gen_salt() functions are designed to hash passwords. The crypt() function performs hashing to encrypt data and the gen_salt() function generates salt values for the crypt() function to generate salted hashes.

The algorithms that are used in the crypt() function differ from the common MD5 and SHA1 hash algorithms in the following aspects:

• The algorithms that are used in the crypt() function require a long period of time to encrypt passwords. In most cases, passwords are small in length, and the crypt() function can only use complex encryption algorithms to increase the difficulty of decrypting passwords.

• The crypt() function uses a random salt value so that users that have the same password obtain different ciphertext passwords. This is an additional defense against cracking the algorithm.

• The crypt() function returns the type of the algorithm in the result. You can use different types of algorithms to encrypt passwords for different users.

• Specific algorithms that are used in the crypt() function are adaptive. When the computer performance increases, you can slow down the algorithm without incompatibility with existing passwords.

Supported algorithms for the crypt() function

crypt() syntax

crypt(password text, salt text) returns text 

The crypt() function is used to return the hash value of a password string. The salt parameter is generated by the gen_salt() function. For the same password string, the crypt() function returns different results because the gen_salt() function generates a different salt value each time. When you verify the password, you can use the previous hash result as the salt value.

crypt() examples

• Example 1: Use the crypt() function to configure a password.

  UPDATE ... SET pswhash = crypt('my_password', gen_salt('md5')); 

• Example 2: Compare password hash values to check whether the password is correct.

  SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ; 

  If the entered password is correct, true is returned.

gen_salt() syntax

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

The gen_salt() function is used to generate a random salt string for use in the crypt() function. The salt string also determines the algorithm to be used in the crypt() function.

type: the hash algorithm. Valid values: DES, XDES, MD5, and BF.

iter_count: the iteration count. For the XDES and BF algorithms, a higher count indicates more time to hash and crack the password.

gen_salt() examples

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

Sample result:

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

PGP encryption functions

AnalyticDB for PostgreSQL implements the OpenPGP (RFC 4880) standard. Symmetric encryption and asymmetric encryption are supported.

pgp_sym_encrypt()

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

The pgp_sym_encrypt() function is used to encrypt data by using a symmetric Pretty Good Privacy (PGP) key.

• data: the data to be encrypted.

• psw: the symmetric PGP key.

• options: the option settings.

pgp_sym_decrypt()

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

The pgp_sym_decrypt() function is used to decrypt a symmetric-key-encrypted message.

• msg: the message to be decrypted.

• psw: the symmetric PGP key.

• options: the option settings.

You cannot use the pgp_sym_decrypt function to decrypt BYTEA data. This avoids outputting invalid character data. You can use the pgp_sym_decrypt_bytea function to decrypt the original textual data.

pgp_pub_encrypt()

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

The pgp_pub_encrypt() function is used to encrypt data by using a public PGP key.

• data: the data to be encrypted.

• key: the public PGP key. If you specify a private key, an error message is returned.

• options: the option settings.

pgp_pub_decrypt()

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 

The pgp_pub_decrypt() function is used to decrypt a public-key-encrypted message.

• msg: the message to be decrypted.

• key: the private key corresponding to the public key that is used to encrypt data. If the private key is password-protected, specify the password in psw. If no password exists but you want to specify options, specify an empty password.

You cannot use the pgp_pub_decrypt function to decrypt BYTEA data. This avoids outputting invalid character data. You can use the pgp_pub_decrypt_bytea function to decrypt the original textual data.

Random-data functions

gen_random_bytes()

gen_random_bytes(count integer) returns bytea 

The gen_random_bytes() function is used to return cryptographically strong random bytes.

count: the number of returned bytes. Valid values: 1 to 1024.

Example:

SELECT gen_random_bytes(16);

Sample result:

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

gen_random_uuid()

The gen_random_uuid() function is used to return a version 4 (random) UUID.

Example:

SELECT gen_random_uuid();

Sample result:

gen_random_uuid 
--------------------------------------
2bd664a2-b760-4859-8af6-8d09ccc5b830

SM4 encryption algorithm

Information security requires that sensitive information stored in databases is encrypted, such as the logon password, credit card number, and identity card number. The pgcrypto extension provides various one-way and two-way encryption algorithms.

• One-way encryption algorithm: Plaintext data cannot be decrypted from ciphertext data. This non-invertible encryption algorithm is suitable for data verification, such as logon password verification.

• Two-way encryption algorithm: Plaintext data can be decrypted from ciphertext data based on keys. This invertible encryption algorithm is suitable for securing data transmission in scenarios such as electronic payment and digital signature.

AnalyticDB for PostgreSQL supports the SM4 encryption algorithm. SM4 is a two-way encryption algorithm. If you use this algorithm, you must provide keys to encrypt data on the database server.

Installation

Before you use the SM4 encryption algorithm on an AnalyticDB for PostgreSQL instance, install the pgcryptoto extension on the Extensions page of the instance. For more information, see Install, update, and uninstall extensions.

Encryption

The SM4 function uses the following syntax to encrypt key fields:

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

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

The following table describes the parameters.

• Example 1: Encrypt the text field who am i with the encryption key key.

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

  The value of the encrypted field is in the BYTEA format. Sample result:

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

• Example 2: Use the SM4 function to convert data of the t_plain table to ciphertext data and insert the data into the t_enc table.

  -- DROP EXTENSION encdb;
  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;

  The value of the introduction field is encrypted. Sample result:

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

Decryption

The SM4 function uses the following syntax to decrypt the encrypted fields:

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

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

The following table describes the parameters.

• Example 1: Decrypt the preceding encrypted field.

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

  The encrypted field is decrypted to who am i. Sample result:

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

• Example 2: Use the SM4 function to decrypt and query the t_enc table.

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

  The value of the introduction field is decrypted. Sample result:

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

References

For more information about pgcrypto, see postgresql.