Can VSQL be used by other than dbadmin users?
I have created a few Vertica users with access to database. How can these users use vsql if the first prompt is for password to database? Database password is dbadmin user password, so in essence these users with be logging with dbadmin access and not their own access.
How can users, other than dbadmin, use vsql to work with database?
Best Answer
-
DaveT
Vertica Employee Employee
One theory is that you have an authentication method that is preventing this. For example, it is possible if you tried vsql locally that an authentication rule prevented it whereas DBVis worked because it was not a local attempt. Here is an example where user "doug" is rejected locally but not when I provide a host. This is due to an authentication rule that prevent this user from connecting locally.
vsql -U doug -w password123 -c "select 1 c1"
vsql: FATAL 2248: Authentication failed for username "doug"But this works:
vsql -U doug -w password123 -h sandbox1 -c "select 1 c1"c1
1
(1 row)5
Answers
Yes. it can be used by other users. You need to use -U while running the vsql command
-bash-4.1$ vsql -U test
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
INFO: Connected to localhost at port 5433.
test=>
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/CommandLineOptions.htm
Sruthi, I am able to access the database via DBVis, but using vsql, I get:
vsql: FATAL 2248: Authentication failed for username
Please double check the password you are typing in.. if it still fails with vsql but works with DbViz... please open a support case with screenshots and logs.
Thanks Will do. Yes, password is correct.
Thanks Doug, yes, I do have authentication (local_hash) assigned to these users. I tried your method and yes, it worked when I passed the host name. Appreciate the help!
When you create a single authentication method, the intent is to lock things down.
See: https://www.vertica.com/blog/authentication-methods-dbadmin