Setting Session Authorization to Troubleshoot

There are possible scenarios in which a dbadmin would want to run queries as another user to troubleshoot or test. You can use SET SESSION AUTHORIZATION to impersonate another user and run queries. Let’s understand this with an example.

Here we create a user named test, resource pool named userpool, and make this a default resource pool for that user.

verticadb=> create user test;
CREATE USER
verticadb=> create resource pool userpool;
CREATE RESOURCE POOL
verticadb=> grant usage on resource pool userpool to test;
GRANT PRIVILEGE
verticadb=> alter user test resource pool userpool;
ALTER USER

Let’s run a sample query and as you can see the query is run as the dbadmin user and we used the general pool.

verticadb=> profile select * from t1;
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273776800 and statement_id=1;
NOTICE 3557:  Initiator memory for query: [on pool general: 7785 KB, minimum: 7785 KB]
NOTICE 5077:  Total memory required by query: [7785 KB]
 a | b |       c
---+---+---------------
 1 | 2 | un, deux
 3 | 4 | trois, quatre
(2 rows)
verticadb=> select * from query_consumption where transaction_id=45035996273776800 and statement_id=1;
-[ RECORD 1 ]----------+----------------------------------
start_time             | 2023-09-27 19:06:08.374505-04
end_time               | 2023-09-27 19:06:08.479985-04
session_id             | v_verticadb_node0001-2473455:0x134
user_id                | 45035996273704962
user_name              | dbadmin
transaction_id         | 45035996273776800
statement_id           | 1
cpu_cycles_us          | 72424
network_bytes_received | 0
network_bytes_sent     | 0
data_bytes_read        | 55
data_bytes_written     | 0
data_bytes_loaded      | 0
bytes_spilled          | 0
input_rows             | 2
input_rows_processed   | 2
peak_memory_kb         | 7785
thread_count           | 6
duration_ms            | 247
resource_pool          | general
output_rows            | 2
request_type           | QUERY
label                  |
is_retry               | f
success                | t

Now, let’s set the session authorization so that the dbadmin user can run the query as a test user.

verticadb=> SELECT CURRENT_USER(), SESSION_USER();
 current_user | session_user
--------------+--------------
 dbadmin      | dbadmin
(1 row)

verticadb=> SET SESSION AUTHORIZATION test;
SET
verticadb=> SELECT CURRENT_USER(), SESSION_USER();
 current_user | session_user
--------------+--------------
 test         | test
(1 row) 

Now let’s run the sample query.

verticadb=> profile select * from t1;
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273776803 and statement_id=1;
NOTICE 3557:  Initiator memory for query: [on pool userpool: 7785 KB, minimum: 7785 KB]
NOTICE 5077:  Total memory required by query: [7785 KB]
 a | b |       c
---+---+---------------
 1 | 2 | un, deux
 3 | 4 | trois, quatre
(2 rows)

verticadb=> select * from query_consumption where transaction_id=45035996273776803  and statement_id=1;
-[ RECORD 1 ]----------+----------------------------------
start_time             | 2023-09-27 19:07:24.167322-04
end_time               | 2023-09-27 19:07:24.174564-04
session_id             | v_verticadb_node0001-2473455:0x134
user_id                | 45035996273861560
user_name              | test
transaction_id         | 45035996273776803
statement_id           | 1
cpu_cycles_us          | 2288
network_bytes_received | 0
network_bytes_sent     | 0
data_bytes_read        | 0
data_bytes_written     | 0
data_bytes_loaded      | 0
bytes_spilled          | 0
input_rows             | 2
input_rows_processed   | 2
peak_memory_kb         | 7785
thread_count           | 6
duration_ms            | 23
resource_pool          | userpool
output_rows            | 2
request_type           | QUERY
label                  |
is_retry               | f
success                | t 

You can see here, the query was run as a test user because of the session authorization feature, and it used the same resource pool as the test user.

Let’s run another query against another table.

eonv1111=> profile select * from inventory;
ERROR 4367:  Permission denied for relation inventory

This query failed as the test user does not have the necessary grants to access the table. It honors and checks for access policies, grants, and so on, before running the query. For more information, see SET SESSION AUTHORIZATION in the Vertica documentation.