HomeMogDBMogDB StackUqbar
v3.1

Documentation:v3.1

Supported Versions:

Other Versions:

Security Functions

  • gs_encrypt_aes128(encryptstr,keystr)

    Description: Encrypts encryptstr strings using keystr as the key and returns encrypted strings. The value of keystr ranges from 8 to 16 bytes and contains at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters.

    Return type: text

    Length of the return value: At least 92 bytes and no more than (4 * [Len/3] + 68 ) bytes, where Len indicates the length of the data before encryption (unit: byte).

    Example:

    MogDB=# SELECT gs_encrypt_aes128('MPPDB','Asdf1234');
    
                                   gs_encrypt_aes128
    -------------------------------------------------------------------------------------
    gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=
    (1 row)

    img NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

  • gs_encrypt(encryptstr,keystr, encrypttype)

    Description: Encrypts encryptstr strings using keystr as the key and returns encrypted strings based on encrypttype. The value of keystr contains 8 to 16 bytes and at least three types of the following characters: uppercase letters, lowercase letters, digits, and special characters. The value of encrypttype can be aes128 or sm4.

    Return type: text

    Example:

    MogDB=#  SELECT gs_encrypt('MPPDB','Asdf1234','sm4');
             gs_encrypt
     ------------------------------
     ZBzOmaGA4Bb+coyucJ0B8AkIShqc
    (1 row)

    img NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

  • gs_decrypt_aes128(decryptstr,keystr)

    Description: Decrypts decrypt strings using keystr as the key and returns decrypted strings. The keystr used for decryption must be consistent with that used for encryption. keystr cannot be empty.

    img NOTE: This parameter needs to be used with the gs_encrypt_aes128 encryption function.

    Return type: text

    Example:

    MogDB=# SELECT gs_decrypt_aes128('gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=','1234');
     gs_decrypt_aes128
    -------------------
     MPPDB
    (1 row)

    img NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

  • gs_decrypt(decryptstr, keystr, decrypttype)

    Description: Decrypts decrypt strings using keystr as the key and returns decrypted strings based on decrypttype. The decrypttype and keystr used for decryption must be consistent with those used for encryption. The value of keystr cannot be empty. The value of decrypttype can be aes128 or sm4.

    This function needs to be used with the gs_encrypt encryption function.

    Return type: text

    Example:

    MogDB=# select gs_decrypt('ZBzOmaGA4Bb+coyucJ0B8AkIShqc','Asdf1234','sm4');
     gs_decrypt
    ------------
     MPPDB
    (1 row)

    img NOTE: A decryption password is required during the execution of this function. For security purposes, the gsql tool does not record the function in the execution history. That is, the execution history of this function cannot be found in gsql by paging up and down.

  • aes_encrypt(str, key_str, init_vector)

    Description: Encrypts the string str using the key string key_str and initialization vector init_vector based on the AES algorithm.

    Parameter description:

    -- str: character string to be encrypted. If str is set to NULL, the function returns NULL.

    -- key_str: key character string. 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 for the 128-bit, 192-bit, or 256-bit key (determined by block_encryption_mode. For details, see Security Configuration.

    -- 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 value for each encryption is unique in OFB mode and that the IV value for each encryption is unpredictable in CBC and CFB modes.

    Return type: text

    Example:

    MogDB=# select aes_encrypt('huwei123','123456vfhex4dyu,vdaladhjsadad','1234567890123456');
     aes_encrypt
    -------------
     u*8\x05c?0
    (1 row)

    img NOTE:

    (1) This function is valid only when MogDB is compatible with the MY type (that is, sql_compatibility = 'B').

    (2) 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.

    (3) 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.

    (4) Do not call the function when debug_print_plan is enabled, 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 enabled, and then provide the information to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.

    (5) The SQL_ASCII setting is different 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 OpenSSL third-party library, the returned encoding of 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_decrypt(pass_str, key_str, init_vector)

    Description: Decrypts the string str using the key string key_str and initialization vector init_vector based on the AES algorithm.

    Parameter description:

    -- pass_str: character string to be decrypted. If pass_str is set to NULL, the function returns NULL.

    -- key_str: key character string. 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 for the 128-bit, 192-bit, or 256-bit key (determined by block_encryption_mode. For details, see Security Configuration.

    -- init_vector: An initialization variable is provided for the required block decryption mode. The length is greater than or equal to 16 bytes. Bytes greater than 16 bytes are automatically ignored. If neither pass_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 value for each encryption is unique in OFB mode and that the IV value for each encryption is unpredictable in CBC and CFB modes.

    Return type: text

    Example:

    MogDB=# select aes_decrypt(aes_encrypt('huwei123','123456vfhex4dyu,vdaladhjsadad','1234567890123456'),'123456vfhex4dyu,vdaladhjsadad','1234567890123456');
     aes_decrypt
    -------------
     huwei123
    (1 row)

    img NOTE:

    (1) This function is valid only when MogDB is compatible with the MY type (that is, sql_compatibility = 'B').

    (2) 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.

    (3) 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.

    (4) Do not call the function when debug_print_plan is enabled, 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 enabled, and then provide the information to external maintenance engineers for fault locating. After you finish using the logs, delete them as soon as possible.

    (5) To ensure successful decryption, ensure that the values of block_encryption_mode, key_str and iv are the same as those during encryption.

    (6) 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.

    (7) The SQL_ASCII setting is different 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 OpenSSL third-party library, the returned encoding of 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.

  • gs_password_deadline

    Description: Indicates the number of remaining days before the password of the current user expires.

    Return type: interval

    Example:

    MogDB=# SELECT gs_password_deadline();
      gs_password_deadline
    -------------------------
     83 days 17:44:32.196094
    (1 row)
  • gs_password_notifytime

    Description: Specifies the number of days prior to password expiration that a user will receive a reminder.

    Return type: int32

  • login_audit_messages

    Description: Queries login information about a login user.

    Return type: tuple

    Example:

    • Check the date, time, and IP address successfully authenticated during the last login.

      mogdb=> select * from login_audit_messages(true);
      username | database |       logintime        |    mytype     | result | client_conninfo
      ----------+----------+------------------------+---------------+--------+-----------------
      omm      | MogDB | 2020-06-29 21:56:40+08 | login_success | ok     | gsql@[local]
      (1 row)
    • Check the date, time, and IP address that failed to be authenticated during the last login.

      mogdb=>  select * from login_audit_messages(false) ORDER BY logintime desc limit 1;
      username | database |       logintime        |    mytype    | result |  client_conninfo
      ----------+----------+------------------------+--------------+--------+-------------------
      omm      | MogDB | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local]
      (1 row)
    • Check the number of failed attempts, date, and time since the previous successful authentication.

      mogdb=>  select * from login_audit_messages(false);
      username | database |       logintime        |    mytype    | result |  client_conninfo
      ----------+----------+------------------------+--------------+--------+-------------------
      omm      | MogDB | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local]
      omm      | MogDB | 2020-06-29 21:57:53+08 | login_failed | failed | [unknown]@[local]
      (2 rows)
  • login_audit_messages_pid

    Description: Queries login information about a login user. Different from login_audit_messages, this function queries login information based on backendid. Information about subsequent logins of the same user does not alter the query result of previous logins and cannot be found using this function.

    Return type: tuple

    Example:

    • Check the date, time, and IP address successfully authenticated during the last login.

      mogdb=> SELECT * FROM login_audit_messages_pid(true);
      username | database |       logintime        |    mytype     | result | client_conninfo |    backendid
      ----------+----------+------------------------+---------------+--------+-----------------+-----------------
      omm      | MogDB | 2020-06-29 21:56:40+08 | login_success | ok     | gsql@[local]    | 139823109633792
      (1 row)
    • Check the date, time, and IP address that failed to be authenticated during the last login.

      mogdb=> SELECT * FROM login_audit_messages_pid(false) ORDER BY logintime desc limit 1;
      username | database |       logintime        |    mytype    | result |  client_conninfo  |    backendid
      ----------+----------+------------------------+--------------+--------+-------------------+-----------------
      omm      | MogDB | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] | 139823109633792
      (1 row)
    • Check the number of failed attempts, date, and time since the previous successful authentication.

      mogdb=> SELECT * FROM login_audit_messages_pid(false);
      username | database |       logintime        |    mytype    | result |  client_conninfo  |    backendid
      ----------+----------+------------------------+--------------+--------+-------------------+-----------------
      omm      | MogDB | 2020-06-29 21:57:55+08 | login_failed | failed | [unknown]@[local] | 139823109633792
      omm      | MogDB | 2020-06-29 21:57:53+08 | login_failed | failed | [unknown]@[local] | 139823109633792
      (2 rows)
  • inet_server_addr

    Description: Displays the server IP address.

    Return type: inet

    Example:

    MogDB=# SELECT inet_server_addr();
     inet_server_addr
    ------------------
     10.10.0.13
    (1 row)

    img NOTE:

    • The client IP address 10.10.0.50 and server IP address 10.10.0.13 are used as an example.
    • If the database is connected to the local PC, the value is empty.
  • inet_client_addr

    Description: Displays the client IP address.

    Return type: inet

    Example:

    MogDB=# SELECT inet_client_addr();
     inet_client_addr
    ------------------
     10.10.0.50
    (1 row)

    img NOTE:

    • The client IP address 10.10.0.50 and server IP address 10.10.0.13 are used as an example.
    • If the database is connected to the local PC, the value is empty.
  • pg_query_audit

    Description: Views audit logs of the primary database node.

    Return type: record

    The following table describes return fields.

    Name Type Description
    time timestamp with time zone Operation time
    type text Operation
    result text Operation result
    userid oid User ID
    username text Name of the user who performs the operation
    database text Database name
    client_conninfo text Client connection information
    object_name text Object name
    detail_info text Operation details
    node_name text Node name
    thread_id text Thread ID
    local_port text Local port
    remote_port text Remote port

    For details about how to use the function and details about function examples, see Querying Audit Results.

  • pg_delete_audit

    Description: Deletes audit logs in a specified period.

    Return value type: void

    For details about how to use the function and details about function examples, see Maintaining Audit Logs.

Copyright © 2011-2024 www.enmotech.com All rights reserved.