Connect to database from a non-database server

[JimKnicely]

Hi,

We can connect to a Vertica database from any Linux server which is running the same version of Linux as the database server. Simply copy the vsql binary from the database server to your local server then connect!

For example, say I want to connect to the vertica01 server from a server named non-cluster01.

First I have to copy the vsql binary from vertica01 to non-cluster01:

Code: Select all

[root@non-cluster01 ~]# scp root@vertica01:/opt/vertica/bin/vsql /usr/local/bin
root@vertica01's password: *********

[root@non-cluster01 ~]# which vsql
/usr/local/bin/vsql

Now I can connect using vsql:

Code: Select all

[root@non-cluster01 ~]# vsql -h vertica01 -U vusername -W
Password: *********
Welcome to vsql, the Vertica Analytic Database v5.0.11-0 interactive terminal.

Type:  \h for help with SQL commands
       \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vusername=>

[DataWhisperer]

We can connect to a Vertica database from any Linux server which is running the same version of Linux as the database server. Simply copy the vsql binary from the database server to your local server then connect!

I've tested this, it can be any "supported" linux distro (or compatible).

Also, make sure the authentication file is updated, especially if you're using IP based filtering. Just because you can log in from one system (or localhost) doesn't necessarily mean one can log in from another system.

Example 1:

Code: Select all

ClientAuthentication = local datawhisperer trust

Would allow me to login locally w/o a password

Example 2:

Code: Select all

ClientAuthentication = host 10.0.0.0/8 datawhisperer password

Would allow me to login from a 10.0.0.0/8 IP w/ a password

Example 3:

Code: Select all

ClientAuthentication = host 10.0.0.0/8 datawhisperer ldap "ldaps://ldap.server.com/dc=,dc=;cn=;,ou="

Would allow me to login from a 10.0.0.0/8 IP via LDAPS auth.

If example 1 was setup, I could ONLY login locally, regardless if I tried from remote host. Conversely, if I tried to login locally with example 2 or 3 and used 127.0.0.1 or localhost, it would reject me as I'm not accessing from a 10.0.0.0/8 network.

You can string them all together to allow it though

Example 4:

Code: Select all

ClientAuthentication = local datawhisperer trust, ClientAuthentication = host 10.0.0.0/8 datawhisperer password, ClientAuthentication = host 10.0.0.0/8 datawhisperer ldap "ldaps://ldap.server.com/dc=,dc=;cn=;,ou="

Would allow me to login locally w/o a password, if I was from 10.0.0.0 it would verify against my local DB password, and if that failed, LDAP.

Also, if you use LDAP, you will need a local account on the DB still to login (just no password set).