Secure Connections for ODBC/JDBC Clients and ybsql ​

This section covers SSL connection requirements and methods that are specific to the PostgreSQL ODBC and JDBC drivers and the ybsql client tool, which are based on the same libpq implementation. This section assumes that you are working with the PostgreSQL-developed ODBC and JDBC drivers. For drivers provided by other vendors, the concepts are the same but the specific implementation details, such as property names, are likely to be different.

The PostgreSQL drivers and ybsql use the PostgreSQL model of SSL modes to determine whether TLS/SSL is used when they connect to the server and, if so, how and under what restrictions. You can set the SSL mode with the operating-system environment variable YBSSLMODE or PGSSLMODE, or with an sslmode connection property, depending on how you are connecting.

If you choose an SSLMODE requiring verification, the certificate(s) of one or more trusted CAs must be located in ~/.yellowbrick/root.cert on Linux clients or %APPDATA%\yellowbrick\root.crt on Windows clients. These modes are used to prevent being a victim of server identity spoofing.

SSL Mode for ODBC/JDBC Clients and ybsql ​

Important: SSL-only connectivity is enabled for all data warehouse instances. Therefore, all client connections must use SSL.

The default Yellowbrick SSL mode is prefer, which supports encryption of all network communications between the client and the server instance but does not check certificates to verify that the host system is trusted. If you want to use the default prefer mode, you do not need to specify an SSL mode when you log into an instance from ybsql. The allow and require modes also attempt SSL connections and will fail if an SSL connection cannot be made. For example:

% export YBSSLMODE=require
% ybsql                  
Password: 
ybsql (6.1.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type:  \h for help with SQL commands
      \? for help with ybsql commands
      \g or terminate with semicolon to execute query
      \q to quit
...

If you want to use one of the SSL modes that verify trust in addition to encrypting network communication (verify-ca or verify-full), you may need to obtain and export the "chain of trust" from the Yellowbrick instance, make sure the certificates in the chain are valid, and place them in the correct location on the client system. A verified SSL connection checks the signature of the certificate to verify that it comes from a trusted certificate authority (CA), and that the hostname used in the connection matches the subject alternative name (SAN) in the certificate.

For details about obtaining certificates and troubleshooting SSL connections, see Obtaining a Certificate Chain.

If you don't set up the chain of trust correctly, you will see the following error:

% export YBSSLMODE=verify-ca
% ybsql                     
ybsql: root certificate file "*******/.yellowbrick/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

SSL Modes ​

Whether you use the environment variable or a connection property, YBSSLMODE accepts the following values:

Creating a Client-Side root CA File ​

If you are using an SSL mode that verifies trust, you need to have a root CA file on your client host that contains the needed certificates. The file will typically be provided by your DBA or IT staff. If that is not the case, you can manually create the file yourself. Note that this file:

• Must contain the intermediary cert(s) and root cert for the server(s) you are connecting to.
• May contain intermediary and root certs for connecting to multiple different servers.
• Must be a text file that contains PEM certificates (base 64-encoded text).

You can explicitly include the root CA bundle file name as a connection property or simply use the default file name and path for your specific client operating system, as shown in the following table:

Yellowbrick and PostgreSQL diverge slightly in terms of SSL support. Yellowbrick does not support two-way trust; therefore, only the root.cert or root.crt files can be used when connecting to Yellowbrick.

Environment Variables and Connection Properties ​

You can set the defaults for most connection properties by using environment variables. You can also explicitly set them and override the defaults or environment variable settings as part of the connection. The following table lists the environment variables and their libpq connection property names.

Setting Environment Variables ​

The most common way to set the TLS/SSL connection options you want to use is through the environment variables. Using the environment variables will set the default values for all drivers and applications that use libpq. These values can then be overridden at the application or driver level when connecting. When these properties are evaluated, the order of precedence is as follows:

• Connection property (if set)
• YB* environment variable
• PG* environment variable

The command syntax for setting environment variables differs by operating system and command shell. For example, to set YBSSLMODE to require, use:

In This Section

• Secure Connections for ODBC Clients

• Secure Connections for JDBC Clients

• Secure Connections for ybsql (libpq)

• Secure Connections for DBeaver Clients

Parent topic:Configuring SSL/TLS for Tools and Drivers