Create Database Tools Connections With Terraform

In this post we will look at setting up a Database Tools connection resource in Oracle Cloud Infrastructure (OCI) using the OCI Terraform provider. Database Tools connections in OCI are first-class resources just like other cloud primitives such as virtual cloud networks (VCN), compute instances, or databases. As such, you can manage connection resources using Terraform!

If you are not familiar with what a Database Tools connection in OCI does or how to set one up you can find more information in these related posts:

• Database Tools Connections
• Creating DB Users, Connection, and Private Endpoints in OCI (by Jeff Smith)
• Introducing SQL Developer Web to Oracle Cloud Infrastructure (by Jeff Smith)
• Setup OCI DBtools Connections with an ADB Access Control List

And of course, there is the official documentation:

• Database Tools Documentation

TL/DR: If you want to skip the rest of this post and just jump straight to code scroll to the end or, OCI provides more examples here:

You may find the following related links helpful for future reference:

• OCI Terraform Provider Documentation
• Terraform Developer Documentation
• Database Tools API

Terraform is both a command line tool and a language for declaring resources that you create, often in the cloud, but cloud is not a hard requirement given providers can do non-cloud things like spin up a container locally.

Providers extend Terraform with new functionality for a given task. For example, they are responsible for converting your configuration into actual resources on a cloud service. The OCI providers for Terraform make REST API calls to the various control planes of OCI services.

To create OCI resources using Terraform I need to configure the OCI provider (tell it how to authenticate) and then configure OCI resources using resource blocks. What follows is a light tutorial on setting up Terraform locally, configuring it and then using it to create a resource. If you are only interested in the syntax for creating a connection skip to the end or see the examples linked above.

Prerequisites

If you want to follow along with this post, there are a number of pre-requisites you need to take care of which I will not provide steps for in this post.

I assume the following:

• Terraform is installed and you can run terraform without error, for example:

$ terraform version
Terraform v1.10.5
...

• An Oracle Cloud Infrastructure tenancy exists
• A compartment is created in the tenancy where the Database Tools connection resource will be created
• Policies are in place that authorize your authenticated principal to manage resources in the target compartment, or perhaps you are the administrator
• An Autonomous Database exists in the same compartment for this demo with Public Access enabled (in practice the database would be in a different compartment but I will keep it simple for the demo)
• An SSO wallet for that ADB database stored in a secret in a vault

• A valid database username
• A valid database password (for the database user) stored in a secret in a vault
• An OCI configuration profile that you are able to use to call OCI services (used for authenticating via Terraform later)

About Secrets in Vaults

Regarding creating secrets in a vault, I don't cover the steps in this post but you can review OCI's documentation for Secrets in Vault if you need some pointers on how to create secrets. You can roughly compare secrets in a vault to a password manager, but for an OCI cloud tenancy.

Secrets in a vault are a hard requirement for the Database Tools service for connections because we don't store your passwords or wallet contents in the Database Tools service directly, only an opaque identifier that points to a secret.

Secrets are used at runtime by IAM principals that have been granted access via policies of your tenancy. At this point I'd like to recommend that if you have never created a Database Tools connection in OCI, it can be helpful to go through the process manually at least once to learn how it all works. The Database Tools UI in the OCI console has some nice features to make this all a little easier.

Finally, we are ready to begin. Lets start with setting up Terraform!

Configure Terraform

For this example I created a new folder called dbtools-demo and used that for all the Terraform code in the example.

The way I setup the configuration is not required. Terraform is pretty flexible with respect to configuration files. Feel free to dump everything in two files (main.tf and terraform.tfvars if you are following along)

I am going to keep it simple in this example (no sub modules) but extract some bits into separate files in the "root module" for my own benefit.

• First, inside my working directory I created a new file called config.tf with the following contents:

variable "oci_profile" {}

terraform {
  required_version = ">= 1.10"

  required_providers {
    oci = {
      source  = "oracle/oci"
      version = ">= 6.21.0"
    }
  }
}

provider "oci" {
  config_file_profile = var.oci_profile
}

If you are targeting an older version you may need to substitute hashicorp/oci for the source property of the OCI provider. I am using oracle/oci.

• Next I added a new file called terraform.tfvars with the following contents.

oci_profile = "DEFAULT"

This file will be used by terraform at runtime to populate input variable declarations with actual values. You could pass these in via command line arguments or environment variables if you prefer.

• Next I ran terraform init from my dbtools-demo directory to download the OCI provider and initialize the Terraform backend. (a local backend by default)

$ terraform init
Initializing the backend...
Initializing provider plugins...
- Finding oracle/oci versions matching ">= 6.21.0"...
- Installing oracle/oci v6.23.0...
...

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

Query OCI With a Data Source

Next we will add something to the Terraform state so that we have a value to look at for debugging. This will also validate the OCI provider is working as expected.

• I added a file to the working directory called main.tf with the following contents. Here I introduced two new variables so I added these to my terraform.tfvars file with appropriate values specified for compartment_name and tenancy_ocid.

variable "tenancy_ocid" {}
variable "compartment_name" {}

data "oci_identity_compartments" "demo_compartment" {
  compartment_id = var.tenancy_ocid
  name = var.compartment_name
}

output "compartment_id" {
  value = data.oci_identity_compartments.demo_compartment.compartments[0].id
}

Let's break this down. First I declare some variables that I will pass in when I run terraform on the command line.

Then I used a data source to ask OCI Identity for a list of compartments (effectively one) with the specified name.

