AnalyticDB for PostgreSQL provides a built-in pgcryoto extension for encryption and decryption and integrates the SM4 encryption algorithm into the pgcryoto extension. The pgcrypto extension allows you to store encrypted columns, which further protects sensitive data. This way, encrypted data in databases cannot be obtained without keys.
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.
Create the pgcrypto extension
You cannot manually create the pgcrypto extension. If you want to use the extension, Submit a ticket.
CREATE EXTENSION pgcrypto;
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.
Examples
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 salted hashes for the crypt() function.
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 thecrypt()
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 reversing 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
Algorithm | Maximum password length | Adaptive | Salt bits | Output length | Description |
BF | 72 | Yes | 128 | 60 | A variant of the Blowfish algorithm, which is called Blowfish-2a. |
MD5 | unlimited | No | 48 | 34 | The MD5-based encryption algorithm. |
XDES | 8 | Yes | 24 | 20 | The extended Data Encryption Standard (DES) algorithm. |
DES | 8 | No | 12 | 13 | The native UNIX encryption algorithm. |
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 specified 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 decrypt 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 inpsw
. 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.
The SM4 encryption algorithm supports only the TEXT and VARCHAR data types. Developers must convert the data to be encrypted to strings before encryption.
The SM4 encryption algorithm is supported only for AnalyticDB for PostgreSQL V6.3.8.9 and later. For information about how to view the minor version of an instance, see View the minor engine version.
Installation
The SM4 encryption algorithm requires the pgcrypto extension. You cannot manually create the pgcrypto extension. If you want to use the extension, Submit a ticket.
CREATE EXTENSION pgcrypto;
Encryption
The SM4 function uses the following syntax to encrypt critical 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.
Parameter | Description |
| The TEXT data to be encrypted. |
| The VARCHAR data to be encrypted. |
| The encryption key, in the TEXT format. |
Example 1: Encrypt the text field
who am i
. In this example,key
is used as the encryption 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 thet_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.
Parameter | Description |
| The BYTEA data to be decrypted. |
| The encryption key, in the TEXT format. |
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.