Automatic connection using Remote authentication into Vertica.

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?

Best Answer

Answers

  • Bryan_HBryan_H Employee

    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.

  • SankarmnSankarmn ✭✭

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

  • Bryan_HBryan_H Employee

    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.

  • SankarmnSankarmn ✭✭

    @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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.