CONNECT TO VERTICA does not have client label

Can you file new feature request:
CONNECT TO VERTICA should allow to set client label on connection.
For audit purposes.
As of now, client label is always empty, client type is vertica connect.

Tagged:

Answers

  • moshegmosheg Vertica Employee Administrator
    edited June 2023

    You can assign a client label to the connection.
    Client connections and their labels appear in the SESSIONS and some Data collector tables like DC_REQUESTS_ISSUED.
    To set client labels for ongoing sessions, use SET_CLIENT_LABEL like so:
    SELECT SET_CLIENT_LABEL('my_label_xyz');

    Please consider the following example:

    vsql -g LABEL_X -ef test.sql
    
    SELECT GET_CLIENT_LABEL();
     GET_CLIENT_LABEL
    ------------------
     LABEL_X
    (1 row)
    
    SELECT CLOCK_TIMESTAMP(), 1+2+3+4 as Ten FROM DUAL;
            CLOCK_TIMESTAMP        | Ten
    -------------------------------+-----
     2023-06-03 16:21:10.252019+03 |  10
    (1 row)
    
    \x
    Expanded display is on.
    SELECT * FROM v_monitor.sessions WHERE client_label = 'LABEL_X';
    -[ RECORD 1 ]--------------+-------------------------------------------------------------------
    node_name                  | v_eondb_node0001
    user_name                  | dbadmin
    client_hostname            | [::1]:44032
    client_pid                 | 2725233
    login_timestamp            | 2023-06-03 16:21:10.243231+03
    session_id                 | v_eondb_node0001-359523:0x22e67e
    idle_session_timeout       |
    grace_period               |
    client_label               | LABEL_X
    transaction_start          | 2023-06-03 16:21:10.244526+03
    transaction_id             | 45035996273756784
    transaction_description    | user dbadmin (SELECT CLOCK_TIMESTAMP(), 1+2+3+4 as Ten FROM DUAL;)
    statement_start            | 2023-06-03 16:21:10.252859+03
    statement_id               | 2
    last_statement_duration_us | 7982
    runtime_priority           | LOW
    current_statement          | SELECT * FROM v_monitor.sessions WHERE client_label = 'LABEL_X';
    last_statement             | SELECT CLOCK_TIMESTAMP(), 1+2+3+4 as Ten FROM DUAL;
    ssl_state                  | None
    tls_version                |
    authentication_method      | ImpTrust
    client_type                | vsql
    client_version             | 12.00.0406
    client_os                  | Linux 4.18.0-425.19.2.el8_7.x86_64 x86_64
    client_os_user_name        | dbadmin
    client_os_hostname         | my_client_os_hostname
    client_authentication_name | default: Implicit Trust
    client_authentication      | 0
    requested_protocol         | 3.14
    effective_protocol         | 3.14
    external_memory_kb         | 0
    temp_table_impaired        | f
    
    SELECT * FROM DC_REQUESTS_ISSUED WHERE client_label = 'LABEL_X';
    -[ RECORD 1 ]-----+-----------------------------------------------------------------
    time              | 2023-06-03 16:21:10.243762+03
    node_name         | v_eondb_node0001
    session_id        | v_eondb_node0001-359523:0x22e67e
    user_id           | 45035996273704962
    user_name         | dbadmin
    transaction_id    | 0
    statement_id      | -1
    request_id        | 1
    request_type      | UTILITY
    label             |
    client_label      | LABEL_X
    search_path       | "$user", public, v_catalog, v_monitor, v_internal, v_func
    query_start_epoch | 44
    request           | SELECT GET_CLIENT_LABEL();
    is_retry          | f
    digest            |
    -[ RECORD 2 ]-----+-----------------------------------------------------------------
    time              | 2023-06-03 16:21:10.245076+03
    node_name         | v_eondb_node0001
    session_id        | v_eondb_node0001-359523:0x22e67e
    user_id           | 45035996273704962
    user_name         | dbadmin
    transaction_id    | 45035996273756784
    statement_id      | 1
    request_id        | 2
    request_type      | QUERY
    label             |
    client_label      | LABEL_X
    search_path       | "$user", public, v_catalog, v_monitor, v_internal, v_func
    query_start_epoch | 44
    request           | SELECT CLOCK_TIMESTAMP(), 1+2+3+4 as Ten FROM DUAL;
    is_retry          | f
    digest            | -7148748622092464661
    -[ RECORD 3 ]-----+-----------------------------------------------------------------
    time              | 2023-06-03 16:21:10.253492+03
    node_name         | v_eondb_node0001
    session_id        | v_eondb_node0001-359523:0x22e67e
    user_id           | 45035996273704962
    user_name         | dbadmin
    transaction_id    | 45035996273756784
    statement_id      | 2
    request_id        | 3
    request_type      | QUERY
    label             |
    client_label      | LABEL_X
    search_path       | "$user", public, v_catalog, v_monitor, v_internal, v_func
    query_start_epoch | 44
    request           | SELECT * FROM v_monitor.sessions WHERE client_label = 'LABEL_X';
    is_retry          | f
    
    SELECT * FROM user_sessions WHERE client_label = 'LABEL_X';
    -[ RECORD 1 ]-----------+------------------------------------------
    node_name               | v_eondb_node0001
    user_name               | dbadmin
    session_id              | v_eondb_node0001-359523:0x22e67e
    transaction_id          | 45035996273756784
    statement_id            | 4
    runtime_priority        | HIGH
    session_start_timestamp | 2023-06-03 16:21:10.243242+03
    session_end_timestamp   |
    is_active               | t
    client_hostname         | [::1]:44032
    client_pid              | 2725233
    client_label            | LABEL_X
    ssl_state               | None
    tls_version             |
    authentication_method   | ImpTrust
    client_type             | vsql
    client_version          | 12.00.0406
    client_os               | Linux 4.18.0-425.19.2.el8_7.x86_64 x86_64
    client_os_user_name     | dbadmin
    client_os_hostname      | my_client_os_hostname
    requested_protocol      | 3.14
    effective_protocol      | 3.14
    
  • You are missing point just little bit.
    Goal is to have client label for connection on "remote" Vertica cluster. You were talking about setting client on connection for "source" connection side of CONNECT TO VERTICA.
    If you will check sessions on "remote" Vertica, connections from CONNECT TO VERTICA will always have no label and dbadmin for CLIENT_OS_USER_NAME. To find connection info, you have to look in "source" vertica logs on timestamp.
    Would be nice if user can specify label on connection CONNECT TO VERTICA.

    Always setting CLIENT_OS_USER_NAME to dbadmin make connection from CONNECT TO VERTICA anonymous.
    Would be nice to set it to something meaningful, for example user_name of source connection, or CLIENT_OS_USER_NAME on source connection.

    Purpose of changes - improve Vertica audit.

    Previous user comment is definitely from ChatGPT. Common practice is to prohibit ChatGPT-generated content on user forums.
    Though, if user is not proficient in English, ChatGPT is definitely a big help.

  • moshegmosheg Vertica Employee Administrator

    Thank you Sergey,
    I've tried the following from my PC to a remote cluster and the client_label is shown in DC_REQUESTS_ISSUED as defined (MYLABEL),
    The user_name and client_os_user_name is shown as my local PC Linux user name
    which is also defined as a user in the remote database (not dbadmin).
    vsql -h 10.10.10.40 -d EONDB -g MYLABEL -ef myfile.sql

  • I do not see it
    There are two Vertica clusters involved. Let's call then dev and uat. Both cluster s are 12.0.4.
    I am connecting to dev, and issuing CONNECT to uat:

    /opt/vertica/bin/vsql -g mylabel -h dev_cluster -U dbadmin -w vertica
    connect to vertica uat user uat_user password 'uat_pwd' on 'uat_cluster',5433;

    Now checking session on uat:

    select client_label, client_os_user_name, client_os_hostname from sessions where client_type='vertica connect';

    It returns 1 row with all fields empty.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file