Feature request: db_database for PostgreSQL

[xinyifly]

Docs states that:

NOTE: This resource does not support creating 'PostgreSQL' database and you can use Postgresql Provider to do it.

RDS usually has an intranet only access, making it inconvenient to use PostgreSQL Provider .

Currently using db_database for PostgreSQL would cause db to recreate repeatedly, because of the uppercase of UTF8 :

Terraform will perform the following actions:

  # alicloud_db_database.example must be replaced
-/+ resource "alicloud_db_database" "example" {
      ~ character_set = "UTF8" -> "utf8" # forces replacement
      ~ id            = "pgm-abcdefghijklmnop:example" -> (known after apply)
        instance_id   = "pgm-abcdefghijklmnop"
        name          = "example"
    }

The db_instance is using:

  engine           = "PostgreSQL"
  engine_version   = "10.0"

[xiaozhu36]

HI @xinyifly What provider version is your used?

[xinyifly]

HI @xinyifly What provider version is your used?

# terraform --version
Terraform v0.12.16
+ provider.alicloud v1.62.0

[xiaozhu36]

HI @xinyifly Actually, this un-support issue has been fixed, but the docs has not updated yet. You can have a try. In addition, the PR #1916 has removed the note.

[xinyifly]

Hi, @xiaozhu36 After updated to v1.62.2

# terraform --version
Terraform v0.12.16
+ provider.alicloud v1.62.2

I'm still getting this console message every time I try terraform plan after first apply if I didn't specify character_set or with character_set = "utf8" :

------------------------------------------------------------------------
An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
-/+ destroy and then create replacement

Terraform will perform the following actions:

  # alicloud_db_database.this must be replaced
-/+ resource "alicloud_db_database" "this" {
      ~ character_set = "UTF8" -> "utf8" # forces replacement
      ~ id            = "pgm-abcdefghijklmnop:example" -> (known after apply)
        instance_id   = "pgm-abcdefghijklmnop"
        name          = "example"
    }

Plan: 1 to add, 0 to change, 1 to destroy.

------------------------------------------------------------------------

If I specify character_set = "UTF8" :

# terraform plan

