Use Terraform to enable and disable SQL Explorer and Audit for an ApsaraDB RDS for PostgreSQL instance - ApsaraDB RDS

You can use Terraform to manage an ApsaraDB RDS for PostgreSQL instance using code. This topic describes how to use Terraform to enable or disable the SQL Explorer and Audit feature. This feature helps you retrieve information about the SQL statements that are executed on your database instance and troubleshoot performance issues.

Note

You can run the sample code in this topic with a few clicks. For more information, visit Terraform Explorer.

Prerequisites

An RDS instance is created. For more information, see Create an RDS instance.
The RDS instance is in the Running state. You can use one of the following methods to check whether the RDS instance is in the Running state:
- Check the value of the status parameter by following the instructions provided in Query instance details. If the value is Running, the RDS instance is in the Running state.
- Log on to the ApsaraDB RDS console, switch to the required region, find the RDS instance, and then check the instance status.
An Alibaba Cloud account has all permissions on resources within the account. If an Alibaba Cloud account is leaked, the resources are exposed to major risks. We recommend that you use a Resource Access Management (RAM) user and create an AccessKey pair for the RAM user. For more information, see Create a RAM user and Create an AccessKey pair.

You must use RAM to manage access permissions on cloud resources in an efficient manner. This helps meet the requirements for multi-user collaboration and lets you grant permissions to users based on the principle of least privilege (PoLP) to prevent security vulnerabilities caused by excessive permissions. For more information, see Grant permissions to RAM users.

{
    "Version": "1",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "vpc:DescribeVpcAttribute",
                "vpc:DescribeRouteTableList",
                "vpc:DescribeVSwitchAttributes",
                "vpc:DeleteVpc",
                "vpc:DeleteVSwitch",
                "vpc:CreateVpc",
                "vpc:CreateVSwitch"
            ],
            "Resource": "*"
        },
        {
            "Action": "rds:*",
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Action": "dbs:*",
            "Resource": "acs:rds:*:*:*",
            "Effect": "Allow"
        },
        {
            "Action": "hdm:*",
            "Resource": "acs:rds:*:*:*",
            "Effect": "Allow"
        },
        {
            "Action": "dms:LoginDatabase",
            "Resource": "acs:rds:*:*:*",
            "Effect": "Allow"
        },
        {
            "Effect": "Allow",
            "Action": "ram:CreateServiceLinkedRole",
            "Resource": "*",
            "Condition": {
                "StringEquals": {
                    "ram:ServiceName": [
                        "backupencryption.rds.aliyuncs.com"
                    ]
                }
            }
        },
        {
            "Effect": "Allow",
            "Action": "bss:ModifyAgreementRecord",
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "bss:DescribeOrderList",
                "bss:DescribeOrderDetail",
                "bss:PayOrder",
                "bss:CancelOrder"
            ],
            "Resource": "*"
        }
    ]
}

Prepare the Terraform environment. You can use one of the following methods to use Terraform:
- Use Terraform in Terraform Explorer: Alibaba Cloud provides Terraform Explorer, an online runtime environment for Terraform. You can use Terraform after you log on to Terraform Explorer without the need to install Terraform. For more information, see Explorer. This method is suitable for scenarios in which you want to use and debug Terraform in a fast and convenient manner at no additional cost.
- Use Terraform in Cloud Shell: Terraform is preinstalled in Cloud Shell and identity credentials are configured. You can directly run Terraform commands in Cloud Shell. For more information, see Cloud Shell. This method is suitable for scenarios in which you want to use and debug Terraform in a fast and convenient manner at low cost.
- Install and configure Terraform on your on-premises machine: This method is suitable for scenarios in which network conditions are poor or a custom development environment is used. For more information, see Install and configure Terraform in the local PC.

Resources

Note

You are charged for specific resources. If you no longer require the resources, you must release or unsubscribe from the resources at the earliest opportunity.

alicloud_vpc: creates a virtual private cloud (VPC).
alicloud_vswitch: creates a vSwitch for a VPC.
alicloud_db_instance: creates an RDS instance.

Enable SQL Explorer and Audit

Create a working directory and a configuration file named main.tf in the directory. Copy the following code to the main.tf configuration file:

Create the required resources.

variable "region" {
  default = "cn-shenzhen"
}

variable "zone_id" {
  default = "cn-shenzhen-c"
}

variable "instance_type" {
  default = "pg.n2.2c.2m"
}

provider "alicloud" {
  region = var.region
}

# Create a VPC.
resource "alicloud_vpc" "main" {
  vpc_name   = "alicloud"
  cidr_block = "172.16.0.0/16"
}

# Create a vSwitch.
resource "alicloud_vswitch" "main" {
  vpc_id     = alicloud_vpc.main.id
  cidr_block = "172.16.192.0/20"
  zone_id    = var.zone_id
}

