C3 AI Documentation Home

Configure SSL/TLS for External PostgreSQL Database

To securely connect your external PostgreSQL database using SSL/TLS, follow these steps to provide the C3 AI server with your keystore, truststore, and their corresponding passwords. This topic demonstrates how to connect to an external PostgreSQL database hosted on Amazon RDS.

Prepare keystore and truststore files

  1. Download Certificates – Obtain the necessary certificates from your database provider. These certificates are typically in .pem format.
  2. Convert Certificates – Convert the .pem certificates to a keystore format supported by C3 AI, such as JKS (Java KeyStore). This can be done using the keytool command on Unix-based systems or any other preferred method. Note: Converting from .pem to keystore format is outside the scope of this document.
  3. Set Passwords – Ensure that you set passwords for both the keystore and truststore files during the conversion process.

Load keystore and truststore files

  1. Using the Console:

    a. Navigate to your console page.

    b. Select the Tools button in the upper left corner.

    c. Select Load File from the dropdown menu to upload your keystore and truststore files.

  2. Using the File System:

    a. Alternatively, place the keystore and truststore files in a directory accessible by the C3 AI server file system.

    b. For best practices, it is recommended to store these files in a secure vault.

By following these steps, you configure your C3 AI server to connect securely to your external PostgreSQL database using SSL/TLS.

Create your TlsCertificate object

To establish a secure SSL connection to your external PostgreSQL database, you need to create a TlsCertificate object. This object will contain the necessary information, including the locations of your keystore and truststore files, their passwords, and the SSL mode.

Define the TlsCertificate object

  • Specify the type and location of your truststore and keystore files.
  • Include the passwords for both the truststore and keystore.
  • Set the SSL mode to define the level of verification required.
JavaScript
tlsCertificate = {
    "trustStoreType": "<truststore_type>",  // e.g: JKS, PKCS12, etc.
    "trustStoreLocation": "<path_to_truststore_location>", // e.g. gcs://<blob_store_path>
    "trustStorePassword": "**********",
    "keyStoreType": "<keystore_type>",
    "keyStoreLocation": "<path_to_keystore_location>",
    "keyStorePassword": "**********",
    "sslMode": "<TlsConnectionModeKind>" // e.g VERIFY_CA, VERIFY_FULL, etc.
  }

where:

  • trustStoreType — The type of truststore (e.g., JKS, PKCS12).
  • trustStoreLocation — The path to the truststore file (e.g., gcs://<blob_store_path>).
  • trustStorePassword — The password for the truststore file.
  • keyStoreType — The type of keystore (e.g., JKS, PKCS12).
  • keyStoreLocation — The path to the keystore file.
  • keyStorePassword — The password for the keystore file.
  • sslMode — The SSL mode to use (e.g., VERIFY_CA, VERIFY_FULL).

By following these steps, you can configure the TlsCertificate object to enable a secure SSL connection to your external PostgreSQL database.

Create JdbcCredentials with the TlsCertificate

In this step, you create a JdbcCredential object that stores the necessary connection details for your PostgreSQL database, along with the TlsCertificate object you created earlier. This JdbcCredential object will be used to establish a secure connection to the database.

Define the JdbcCredential object

  • Include the database connection details such as username, server endpoint, port, password, and database name.
  • Incorporate the TlsCertificate object to ensure the connection is secured using SSL/TLS.
JavaScript
creds = {
    "username": "<username>",
    "serverEndpoint": "<endpoint>",
    "port": <port>,
    "password": "<password>",
    "datastore": "postgres",
    "database": "<database_name>",
    "certs": tlsCertificate
  }

where:

  • 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 is listening.
  • password — The password for the specified username.
  • datastore — The type of datastore, which in this case is "postgres".
  • database — The name of the specific database to connect to.
  • certs — The TlsCertificate object containing SSL/TLS configuration details.

By following these steps, you can create a JdbcCredential object that securely connects to your PostgreSQL database using the provided SSL/TLS certificates.

Set the credentials using SqlSourceSystem

In this step, you will set the credentials for an external SQL source system using the SqlSourceSystem#setCredentials method.

This step is necessary to configure the external SQL source system with the credentials required for connecting to the database. By setting these credentials, you enable the system to authenticate and securely communicate with the database.

Retrieve the SQL source system

Use the SqlSourceSystem.forName method to get the specific SQL source system by its name.

Set the credentials

Apply the credentials using the setCredentials method, passing in the JdbcCredential object created in the previous step.

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

where:

  • sql_source_system_name — The name of the SQL source system you are configuring.
  • creds — The JdbcCredential object containing the connection details and SSL/TLS configuration

By following these steps, you will successfully set the credentials for your external SQL source system, ensuring secure and authenticated access to the database.

Verify connection

In this step, you will verify that your system can successfully connect to the external SQL source system. This is done using the SqlSourceSystem#ping method, which checks the connectivity status.

The purpose of this step is to ensure that the credentials and connection settings you have configured are correct and that the system can establish a connection to the SQL source system.

  • Ping the SQL Source System: Use the ping method on the SqlSourceSystem object to check if the connection is reachable.
  • Check Reachability: Verify the result of the ping to confirm that the connection is successful.
Python
ping = sys.ping()
ping.reachable
// ping.reachable should be true

where:

  • sys — The SqlSourceSystem object representing your configured SQL source system.

By following these steps, you will verify that your system can successfully connect to the external SQL source system, ensuring that all configurations are correct and the connection is operational.

Verify SSL connection

In this step, you will verify whether your connection to the SQL source system is using SSL. This verification requires Cluster Admin access and involves running a specific SQL query to check the SSL status and related attributes.

The purpose of this step is to confirm that your connection to the SQL source system is secured using SSL. This ensures that data transmitted between your application and the database is encrypted and protected.

Python
query = "SELECT ssl, version, cipher, bits FROM pg_stat_ssl WHERE pid = pg_backend_pid();"
res = sys.connect().queryTuples(query).collect()
// the result will if you are using ssl or not and various attributes related to it.

Analyze the query results to verify the SSL status and other related attributes.

See also

Was this page helpful?