Help Center > > Developer Guide> SQL Reference> Functions and Operators> Security Functions

Security Functions

Updated at: Jul 15, 2020 GMT+08:00

Security Functions

  • gs_encrypt_aes128(encryptstr,keystr)

    Description: Encrypts encryptstr strings using keystr as the key and returns encrypted strings. The length of keystr ranges from 1 to 16 bytes. Currently, the following types of data can be encrypted: numerals supported in the database; character type; RAW in binary type; and DATE, TIMESTAMP, and SMALLDATETIME in date/time type.

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

    For example:

    1
    2
    3
    4
    5
    6
    SELECT gs_encrypt_aes128('MPPDB','1234');
    
                                   gs_encrypt_aes128
    -------------------------------------------------------------------------------------
    gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=
    (1 row)
    

    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.

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

    Return type: text

    For example:

    1
    2
    3
    4
    5
    SELECT gs_decrypt_aes128('gwditQLQG8NhFw4OuoKhhQJoXojhFlYkjeG0aYdSCtLCnIUgkNwvYI04KbuhmcGZp8jWizBdR1vU9CspjuzI0lbz12A=','1234');
     gs_decrypt_aes128 
    -------------------
     MPPDB
    (1 row)
    

    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_password_deadline

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

    Return type: interval

    For example:

    1
    2
    3
    4
    5
    SELECT gs_password_deadline();
      gs_password_deadline   
    -------------------------
     83 days 17:44:32.196094
    (1 row)
    
  • login_audit_messages

    Description: Queries login information about a login user.

    Return type: tuple

    For example:

    • Checks the date, time, and IP address successfully authenticated during the last login.
      1
      2
      3
      4
      5
      SELECT * FROM login_audit_messages(true);
        username  | database |       logintime        |     type      | result |  client_conninfo   
      ------------+----------+------------------------+---------------+--------+--------------------
           dbadmin    | postgres | 2017-06-02 15:28:34+08 | login_success | ok     | gsql@[local]
      (1 row)
      
    • Checks the date, time, and IP address that failed to be authenticated during the last login.
      1
      2
      3
      4
      SELECT * FROM login_audit_messages(false) ORDER BY logintime desc limit 1;
        username  | database |       logintime        |     type     | result |     client_conninfo     
      ------------+----------+------------------------+--------------+--------+-------------------------
      (0 rows)
      
    • Checks the number of failed attempts, date, and time since the previous successful authentication.
      1
      2
      3
      4
      SELECT * FROM login_audit_messages(false);
        username  | database |       logintime        |     type     | result |     client_conninfo     
      ------------+----------+------------------------+--------------+--------+-------------------------
      (0 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

    For example:

    • Checks the date, time, and IP address successfully authenticated during the last login.
      1
      2
      3
      4
      5
      SELECT * FROM login_audit_messages(true);
        username  | database |       logintime        |     type      | result |  client_conninfo | backendid
      ------------+----------+------------------------+---------------+--------+--------------------
           dbadmin    | postgres | 2017-06-02 15:28:34+08 | login_success | ok     | gsql@[local] | 140311900702464
      (1 row)
      
    • Checks the date, time, and IP address that failed to be authenticated during the last login.
      1
      2
      3
      4
      SELECT * FROM login_audit_messages(false) ORDER BY logintime desc limit 1;
        username  | database |       logintime        |     type     | result |     client_conninfo   | backendid
      ------------+----------+------------------------+--------------+--------+-------------------------
      (0 rows)
      
    • Checks the number of failed attempts, date, and time since the previous successful authentication.
      1
      2
      3
      4
      SELECT * FROM login_audit_messages(false);
        username  | database |       logintime        |     type     | result |     client_conninfo    | backendid
      ------------+----------+------------------------+--------------+--------+-------------------------
      (0 rows)
      
  • inet_server_addr

    Description: Displays the server IP address.

    Return type: inet

    For example:

    1
    2
    3
    4
    5
    SELECT inet_server_addr();
     inet_server_addr
    ------------------
     10.10.0.13
    (1 row)
    
    • 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

    For example:

    1
    2
    3
    4
    5
    SELECT inet_client_addr();
     inet_client_addr
    ------------------
     10.10.0.50
    (1 row)
    
    • 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: Displays audit logs of the CN.

    Return type: record

    The following table describes return fields.

    Name

    Type

    Description

    time

    timestamp with time zone

    Operation time

    type

    text

    Operation type

    result

    text

    Operation results

    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 Viewing Audit Information.

  • pgxc_query_audit

    Description: Displays audit logs of all CNs.

    Return type: record

    The return fields of this function are the same as those of the pg_query_audit function.

    For details about how to use the function and details about function examples, see Viewing Audit Information.

  • pg_delete_audit

    Description: Deletes audit logs in a specified period.
    Return type: void

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel