C3 AI Documentation Home

Configure SSL/TLS for External PostgreSQL Database

To connect to an external PostgreSQL database over SSL/TLS, give the C3 AI server your keystore, your truststore, and their passwords. This topic uses a PostgreSQL database hosted on Amazon RDS as the example.

You prepare and load the keystore and truststore files, describe them in a TlsCertificate object, add that object to a JdbcCredentials object, and set the credentials on your SqlSourceSystem. You then check that the connection works and that it uses SSL.

C3 AI Studio offers a visual tool for building data pipelines called Data Fusion. Data Fusion configures data sources and connects them to C3 Canonicals and Entities. For more information, see Data Fusion Overview.

Prepare keystore and truststore files

Prepare the files from the certificates your database provider gives you:

  1. Download the certificates from your database provider. Providers usually supply them in .pem format.
  2. Convert the .pem certificates to a keystore format that C3 AI supports, such as JKS (Java KeyStore). On Unix-based systems you can use the keytool command, or any other method you prefer. This topic does not cover the conversion itself.
  3. Set a password for the keystore file and for the truststore file during the conversion.

Load keystore and truststore files

Make both files available to the C3 AI server in one of two ways:

  • Upload them from the C3 AI Console:
    1. Open the C3 AI Console.
    2. Select Tools in the upper left corner.
    3. Select Load File, and then upload your keystore and truststore files.
  • Place them in a directory that the C3 AI server file system can access. As a best practice, store the keystore and truststore files in a secure vault.

Create the TlsCertificate object

A TlsCertificate object holds the type, location, and password of your truststore and keystore files, and the SSL mode. The SSL mode sets the level of certificate verification the connection requires.

JavaScript
tlsCertificate = {
    "trustStoreType": "<truststore_type>",  // e.g. JKS, PKCS12, etc.
    "trustStorePath": "<path_to_truststore_location>", // e.g. gcs://<blob_store_path>
    "trustStorePassword": "**********",
    "keyStoreType": "<keystore_type>",
    "keyStorePath": "<path_to_keystore_location>",
    "keyStorePassword": "**********",
    "sslMode": "<TlsConnectionModeKind>" // e.g. VERIFY_CA, VERIFY_FULL, etc.
  }

The object has the following fields:

  • trustStoreType: The type of truststore, for example JKS or PKCS12.
  • trustStorePath: The path to the truststore file, for example gcs://<blob_store_path>.
  • trustStorePassword: The password for the truststore file.
  • keyStoreType: The type of keystore, for example JKS or PKCS12.
  • keyStorePath: The path to the keystore file.
  • keyStorePassword: The password for the keystore file.
  • sslMode: The SSL mode to use, for example VERIFY_CA or VERIFY_FULL.

Create JdbcCredentials with the TlsCertificate

A JdbcCredentials object holds the connection details for your PostgreSQL database. Add the TlsCertificate object in its certs field to secure the connection with SSL/TLS.

JavaScript
creds = {
    "username": "<username>",
    "serverEndpoint": "<endpoint>",
    "port": <port>,
    "password": "<password>",
    "datastore": "postgres",
    "database": "<database_name>",
    "certs": tlsCertificate
  }

The object has the following fields:

  • username: The username for accessing the PostgreSQL database.
  • serverEndpoint: The endpoint URL of the PostgreSQL server.
  • port: The port number on which the PostgreSQL server listens.
  • password: The password for the specified username.
  • datastore: The type of datastore, postgres for this connection.
  • database: The name of the database to connect to.
  • certs: The TlsCertificate object that holds the SSL/TLS configuration.

Set the credentials on the SQL source system

Set the credentials on your external SQL source system so that it can authenticate with the database over the secure connection. Get the source system by name with SqlSourceSystem.forName, and then pass the JdbcCredentials object to SqlSourceSystem#setCredentials:

JavaScript
sys = SqlSourceSystem.forName("<sql_source_system_name>")
sys.setCredentials(creds)

The call takes the following values:

  • <sql_source_system_name>: The name of the SQL source system you are configuring.
  • creds: The JdbcCredentials object that holds the connection details and the SSL/TLS configuration.

Verify the connection

Check that the source system can reach the database with the credentials and settings you configured. Call SqlSourceSystem#ping on the source system object sys, and then read the reachable field of the result:

JavaScript
ping = sys.ping()
ping.reachable
// ping.reachable should be true

A value of true means the connection works.

Verify the SSL connection

Check that the connection to the SQL source system uses SSL, so that data between your application and the database is encrypted. Run the following query against the pg_stat_ssl view. The query needs cluster administrator access.

JavaScript
query = "SELECT ssl, version, cipher, bits FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
res = sys.connect().queryTuples(query).collect()
// the result shows whether the connection uses SSL, and its SSL attributes.

In the result, ssl shows whether the connection uses SSL, and version, cipher, and bits describe the SSL session.

See also

Was this page helpful?