Error: "character_set" must contain a valid string value should be in array []string{"utf8", "gbk", "latin1", "utf8mb4", "Mohawk_100_BIN", "Chinese_PRC_CI_AS", "Chinese_PRC_CS_AS", "SQL_Latin1_General_CP1_CI_AS", "SQL_Latin1_General_CP1_CS_AS", "Chinese_PRC_BIN"}, got "UTF8"

  on rds.tf line 27, in resource "alicloud_db_database" "this":
  27: resource "alicloud_db_database" "this" {

Currently I have to use lifecycle to ignore character_set

  lifecycle {
    ignore_changes = [character_set]
  }

BTW, is there an option that equals to Authorized Account in RDS database creation page? That possibly equals to this SQL statement:

CREATE DATABASE dbname OWNER rolename;

Which will lead to this database owner query statement to my specified account rather than aurora :

SELECT u.usename 
 FROM pg_database d
  JOIN pg_user u ON (d.datdba = u.usesysid)
 WHERE d.datname = (SELECT current_database());

Output:

+-------------------+
| USENAME           |
+-------------------+
| aurora            |
+-------------------+

I can get my specified Authorized Account name from the query if I create the database in web control panel manually.

[xinyifly]

After created the database, my attempt to assign privileges ran into issue:

Terraform will perform the following actions:

  # alicloud_db_account_privilege.this will be created
  + resource "alicloud_db_account_privilege" "this" {
      + account_name = "abcdefg"
      + db_names     = [
          + "abcdefg",
        ]
      + id           = (known after apply)
      + instance_id  = "pgm-abcdefghijklmnop"
      + privilege    = "ReadWrite"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

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

alicloud_db_account_privilege.this: Creating...

Error: [ERROR] terraform-provider-alicloud/alicloud/resource_alicloud_db_account_privilege.go:78:
[ERROR] terraform-provider-alicloud/alicloud/service_alicloud_rds.go:406: Resource pgm-abcdefghijklmnop:abcdefg:ReadWrite GrantAccountPrivilege Failed!!! [SDK alibaba-cloud-sdk-go ERROR]:
SDK.ServerError
ErrorCode: InvalidDBInfo.Malformed
Recommend: 
RequestId: 8280F5B0-009D-4EF9-8181-18D7674CBABE
Message: The specified parameter DBInfo is not valid or db not exist.

  on rds.tf line 37, in resource "alicloud_db_account_privilege" "this":
  37: resource "alicloud_db_account_privilege" "this" {

[xiaozhu36]

HI @xinyifly Let me check your scenario:

1. Create a RDS instance which engine is postgresql and version is 10.0
2. Create a database
3. Create a normal account
4. Grant the normal account to the database, but got an error "InvalidDBInfo.Malformed" Please let me know whether there missing more details.

[xinyifly]

Hi @xiaozhu36 Please try this as main.tf :

provider "alicloud" {
  version = "1.63.0"
  region  = "cn-shanghai"
}

resource "alicloud_vpc" "this" {
  cidr_block = "10.0.0.0/8"
}

resource "alicloud_vswitch" "this" {
  vpc_id = alicloud_vpc.this.id

  cidr_block        = "10.0.0.0/16"
  availability_zone = "cn-shanghai-f"
}

resource "alicloud_db_instance" "this" {
  vswitch_id = alicloud_vswitch.this.id

  engine           = "PostgreSQL"
  engine_version   = "10.0"
  instance_storage = 20
  instance_type    = "pg.n2.medium.1"
}

resource "alicloud_db_database" "this" {
  instance_id = alicloud_db_instance.this.id
  name        = "example"
}

After terraform apply finished, try terraform plan again.

[xiaozhu36]

HI @xinyifly I reproduced the issue character_set = "UTF8" -> "utf8" # forces replacement. I have confirmed that the postgresql supports UTF8 and mysql support utf8 and you need to change your template. The character_set limetation has been removed from 1.63.0 and added more details in https://www.terraform.io/docs/providers/alicloud/r/db_database.html#character_set. Please check it.

[xinyifly]

Hi @xiaozhu36 I just confirmed the usage of character_set = "UTF8" for PostgreSQL in v1.63.0 , it works now, thank you!

[xinyifly]

Hi @xiaozhu36 , I just confirmed alicloud_db_account_privilege in v1.66.0 worked as expected as well, the permission created correctly. Except that the operation timed out:

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # alicloud_db_account_privilege.example will be created
  + resource "alicloud_db_account_privilege" "example" {
      + account_name = "example"
      + db_names     = [
          + "example",
        ]
      + id           = (known after apply)
      + instance_id  = "pgm-1234567890abcdef"
      + privilege    = "DBOwner"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

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

alicloud_db_account_privilege.example: Creating...
alicloud_db_account_privilege.example: Still creating... [10s elapsed]
alicloud_db_account_privilege.example: Still creating... [20s elapsed]
alicloud_db_account_privilege.example: Still creating... [30s elapsed]
alicloud_db_account_privilege.example: Still creating... [40s elapsed]
alicloud_db_account_privilege.example: Still creating... [50s elapsed]
alicloud_db_account_privilege.example: Still creating... [1m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [1m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [1m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [1m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [1m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [1m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [2m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [2m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [2m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [2m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [2m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [2m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [3m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [3m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [3m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [3m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [3m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [3m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [4m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [4m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [4m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [4m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [4m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [4m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [5m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [5m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [5m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [5m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [5m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [5m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [6m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [6m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [6m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [6m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [6m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [6m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [7m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [7m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [7m20s elapsed]
alicloud_db_account_privilege.example: Still creating... [7m30s elapsed]
alicloud_db_account_privilege.example: Still creating... [7m40s elapsed]
alicloud_db_account_privilege.example: Still creating... [7m50s elapsed]
alicloud_db_account_privilege.example: Still creating... [8m0s elapsed]
alicloud_db_account_privilege.example: Still creating... [8m10s elapsed]
alicloud_db_account_privilege.example: Still creating... [8m20s elapsed]

Error: [ERROR] terraform-provider-alicloud/alicloud/resource_alicloud_db_account_privilege.go:80:
[ERROR] terraform-provider-alicloud/alicloud/service_alicloud_rds.go:410:
[ERROR] terraform-provider-alicloud/alicloud/service_alicloud_rds.go:884: Resource pgm-1234567890abcdef:example:DBOwner WaitForAccountPrivilege Timeout In 500 Seconds. Got:  Expected: example !!! [Provider ERROR]:
<nil cause>

  on rds.tf line 37, in resource "alicloud_db_account_privilege" "example":
  37: resource "alicloud_db_account_privilege" "example" {

Then I tried terraform refresh then terraform apply again, it tips that:

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
-/+ destroy and then create replacement

Terraform will perform the following actions:

  # alicloud_db_account_privilege.linoyun is tainted, so must be replaced
-/+ resource "alicloud_db_account_privilege" "linoyun" {
        account_name = "linoyun"
      ~ db_names     = [
          + "linoyun",
        ]
      ~ id           = "pgm-uf61zf6z4hg5ze67:linoyun:DBOwner" -> (known after apply)
        instance_id  = "pgm-uf61zf6z4hg5ze67"
        privilege    = "DBOwner"
    }

Plan: 1 to add, 0 to change, 1 to destroy.

And timed out again, seems the query interface didn't match well.

# terraform --version
Terraform v0.12.17
+ provider.alicloud v1.66.0

[xinyifly]

# aliyun rds DescribeAccounts --DBInstanceId pgm-uf61zf6z4hg5ze67
{
    "Accounts": {
        "DBInstanceAccount": [
            {
                "DatabasePrivileges": {
                    "DatabasePrivilege": []
                },
                "AccountStatus": "Available",
                "AccountDescription": "",
                "DBInstanceId": "pgm-uf61zf6z4hg5ze67",
                "AccountName": "linoyun",
                "AccountType": "Normal"
            }
        ]
    },
    "SystemAdminAccountFirstActivationTime": "",
    "RequestId": "3B295FCC-C591-41E4-95EE-7241FB86D697",
    "SystemAdminAccountStatus": "False"
}

It seems that the API returned an empty DatabasePrivileges list.