# Create an RDS instance.
resource "alicloud_db_instance" "instance" {
  engine               = "PostgreSQL"
  engine_version       = "13.0"
  instance_type        = var.instance_type
  instance_storage     = "30"
  instance_charge_type = "Postpaid"
  vswitch_id           = alicloud_vswitch.main.id
}

In the main.tf file, add the sql_collector_status configuration item to the resource "alicloud_db_instance" "instance" {} block.
```
...
resource "alicloud_db_instance" "instance" {
...
  sql_collector_status = "Enabled"
} 
```

Run the following command to initialize the runtime environment for Terraform:

terraform init

If the following information is returned, Terraform is initialized.

Initializing the backend...

Initializing provider plugins...
- Checking for available provider plugins...
- Downloading plugin for provider "alicloud" (hashicorp/alicloud) 1.90.1...
...

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.

Create an execution plan and preview the changes.
```
terraform plan
```

Run the following command to create the resources:

terraform apply

When prompted, enter yes and press Enter. Wait for the command to complete. The following output indicates that the operation was successful.

alicloud_db_instance.instance: Modifying... [id=pgm-****]
alicloud_db_instance.instance: Still modifying... [id=pgm-****, 10s elapsed]
alicloud_db_instance.instance: Still modifying... [id=pgm-****, 20s elapsed]
...
alicloud_db_instance.instance: Still modifying... [id=pgm-****, 3m0s elapsed]
alicloud_db_instance.instance: Modifications complete after 3m4s [id=pgm-****]

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

Verify the result.

Run the terraform show command

You can run the following command to view the status of SQL Explorer and Audit.

terraform show

# alicloud_db_instance.instance:
resource "alicloud_db_instance" "instance" {
    acl                        = "prefer"
    ca_type                    = "aliyun"
    client_ca_enabled          = 0
    client_crl_enabled         = 0
    connection_string          = "pgm-****.pg.rds.aliyuncs.com"
    connection_string_prefix   = "pgm-****"
    db_instance_storage_type   = "cloud_essd"
    db_time_zone               = "Asia/Shanghai"
    deletion_protection        = false
    engine                     = "PostgreSQL"
    engine_version             = "13.0"
    force_restart              = false
    ha_config                  = "Manual"
    id                         = "pgm-****"
    instance_charge_type       = "Postpaid"
    instance_name              = "terraformtest"
    instance_storage           = 50
    instance_type              = "pg.n2.2c.2m"
    maintain_time              = "05:00Z-06:00Z"
    manual_ha_time             = "2022-09-30T09:00:00Z"
    monitoring_period          = 300
    period                     = 0
    port                       = "5432"
    private_ip_address         = "192.168.XX.XX"
    replication_acl            = "prefer"
    resource_group_id          = "rg-****"
    security_group_id          = "sg-****"
    security_group_ids         = [
        "sg-bp1h9iqx6mxan5tcouwc",
    ]
    security_ip_mode           = "normal"
    security_ips               = [
        "0.0.0.0/0",
    ]
    server_cert                = <<-EOT
        -----BEGIN CERTIFICATE-----
        MIIE7jCCA9****fM4ALgBJ2
        N9xwKlPQ65q/kux0yErtwhAD
        -----END CERTIFICATE-----
    EOT
    server_key                 = <<-EOT
        -----BEGIN RSA PRIVATE KEY-----
        MIIJKQIBAA****T4Y1K34yE+e+VAdGp
        -----END RSA PRIVATE KEY-----
    EOT
    sql_collector_config_value = 30
    sql_collector_status       = "Enabled"
    ssl_action                 = "Open"
    ssl_status                 = "0"
    storage_auto_scale         = "Enable"
    storage_threshold          = 30
    storage_upper_bound        = 100
    target_minor_version       = "rds_postgres_1300_20220830"
    tcp_connection_type        = "SHORT"
    vpc_id                     = "vpc-****"
    vswitch_id                 = "vsw-****"
    zone_id                    = "cn-hangzhou-j"

    pg_hba_conf {
        address     = "127.0.0.1"
        database    = "all"
        method      = "md5"
        priority_id = 1
        type        = "host"
        user        = "all"
    }
}

Log on to the ApsaraDB RDS console

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the navigation pane on the left, click Autonomy Service > SQL Explorer and Audit to view the status of SQL Explorer and Audit.
The feature is enabled if its page is displayed and you can use its functions.

Disable SQL Explorer and Audit

In the main.tf file, modify the sql_collector_status configuration item in the resource "alicloud_db_instance" "instance" {} block.
```
...
resource "alicloud_db_instance" "instance" {
...
  sql_collector_status = "Disabled"
}               
```

Run the following command:

terraform apply

When prompted, enter yes and press Enter. Wait for the command to complete. The following output indicates that the operation was successful.

