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:
0
Answers
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.14You 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.
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.