Automatic connection using Remote authentication into Vertica.

Sankarmn

We want few users/jobs to connect to Vertica to run SQL's and collect data using remote authentication. User would authenticate into server as os user and then automatically connect to the database using vsql to run SQL's. How can we achieve this?

SergeB

Just to confirm, your scenario would be that users shh / login to a Vertica node and then run vsql ? In that case, you could use a LOCAL TRUST authentication or an IDENT authentication if you want to limit access to only certain OS users.
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/Security/ClientAuth/ConfiguringIdentAuthentication.htm

Bryan_H

For shell-based login and scripts, one option is to set environment variables: https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConnectingToVertica/vsql/vsqlEnvironmentVariables.htm
E.g. set VSQL_USER, VSQL_PASSWORD, VSQL_HOST and the user or script can run "vsql" and log in automatically using the user, password, host in the environment.

Sankarmn

Setting the VSQL_PASSWORD as env. variable for scheduler job user is a concern as its exposed to other users as well.

Bryan_H

It's also possible to set password on the command line with the -w switch, though this is also accessible to any user who can read the script.
Is it possible to isolate the scheduler user and home directory, setting ownership to specific user/group with mode 600 on files and 700 on folders, similar to requirement for SSH keys in the .ssh folder?
I'm not sure there is a mechanism that would not require a secret to be stored for automatic login. For example, Kerberos and TLS client certificate still require a file (ticket or private key) to be stored in a location accessible to the user.

Sankarmn

@Bryan_H said:
It's also possible to set password on the command line with the -w switch, though this is also accessible to any user who can read the script.
Is it possible to isolate the scheduler user and home directory, setting ownership to specific user/group with mode 600 on files and 700 on folders, similar to requirement for SSH keys in the .ssh folder?
I'm not sure there is a mechanism that would not require a secret to be stored for automatic login. For example, Kerberos and TLS client certificate still require a file (ticket or private key) to be stored in a location accessible to the user.

Setting the file permissions was the option we had with securing passwords, until unless we have other authentication methods.