CONNECT TO VERTICA

Connects to another Vertica database to enable importing and exporting data across Vertica databases, with COPY FROM VERTICA and EXPORT TO VERTICA, respectively.

Connects to another Vertica database to enable importing and exporting data across Vertica databases, with COPY FROM VERTICA and EXPORT TO VERTICA, respectively.

After you establish a connection to another database, the connection remains open in the current session until you explicitly close it with DISCONNECT. You can have only one connection to another database at a time. However, you can establish successive connections to different databases in the same session.

By default, invoking CONNECT TO VERTICA occurs over the Vertica private network. For information about creating a connection over a public network, see Using public and private IP networks.

Important

Copy and export operations can fail if either side of the connection is a single-node cluster installed on localhost.

Syntax

CONNECT TO VERTICA db-spec
    [ USER username ]
    [ PASSWORD 'password' ] ON 'host', port
    [ TLS CONFIGURATION tls_configuration ]
    [ TLSMODE PREFER ]

Parameters

db-spec

The target database, either the database name or DEFAULT.

username

The username to use when connecting to the other database. If omitted, the current user's username is used.

password

The password of the user on the target database. If omitted, you can use credential forwarding or mutual TLS to authenticate to the target database. For details, see Passwordless authentication

host

The host name of one of the nodes in the other database.

port

The port number of the other database.

tls_configuration

The TLS Configuration to use for TLS. The TLS Configuration is ignored if ImportExportTLSMode is set to any of the following:

REQUIRE_FORCE
VERIFY_CA_FORCE
VERIFY_FULL_FORCE

The effective TLS mode of CONNECT TO VERTICA changes depending on the TLSMODE of the TLS Configuration and the value of ImportExportTLSMode (for non-FORCE values). For details, see Effective TLSMode.

If the target database is configured for mutual TLS and the user on the target database can authenticate with TLS, you can use tls_configuration to authenticate instead of password.

TLSMODE PREFER

Deprecated

This parameter has been superseded by the TLS CONFIGURATION parameter. TLSMODE PREFER only takes effect if TLS CONFIGURATION is not set.

Overrides the value of configuration parameter ImportExportTLSMode for this connection to PREFER. If TLS CONFIGURATION is set or ImportExportTLSMode is set to REQUIRE_FORCE, VERIFY_CA_FORCE, or VERIFY_FULL_FORCE, then TLSMODE PREFER has no effect.

If TLSMODE PREFER and ImportExportTLSMode are both not set, CONNECT TO VERTICA uses ENABLE.

Effective TLS mode

The effective TLS mode of CONNECT TO VERTICA is determined by the TLSMODE of the TLS Configuration and the value of ImportExportTLSMode. The following table summarizes this interaction for non-FORCE values of ImportExportTLSMode:

TLS Configuration	ImportExportTLSMode	Effective TLS mode
ENABLE	PREFER	PREFER
ENABLE	Anything except PREFER	REQUIRE
TRY_VERIFY, VERIFY_CA	Anything	VERIFY_CA
VERIFY_FULL	Anything	VERIFY_FULL

Privileges

None

Security requirements

When importing from or exporting to a Vertica database, you can connect only to a database that uses trusted (username only) or password-based authentication, as described in Security and authentication. OAuth and Kerberos authentication methods are not supported.

If configured with a certificate, Vertica encrypts data during transmission using TLS and attempts to encrypt plan metadata. You can set configuration parameter ImportExportTLSMode to require encryption for plan metadata.

Passwordless authentication

If you omit the password in the call to CONNECT TO VERTICA, you can authenticate to the target database in the following ways:

Credential forwarding
TLS authentication

For brevity, in this and later sections, "caller" refers to the user that runs CONNECT TO VERTICA from the source database to connect to the target database.

Credential forwarding

Credential forwarding lets you authenticate as the current user on the target database by forwarding your hash or password, depending on the caller's authentication method on the target database. To do this, the following requirements must be met:

The caller exists in both databases.
In the target database, the caller has been granted a hash or ldap authentication record.

For an example, see Examples.

TLS authentication

TLS authentication lets you use the certificates and keys in the specified tls_configuration parameter to authenticate instead of a password. To do this, the following requirements must be met:

The caller exists in both databases.
In the source database:
- A custom TLS configurations contains a CA certificate, a client certificate, and client key that can be used to authenticate to the target database with TLS authentication.
- The caller has USAGE privileges on the TLS Configuration.
In the target database:
- The caller has been granted a TLS authentication record.
- The caller can authenticate with TLS using the certificates of TLS Configuration from the source database. This requires a CA certificate that signs the client certificate from the source database.

For an example, see Examples.

Examples

Password authentication

The following example authenticates as the dbadmin to ExampleDB on the host VerticaHost01 on port 5433:

=> CONNECT TO VERTICA ExampleDB USER dbadmin PASSWORD 'Password123' ON 'VerticaHost01',5433;
CONNECT

Credential forwarding

In the following example, Penny wants to run CONNECT TO VERTICA from db_1 to connect to db_2:

Note

In general, the LDAP link service should be used instead to synchronize users between databases. For demonstration purposes, the example below manually configures the user penny on both databases. You can skip these steps if you use LDAP Link.

On db_1, create the user penny:

=> CREATE USER penny IDENTIFIED BY 'my_password';

On db_2, create the user penny with the same hash, salt, and security (hashing) algorithm:

On db_1, retrieve the hash (from the password column) and salt from PASSWORDS:

=> SELECT user_name, password, salt FROM passwords WHERE user_name='penny';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------
user_name | penny
password  | sha512b2e0911954a79d9d419b7b42774d36d17dd8a663c966bf0dd8f4cd6aad00d3c7ee7ba74b9bf0e56071cd995ae04aeaae537b35903e97255ed5fe286b6ff0d00a
salt      | eac4ad840264e8c590120b31b815318f

