add graceful handling for missing databases in postgresql_schemas data source

[luis-alen]

Description:
Hello, and thank you for maintaining this provider! I’ve encountered a challenge when using the postgresql_schemas data source to retrieve schemas dynamically, especially in situations where a database might not yet exist during the Terraform apply phase.

Problem:
Currently, if the specified database does not exist, the postgresql_schemas data source throws a connection error and fails the Terraform plan or apply. This requires a workaround, such as a two-step apply or using the -target flag to create the database first, which adds complexity to the configuration and automation pipelines. The error occurs because dataSourcePostgreSQLSchemasRead attempts to connect to the database directly and doesn’t handle missing databases gracefully.

Workaround Attempt:
To address this, I tried adding a depends_on parameter to the postgresql_schemas data source to ensure the database resource was created first. However, this approach still led to issues because I also try to create grants dynamically for each database + schema and terraform requires for_each values to be known at plan time.

Since the schemas for each database are not known until after the database creation, Terraform cannot determine the values of for_each during the plan phase. This results in an error, as Terraform requires the for_each values to be fully known before proceeding with the apply.

Example:

locals {
  all_databases = ["db1", "db2"]
  fetch_schemas = true
}

# Step 1: Create databases
resource "postgresql_database" "databases" {
  for_each = toset(local.all_databases)
  name     = each.key
}

# Step 2: Fetch schemas for each database
data "postgresql_schemas" "db_schemas" {
  for_each   = local.fetch_schemas ? toset(local.all_databases) : []
  database   = each.key
  depends_on = [postgresql_database.databases]
}

# Step 3: Grant read-only access based on schemas
locals {
  flattened_schemas_by_db = {
    for item in flatten([
      for db_instance in local.all_databases : [
        for schema in try(data.postgresql_schemas[db_instance].schemas, []) : {
          database = db_instance
          schema   = schema
        }
      ]
    ]) : "${item.database}_${item.schema}" => item
  }
}

resource "postgresql_role" "read_only_role" {
  name  = "read_only_role"
}

resource "postgresql_grant" "read_only_grant" {
  for_each    = local.flattened_schemas_by_db
  database    = each.value["database"]
  role        = "read_only_role"
  schema      = each.value["schema"]
  object_type = "table"
  privileges  = ["SELECT"]
}

Suggested Solution:
It would be helpful if the data source could gracefully handle missing databases by returning an empty schema list instead of failing. This could be done by catching connection errors specifically related to a missing database. If the database doesn’t exist, the provider could return an empty list for schemas, allowing Terraform to proceed without needing multiple apply steps.

Proposed Implementation:
In dataSourcePostgreSQLSchemasRead, check the error returned from startTransaction for cases where the database doesn’t exist. If the error indicates a missing database, the function could return an empty list for schemas rather than an error.

This change would make the provider more flexible and simplify configurations where database creation and schema grants need to happen in a single apply step.

Thank you for considering this suggestion, and please let me know if I can provide any further information or testing assistance.