Product DocumentationAppearance
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.
SSL Modes
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 a connection property, depending on how you are connecting.
Whether you use the environment variable or a connection property, you can set YBSSLMODE to any of the following values:
| SSL Mode | Description |
|---|---|
allow | Attempt a non-SSL connection. If it fails, attempt an SSL connection. |
disable | Only attempt a non-SSL connection. |
prefer | Attempt an SSL connection, but do not verify trust. If the connection fails, attempt a non-SSL connection. prefer is the default option. |
require | Only attempt an SSL connection. If a root CA file is present, verify the certificate as if verify-ca is specified. |
verify-ca | Attempt an SSL connection and verify that the server certificate is issued by a trusted CA. Verify that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA). |
verify-full | Only attempt an SSL connection. Verify that the server certificate is issued by a trusted CA and that the server host name matches the name in the certificate. Verify that the server host name matches its certificate's Common Name (CN) attribute. If the CN attribute starts with an asterisk (*), it is a wildcard and will match all characters except a dot (.). This means the certificate will not match subdomains and will not match if an IP address is used instead of a host name. verify-full is recommended in most security-sensitive environments. |
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:
| Operating System | Location | Notes |
|---|---|---|
| Linux, AIX, macOS | ~/.postgresql/root.cert~/.yellowbrick/root.cert | Root certs are needed for ybsql but not for Java-based ybtools. Intermediary certs may be needed in both cases. |
| Windows | %APPDATA%\postgresql\root.crt%APPDATA%\yellowbrick\root.crt |
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.
| Yellowbrick Environment Variable | PostgreSQL Environment Variable | Connection Property | Notes |
|---|---|---|---|
YBHOST | PGHOST | host | Use a fully qualified host name if you are connecting with an SSL verify trust mode. |
YBHOSTADDR | PGHOSTADDR | hostaddr | Do not use with an SSL verify trust mode. |
YBPORT | PGPORT | port | If no port is specified, the default is 5432. |
YBDATABASE | PGDATABASE | dbname | |
YBUSER | PGUSER | user | |
YBPASSWORD | PGPASSWORD | password | |
YBAPPNAME | PGAPPNAME | application_name | |
YBSSLMODE | PGSSLMODE | sslmode | See SSL Modes for details. |
YBSSLROOTCERT | PGSSLROOTCERT | sslrootcert | |
| `` | PGREQUIRESSLMODE | requiressl | Deprecated in favor of YBSSLMODE/PGSSLMODE. |
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:
| Operating System | Statement |
|---|---|
| Linux, AIX, and macOS | export YBSSLMODE=require |
| Windows CMD | set YBSSLMODE=require |
| Windows PowerShell | $env:YBSSLMODE = 'require' |
In This Section
Parent topic:Configuring SSL/TLS for Tools and Drivers