Finally an output block defined a value compartment_id that is saved in the Terraform state. I did this just for debugging but in complex configurations with sub modules outputs are often useful.

A note about the data source above, it requires a compartment_id parameter. This is not the ID of the compartment I am searching for, but rather the ID of the parent compartment to search within (usually the tenancy). Technically this is a compartment list operation but later I grab the first compartment returned because compartment names are unique.

Remember those prerequisites? I already have a tenancy, I created a compartment in my tenancy called icodealot_dev for the purpose of the demo. And, I already have an OCI configuration file setup with a DEFAULT profile. For reference my terraform.tfvars file now looks like this:

oci_profile = "DEFAULT"
tenancy_ocid = "ocid1.tenancy.oc1..aaaabbbbccccmytenancyocid"
compartment_name = "icodealot_dev"

• With all of that in place, I ran terraform plan

$ terraform plan
data.oci_identity_compartments.demo_compartment: Reading...
data.oci_identity_compartments.demo_compartment: Read complete after 2s ...

Changes to Outputs:
  + compartment_id = "ocid1.compartment.oc1..aaaabbbccccdemocompartmentocid"

...

Success! This is great and now we know that the OCI provider is configured correctly and Terraform is able to talk to OCI using my OCI configuration file.

To validate an OCI profile (i.e. with a developer's key pair) is correct, you can use the OCI command line to do something like:

$ oci --profile <profile> iam compartment list --name icodealot_dev
{
  "data": [
    {
      "compartment-id": "ocid1.tenancy.oc1..aaaabbbccccmytenancyocidappearshere",
      "defined-tags": {
...

If the config profile uses temporary session tokens instead of an uploaded key pair you will need to add oci options such as -auth security_token, etc. If you don't have it yet, or you are are not sure that you have the correct compartment name (icodealot_dev in the example above) you can just list compartments with a --limit 1 and make sure you get back a successful response.

Create a Connection with Terraform

Now it is time to add the configuration for a Database Tools connection to the Terraform code. I updated main.tf to add the following variables at the top:

...
variable "db_name" {}
variable "db_user" {}
variable "db_password_secret_id" {}
variable "db_wallet_secret_id" {}

There are four new variables here but I would also like to note, you can also just hard-code known values in your config.tf and main.tf files for testing purposes. In addition to new variables defined in main.tf I updated my terraform.tfvars file to set the correct values. For example:

oci_profile = "DEFAULT"
tenancy_ocid = "ocid1.tenancy.oc1..aaaabbbbccccmytenancyocid"
compartment_name = "icodealot_dev"
db_name = "TESTFOR23AI"
db_user = "ADMIN"
db_password_secret_id = "ocid1.vaultsecret.oc1.phx.aaabbbccccpasswordsecretid"
db_wallet_secret_id = "ocid1.vaultsecret.oc1.phx.aaabbbccccwalletsecretid"

Recall from the prerequisites that we needed a password and SSO wallet secret in a vault as well as an existing ADB database.

Next we will use data sources to lookup connection related details that we will need for the Database Tools connection instead of hard-coding values such as a connection string. I also use this data source to provide an optional "related resource" on the Database Tools connection so that we get a nice link from the connection to the database in the UI of the OCI console.

I added the following Terraform code to my main.tf file.

...
data "oci_database_autonomous_databases" "demo_database" {
  compartment_id = local.demo_compartment_id
  display_name = var.db_name
}

locals {
  demo_compartment_id = data.oci_identity_compartments.demo_compartment.compartments[0].id
  demo_database = data.oci_database_autonomous_databases.demo_database.autonomous_databases[0]
  database_id = local.demo_database.id
  adb_connection_profiles = local.demo_database.connection_strings[0].profiles
  connection_string = [for p in local.adb_connection_profiles : p.value if p.consumer_group == "LOW"][0]
}

Notice that the locals block here comes after the data source but we are still able to use the value of local.demo_compartment_id here. Terraform takes care of resolving references and the order defined in configuration files is generally not significant, though you are also free to define multiple locals blocks in your files if it bothers you to have this visual oddity.

It's finally time to put all that work to good use. Here is our definition of the Database Tools connection at the end of our main.tf file.

...
resource "oci_database_tools_database_tools_connection" "demo_connection" {
  compartment_id = local.demo_compartment_id
  display_name = "dbtools_connection_terraform_demo"
  type = "ORACLE_DATABASE"
  related_resource {
    entity_type = "AUTONOMOUSDATABASE"
    identifier = local.database_id
  }
  user_name = var.db_user
  connection_string = local.connection_string
  user_password {
    value_type = "SECRETID"
    secret_id  = var.db_password_secret_id
  }
  key_stores {
    key_store_type = "SSO"
    key_store_content {
      value_type = "SECRETID"
      secret_id = var.db_wallet_secret_id
    }
  }
}

Finally! I ran terraform plan, validated the changes, and then ran terraform apply.

$ terraform apply
...
Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

...
Apply complete! Resources: 1 added, 0 changed, 0 destroyed.
...

Checking in the OCI console I can see that there is a new connection so I can Validate to make sure everything works as expected:

Since we took the time to setup a related resource we also get relevant links to the database from the connection screen. Cool!

I'll explore topics like this in future posts that build on the idea of using Terraform to manage resources in the cloud. I hope you found this post useful and I always appreciate feedback letting me know.

Thanks for reading and until next time.

Cheers!