Encryption and Decryption Functions

AES_DECRYPT

AES_DECRYPT(crypt_str, key_str[, init_vector][, kdf_name][, salt][, info | iterations])

Description: Based on AES algorithm, uses the decryption password key_str, initialization vector init_vector, and KDF-related options (kdf_name, salt, and info/iterations) to decrypt the encrypted string crypt_str.

Parameters:

crypt_str: string to be decrypted. If crypt_str is set to NULL, the function returns NULL.
key_str: decryption password. If key_str is set to NULL, the function returns NULL. For security purposes, you are advised to use a 128-bit, 192-bit, or 256-bit secure random number as the key string if the key length is 128 bits, 192 bits, or 256 bits (determined by the value of block_encryption_mode).
init_vector: An initialization variable is provided for the required block encryption mode. The length is greater than or equal to 16 bytes. Bytes greater than 16 bytes are automatically ignored. If neither str nor key_str is NULL, this parameter cannot be NULL. Otherwise, an error is reported. For security purposes, you are advised to ensure that the IV for each encryption is unique in OFB mode and that the IV for each encryption is unpredictable in CBC or CFB mode.
kdf_name: specifies the KDF algorithm to be used. The value can be PBKDF2 or HKDF.
salt: used together with the original password to generate a key.
info/iterations: For HKDF, INFO is the parameter specified by this field. For PBKDF2, ITERATIONS is the number of iterations specified by this field.

Return value type: LONGBLOB

Examples:

m_db=# SELECT AES_DECRYPT(AES_ENCRYPT('huwei123','123456vfhex4dyu','vdaladhjsadadabcdef','hkdf'),'123456vfhex4dyu','vdaladhjsadadabcdef','hkdf');
 aes_decrypt 
-------------
 huwei123
(1 row)

A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
Do not call this function during operations related to stored procedures, preventing the risk of sensitive information disclosure. In addition, when using the stored procedure that contains the function, you are advised to filter the parameter information of the function before providing the information for external maintenance personnel to locate the fault. Delete the logs after using them.
Do not call the function when debug_print_plan is set to on, preventing the risk of sensitive information disclosure. You are advised to filter parameter information of the function in the log files generated when debug_print_plan is set to on before providing the log files to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.
To ensure successful decryption, ensure that the values of block_encryption_mode, key_str and iv are the same as those during encryption.
Due to encoding differences, encrypted data cannot be directly copied from the gsql client for decryption. In this scenario, the decryption result may not be the character string before encryption.
The SQL_ASCII setting performs quite differently from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If this parameter is set to SQL_ASCII, no code conversion occurs. When this function calls the third-party OpenSSL library, the returned data is non-ASCII data. Therefore, when the character set of the database server is set to SQL_ASCII, the encoding of the client must also be set to SQL_ASCII. Otherwise, an error is reported. The database does not convert or verify non-ASCII characters.

AES_ENCRYPT

AES_ENCRYPT(str, key_str[, init_vector][, kdf_name][, salt][, info | iterations])

Description: Based on AES algorithm, the string str is encrypted using the encryption password key_str, initialization vector init_vector, and KDF-related options (kdf_name, salt, and info/iterations).

Parameters:

str: string to be encrypted. If str is set to NULL, the function returns NULL.
key_str: encryption password. If key_str is set to NULL, the function returns NULL. For security purposes, you are advised to use a 128-bit, 192-bit, or 256-bit secure random number as the key string if the key length is 128 bits, 192 bits, or 256 bits (determined by the value of block_encryption_mode).
init_vector: An initialization variable is provided for the required block encryption mode. The length is greater than or equal to 16 bytes. Bytes greater than 16 bytes are automatically ignored. If neither str nor key_str is NULL, this parameter cannot be NULL. Otherwise, an error is reported. For security purposes, you are advised to ensure that the IV for each encryption is unique in OFB mode and that the IV for each encryption is unpredictable in CBC or CFB mode.
kdf_name: specifies the KDF algorithm to be used. The value can be PBKDF2 or HKDF.
salt: used together with the original password to generate a key.
info/iterations: For HKDF, INFO is the parameter specified by this field. For PBKDF2, ITERATIONS is the number of iterations specified by this field.

Return value type: LONGBLOB.

Examples:

m_db=# SELECT AES_ENCRYPT('huwei123','123456vfhex4dyu,vdaladhjsadad','1234567890123456');
    aes_encrypt    
-------------------
 Z_Z\x7F\\x7Fy\x1D
(1 row)

An encryption password is required during the execution of this function. For security purposes, the gsql tool does not record the SQL statements containing the function name in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.
Do not call this function during operations related to stored procedures, preventing the risk of sensitive information disclosure. In addition, when using the stored procedure that contains the function, you are advised to filter the parameter information of the function before providing the information for external maintenance personnel to locate the fault. Delete the logs after using them.
Do not call the function when debug_print_plan is set to on, preventing the risk of sensitive information disclosure. You are advised to filter parameter information of the function in the log files generated when debug_print_plan is set to on before providing the log files to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.
The SQL_ASCII setting performs quite differently from other settings. If the character set of the server is SQL_ASCII, the server interprets the byte values 0 to 127 according to the ASCII standard. The byte values 128 to 255 are regarded as the characters that cannot be parsed. If this parameter is set to SQL_ASCII, no code conversion occurs. When this function calls the third-party OpenSSL library, the returned data is non-ASCII data. Therefore, when the character set of the database server is set to SQL_ASCII, the encoding of the client must also be set to SQL_ASCII. Otherwise, an error is reported. The database does not convert or verify non-ASCII characters.

PASSWORD

PASSWORD(TEXT str_input)

Description: Encrypts the input string twice using SHA-1.

Return value type: TEXT

Examples:

m_db=# SELECT PASSWORD('Gauss');
                 password                  
-------------------------------------------
 *DAC4E366F59D0427EFB4CCE84C7EA705EEA2FCC6
(1 row)

The SHA-1 encryption algorithm used by the PASSWORD function is insecure and may pose security risks. Therefore, you are advised not to use the SHA-1 encryption algorithm.

Parent topic: Functions and Operators

Previous topic: Window Functions

Next topic: Process Control Functions