Query Log (Sessions) - How to skip confidential queries from Log

ersaurabhex

Hi,

Two scenario -
1. We don't want few queries (fired from a specific user or some other query text based rule) to be shown on sessions (select * from sessions).
2. Some of our users would like to use Encryption by a password by while loading the data in database (Concept from - https://github.com/vertica/Vertica-Extension-Packages/tree/master/encryption_package), but these passphrases are visible in Sessions and also in vertica.log
We would like to know something similar to IDENTIFIED BY ****** clause, where passphrases are replaced by * in all logs (vertica.log and sessions) etc.

Regards,
Saurabh

Ben_Vandiver

For UDx, I would consider using session udparameters to pass sensitive data. We use this mechanism for the s3source() load function used prior to 9.0 to read from S3. From the documentation:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/Eon/LoadingDataFromS3.htm?Highlight=s3
Alternatively, define AWS credentials using ALTER SESSION:

=> ALTER SESSION SET UDPARAMETER FOR awslib aws_id='EXAMPLE';
ALTER SESSION
=> ALTER SESSION SET UDPARAMETER FOR awslib aws_secret='EXAMPLE/EXAMPLEKEY';
ALTER SESSION

Any udparameter that ends with '_secret' is not logged in the vertica log.
You can read udparameters using:
https://my.vertica.com/docs/9.0.x/HTML/CppSDK/class_vertica_1_1_session_param_reader_map.htm

We should probably update the encryption extension-package to make use of UDparameters - if you do so, consider contributing back?

This only addresses #2 above

ersaurabhex

Thanks Ben.. your provided solution worked!! Really appreciate your help.