dblinks exposing passwords when used in with clause queries

[ajilani1]

While using dblinks (one or more) in a with clause statement along with dblink passwords, the passwords are getting exposed,

`Utopia=> \x
Expanded display is on.
Utopia=> select * from query_requests where session_id='v_utopia_node0001-439950:0x6bb3c4';
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
node_name | v_utopia_node0001
user_name | dbadmin
session_id | v_utopia_node0001-439950:0x6bb3c4
request_id | 1
transaction_id | 45035996619740592
statement_id | 1
request_type | QUERY
request | SELECT DBLINK(USING PARAMETERS connect_secret=********, query='select node_name, node_address from nodes;') OVER();
request_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
memory_acquired_mb | 100
success | t
error_count |
start_timestamp | 2024-03-27 21:25:46.578761+00
end_timestamp | 2024-03-27 21:25:47.969328+00
request_duration | 00:00:01.390567
request_duration_ms | 1391
is_executing | f
-[ RECORD 2 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
node_name | v_utopia_node0001
user_name | dbadmin
session_id | v_utopia_node0001-439950:0x6bb3c4
request_id | 2
transaction_id | 45035996619740592
statement_id | 2
request_type | QUERY
request | select current_session();
request_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
memory_acquired_mb | 100
success | t
error_count |
start_timestamp | 2024-03-27 21:26:09.354729+00
end_timestamp | 2024-03-27 21:26:09.358541+00
request_duration | 00:00:00.003812
request_duration_ms | 4
is_executing | f
-[ RECORD 3 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
node_name | v_utopia_node0001
user_name | dbadmin
session_id | v_utopia_node0001-439950:0x6bb3c4
request_id | 3
transaction_id | 45035996619740592
statement_id | 3
request_type | QUERY
request | select * from query_requests where session_id='v_utopia_node0001-439950:0x6bb3c4';
request_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
memory_acquired_mb | 274.54
success | t
error_count |
start_timestamp | 2024-03-27 21:26:33.896005+00
end_timestamp | 2024-03-27 21:26:37.494123+00
request_duration | 00:00:03.598118
request_duration_ms | 3598
is_executing | f
-[ RECORD 4 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
node_name | v_utopia_node0001
user_name | dbadmin
session_id | v_utopia_node0001-439950:0x6bb3c4
request_id | 4
transaction_id | 45035996619740592
statement_id | 4
request_type | QUERY
request | select * from query_requests where session_id='v_utopia_node0001-439950:0x6bb3c4';
request_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
memory_acquired_mb | 274.54
success | t
error_count |
start_timestamp | 2024-03-27 21:26:43.090544+00
end_timestamp | 2024-03-27 21:26:46.447629+00
request_duration | 00:00:03.357085
request_duration_ms | 3357
is_executing | f
-[ RECORD 5 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
node_name | v_utopia_node0001
user_name | dbadmin
session_id | v_utopia_node0001-439950:0x6bb3c4
request_id | 5
transaction_id | 45035996619740637
statement_id | 1
request_type | DDL
request | WITH /+ENABLE_WITH_CLAUSE_MATERIALIZATION/ g as (SELECT DBLINK(USING PARAMETERS connect_secret='UID=user1;PWD=aaaaaabbbbbaaaaaabbbb@222333;DSN=Global_Utopia', query='select node_name, node_address from nodes;') OVER()),sc as (SELECT
DBLINK(USING PARAMETERS connect_secret='UID=user1;PWD=aaaaaabbbbbaaaaaabbbb@222333;DSN=SC_Utopia', query='select node_name, node_address from nodes;') OVER()) select g.node_name,g.node_address,sc.node_address from g left join sc on g.node_name=sc.node_nam
e;
request_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
memory_acquired_mb | 100
success | f
error_count |
start_timestamp | 2024-03-27 21:27:34.379119+00
end_timestamp | 2024-03-27 21:27:38.255125+00
request_duration | 00:00:03.876006
request_duration_ms | 3876
is_executing | f
-[ RECORD 6 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--
node_name | v_utopia_node0001
user_name | dbadmin
session_id | v_utopia_node0001-439950:0x6bb3c4
request_id | 6
transaction_id | 45035996619740638
statement_id | 1
request_type | QUERY
request | INSERT INTO v_temp_schema."g_$35$" SELECT public.dblink( USING PARAMETERS connect_secret='UID=user1;PWD=aaaaaabbbbbaaaaaabbbb@222333;DSN=Global_Utopia'::varchar(1024),query='select node_name, node_address from nodes;'::varchar(65000))
OVER () AS ( node_name, node_address ) FROM v_catalog.dual
request_label |
search_path | "$user", public, v_catalog, v_monitor, v_internal, v_func
memory_acquired_mb | 232.44
success | f
error_count | 2
start_timestamp | 2024-03-27 21:27:36.802727+00
end_timestamp | 2024-03-27 21:27:38.245605+00
request_duration | 00:00:01.442878
request_duration_ms | 1443
is_executing | f`

[ajilani1]

As of now the customer has to use with clause expression as it allows them to use two or more dblinks together and join them (via enable_with_clause_materialization).
Or can we introduce session level UDPARAMETER which will allow us to use multiple dblink connection passwords in the same session