On db_1, retrieve the effective security algorithm from PASSWORD_AUDITOR:

=> SELECT user_name, effective_security_algorithm FROM password_auditor WHERE user_name='penny';
 user_name | effective_security_algorithm
-----------+------------------------------
 penny     | SHA512
(1 row)

On db_2, create the user penny:
```
=> CREATE USER penny;
```
On db_2, use ALTER USER to set the security algorithm to the value in db_1. For details, see Password hashing algorithm:
```
=> ALTER USER penny SECURITY_ALGORITHM 'SHA512';
```

On db_2, set penny's password using the hash and salt from db_1:

=> ALTER USER penny IDENTIFIED BY 
'sha512b2e0911954a79d9d419b7b42774d36d17dd8a663c966bf0dd8f4cd6aad00d3c7ee7ba74b9bf0e56071cd995ae04aeaae537b35903e97255ed5fe286b6ff0d00a'
SALT 'eac4ad840264e8c590120b31b815318f';

On db_1, enable credential forwarding for penny by setting EnableConnectCredentialForwarding (disabled by default):
```
=> ALTER USER penny SET EnableConnectCredentialForwarding=1;
```
On db_2, create an authentication record with the hash method:
Note

This example authentication record lets its grantees authenticate to Vertica without TLS. In general, you should also configure client-server TLS and then specify HOST TLS so the hashed password is forwarded to the target database over a secure connection. For example:
```
      
=> CREATE AUTHENTICATION v_hash_auth METHOD 'hash' HOST TLS '0.0.0.0/0';
```
```
=> CREATE AUTHENTICATION v_hash_auth METHOD 'hash' HOST '0.0.0.0/0';
```

Grant the authentication record to penny:

=> GRANT AUTHENTICATION v_hash_auth TO penny;

On db_1, run CONNECT TO VERTICA to connect to db_2, omitting the password. This example uses the default port:
```
=> CONNECT TO VERTICA db_2 ON 'example.com', 5433;
```

TLS authentication

In the following example, Penny wants to run CONNECT TO VERTICA from db_1 to connect to db_2 without specifying her password:

Create the user penny on both databases.

=> CREATE USER penny IDENTIFIED BY 'my_password';

On db_1, create or import a CA certificate, server certificate, and client certificate.

-- Create a CA certificate
=> CREATE KEY root_key TYPE 'RSA' LENGTH 2048;

=>  CREATE CA CERTIFICATE mtls_root_cert
SUBJECT '/C=US/ST=Massachusetts/L=Burlington/O=OpenText/OU=Vertica/CN=Vertica Root CA'
VALID FOR 3650
EXTENSIONS 'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsComment' = 'Vertica generated root CA cert'
KEY mtls_root_key;

-- Create a client certificate, signing it with the CA certificate
=> CREATE KEY client_key TYPE 'RSA' LENGTH 2048;

=> CREATE CERTIFICATE mtls_client_cert
SUBJECT '/C=US/ST=Massachusetts/L=Burlington/O=OpenText/OU=Vertica/CN=penny'
SIGNED BY mtls_root_cert
EXTENSIONS 'nsComment' = 'Vertica client cert', 'extendedKeyUsage' = 'clientAuth'
KEY mtls_client_key;

-- Create a server certificate, signing it with the CA certificate
CREATE KEY mtls_server_key TYPE 'RSA' LENGTH 2048;

CREATE CERTIFICATE mtls_server_cert
SUBJECT '/C=US/ST=Massachusetts/L=Burlington/O=OpenText/OU=Vertica/CN=*.example.com'
SIGNED BY mtls_root_cert
EXTENSIONS 'extendedKeyUsage' = 'serverAuth'
KEY mtls_server_key;

On db_1, create a custom TLS configuration to use with CONNECT TO VERTICA, adding the mtls_client_cert and mtls_root_cert as the certificates and setting TLSMODE to ENABLE or higher:
```
=> CREATE TLS CONFIGURATION mtls;
=> ALTER TLS CONFIGURATION mtls CERTIFICATE mtls_client_cert ADD CA CERTIFICATES mtls_root_cert TLSMODE 'ENABLE';
```

On db_2, import the CA certificate, server certificate, and server key from db_1. You can retrieve the contents of a certificate and key from the CERTIFICATES and CRYPTOGRAPHIC_KEYS system tables:

      
=> CREATE CA CERTIFICATE mtls_root_cert AS '-----BEGIN CERTIFICATE-----certificate_text-----END CERTIFICATE-----';
=> CREATE CERTIFICATE mtls_server_key AS '-----BEGIN PRIVATE KEY-----key-----END PRIVATE KEY-----'
=> CREATE CERTIFICATE mtls_server_cert AS '-----BEGIN CERTIFICATE-----certificate_text-----END CERTIFICATE-----';

On db_2, configure mutual mode client-server TLS using mtls_root_cert and mtls_server_cert and setting the TLSMODE to TRY_VERIFY or higher:
```
=> ALTER TLS CONFIGURATION server CERTIFICATE mtls_server_cert ADD CA CERTIFICATES mtls_root_cert TLSMODE 'TRY_VERIFY';
```

On db_2, create an authentication record for TLS authentication.

=> CREATE AUTHENTICATION mtls_auth METHOD 'tls' HOST TLS '0.0.0.0/0';

On db_2, grant mtls_auth to penny:

=> GRANT AUTHENTICATION mtls_auth TO penny;

On db_1, run CONNECT TO VERTICA to connect to db_2, specifying the mtls TLS Configuration. This example uses the default port:
```
=> CONNECT TO VERTICA vmart USER penny ON 'example.com', 5433 TLS CONFIGURATION mtls;
```