alicloud_db_instance.instance: Modifying... [id=pgm-****]
alicloud_db_instance.instance: Modifications complete after 9s [id=pgm-****]

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

Verify the result.

Run the terraform show command

You can run the following command to check the status of SQL Explorer and Audit.

terraform show

# alicloud_db_instance.instance:
resource "alicloud_db_instance" "instance" {
    client_ca_enabled          = 0
    client_crl_enabled         = 0
    connection_string          = "pgm-****.pg.rds.aliyuncs.com"
    connection_string_prefix   = "pgm-***"
    db_instance_storage_type   = "cloud_essd"
    db_time_zone               = "Asia/Shanghai"
    deletion_protection        = false
    engine                     = "PostgreSQL"
    engine_version             = "13.0"
    force_restart              = false
    ha_config                  = "Auto"
    id                         = "pgm-****"
    instance_charge_type       = "Postpaid"
    instance_storage           = 30
    instance_type              = "pg.n2.2c.2m"
    maintain_time              = "18:00Z-22:00Z"
    monitoring_period          = 300
    period                     = 0
    port                       = "5432"
    private_ip_address         = "172.16.XX.XX"
    resource_group_id          = "rg-****"
    security_group_ids         = []
    security_ip_mode           = "normal"
    security_ips               = [
        "127.0.0.1",
    ]
    sql_collector_config_value = 30
    sql_collector_status       = "Disabled"
    storage_threshold          = 0
    storage_upper_bound        = 0
    target_minor_version       = "rds_postgres_1300_20220730"
    tcp_connection_type        = "LONG"
    vpc_id                     = "vpc-****"
    vswitch_id                 = "vsw-****"
    zone_id                    = "cn-hangzhou-j"
}

# alicloud_vpc.main:
resource "alicloud_vpc" "main" {
    cidr_block            = "172.16.0.0/16"
    id                    = "vpc-****"
    name                  = "alicloud"
    resource_group_id     = "rg-****"
    route_table_id        = "vtb-****"
    router_id             = "vrt-****"
    router_table_id       = "vtb-****"
    secondary_cidr_blocks = []
    status                = "Available"
    vpc_name              = "alicloud"
}

# alicloud_vswitch.main:
resource "alicloud_vswitch" "main" {
    availability_zone = "cn-hangzhou-j"
    cidr_block        = "172.16.192.0/20"
    id                = "vsw-****"
    status            = "Available"
    vpc_id            = "vpc-****"
    zone_id           = "cn-hangzhou-j"
}

Log on to the ApsaraDB RDS console

Log on to the ApsaraDB RDS console and go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the instance ID.
In the navigation pane on the left, click Autonomy Service > SQL Explorer and Audit to view the status of SQL Explorer and Audit.
The feature is disabled if the console prompts you to enable it.

Clear resources

If you no longer need the resources that were created by Terraform, run the following command to release them. For more information about the terraform destroy command, see Common commands.

terraform destroy

Complete sample code

Note

You can run the sample code in this topic with a few clicks. For more information, visit Terraform Explorer.

Sample code

variable "region" {
  default = "cn-shenzhen"
}

variable "zone_id" {
  default = "cn-shenzhen-c"
}

variable "instance_type" {
  default = "pg.n2.2c.2m"
}

variable "target_minor_version" {
  default = "rds_postgres_1300_20240830"
}

provider "alicloud" {
  region = var.region
}

# Create a VPC
resource "alicloud_vpc" "main" {
  vpc_name   = "alicloud"
  cidr_block = "172.16.0.0/16"
}

# Create a vSwitch
resource "alicloud_vswitch" "main" {
  vpc_id     = alicloud_vpc.main.id
  cidr_block = "172.16.192.0/20"
  zone_id    = var.zone_id
}

# Create an ApsaraDB RDS for PostgreSQL instance
resource "alicloud_db_instance" "instance" {
  engine                 = "PostgreSQL"
  engine_version         = "13.0"
  instance_type          = var.instance_type
  instance_storage       = "30"
  instance_charge_type   = "Postpaid"
  vswitch_id             = alicloud_vswitch.main.id
  # Enable SQL Explorer and Audit (Add the following code after the ApsaraDB RDS for PostgreSQL instance is created)
  sql_collector_status   = "Enabled"
  # Disable SQL Explorer and Audit
  # sql_collector_status = "Disabled"
}

If you want to view more complete examples, visit the directory of the corresponding service on the More Complete Examples page.

References

For more information about Terraform, see What is Terraform?
You can also enable or disable SQL Explorer and Audit in the ApsaraDB RDS console. For more information, see SQL Explorer and Audit.
Terraform is available as a managed service in Resource Orchestration Service (ROS). You can create Terraform templates to define ApsaraDB RDS resources, specify resource parameters, and configure dependency relationships for resources. For more information, see Create a Terraform template.