Allow for custom resource ID's in `postgressql_grant`

[STV-MFJ]

Prefacing this with: I'm new to terraform/grunt and have no clue if there is a better way to do this or if a custom id is something that can even be exposed for a module like this

Terraform Version

 ❯ terraform -v
Terraform v1.3.6
on darwin_arm64
+ provider registry.terraform.io/cyrilgdn/postgresql v1.18.0
+ provider registry.terraform.io/hashicorp/aws v4.51.0
+ provider registry.terraform.io/hashicorp/helm v2.8.0
+ provider registry.terraform.io/hashicorp/kubernetes v2.16.1
+ provider registry.terraform.io/hashicorp/random v3.4.3
+ provider registry.terraform.io/integrations/github v5.15.0

 ❯ terragrunt -v
terragrunt version v0.42.5

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_grant

Terraform Configuration Files

#main.tf
locals{
  users = flatten([
    for user, user_config in var.additional_postgres_users: {
      role = user
      password = user_config.passwd
      roles = compact([for item in flatten([for key in values(user_config.access): keys(key)]): 
              replace(item,"public","")
              ])
    }
  ])
  access_schema = flatten([
    for role, role_config in var.additional_postgres_users: [
      for database, db_access in role_config.access: [
        for schema, schema_access in db_access: [
          for object_type, access in schema_access: {
            name = join("-", [role,database,schema,object_type])
            role = role
            schema = schema
            object_type = object_type
            access = access
            database = database
          } if object_type == "schema"
        ]
      ]
    ]
  ])
  access_tables = flatten([
    for role, role_config in var.additional_postgres_users: [
      for database, db_access in role_config.access: [
        for schema, schema_access in db_access: [
          for object_type, access in schema_access: [
            for permission, tables in transpose(access): {
              name = join("-", [role,database,schema,object_type,lower(permission)])
              role = role
              schema = schema
              object_type = object_type
              objects = compact(tables)
              access = [upper("${permission}")]
              database = database
            }
          ] if object_type == "table" && access != null 
        ]
      ]
    ]
  ])
}

provider "postgresql" {
  host      = var.domain
  database  = var.db
  username  = var.user
  password  = var.pass
  superuser = false
}

resource "postgresql_role" "role" {
  name     = var.role_name
  login    = true
  password = var.role_pass
}

resource "postgresql_database" "db" {
  count = var.new_db != null ? 1 : 0
  name  = var.new_db
  owner = postgresql_role.role.name
}

# Create additional db users (secrets.tfvars map)
resource "postgresql_role" "application_role" {
  for_each           = {for user in local.users: user.role => user}
  name               = each.key
  password           = each.value.password
  login              = true
  encrypted_password = true
  roles              = each.value.roles
}

resource "postgresql_grant" "schema_access" {
  for_each    = {for role in local.access_schema: role.name => role}
  database    = each.value.database
  role        = each.value.role
  schema      = each.value.schema
  object_type = each.value.object_type
  privileges  = each.value.access
}

resource "postgresql_grant" "table_permissions" {
  for_each    = {for role in local.access_tables: role.name => role}
  database    = each.value.database
  role        = each.value.role
  schema      = each.value.schema
  object_type = each.value.object_type
  objects     = each.value.objects
  privileges  = each.value.access
}

resource "postgresql_database" "additional_postgresql_dbs" {
  for_each = var.additional_postgresql_dbs
  name     = each.key
  owner    = each.value
}

# terragrunt.hcl
dependency "rds" {
  config_path = "../aurora"
}
include {
  path = "${find_in_parent_folders()}"
}
terraform {
  source = "../../../../modules/postgres-db"
}

inputs = {
  domain    = dependency.rds.outputs.domain
  db        = dependency.rds.outputs.db_name
  user      = dependency.rds.outputs.db_username
  role_name = "datastore"
  new_db    = "datastore"
}

Actual/Expected Behavior

expected per table granularity with grants but the id's that get generated are cut off at the object_type so a subsequent run generates something like: i.e.

# postgresql_grant.table_permissions["user.name-database-schema-select"] will be updated in-place
  ~ resource "postgresql_grant" "table_permissions" {
        id                = "user.name_database_schema_table"
      ~ privileges        = [
          - "INSERT",
          + "SELECT",
        ]
        # (5 unchanged attributes hidden)
    }

if the id matched the terragrunt iterable user.name-database-schema-select or was generated as user.name_database_schema_table_select this would generate a unique id with update permissions with a list of tables for which the user has that permission

ideally letting us set our own resource id would allow for transposition of this i.e. user.name_database_schema_table_tablename

Steps to Reproduce

a secrets.tfvars with something like:

pass = "database password"
role_pass = "main schema role password"

additional_postgres_users = {
   "first.last" = {
     passwd = "role passwd",
     access = {
       "database1" = {
         "schema1" = {
           schema = ["USAGE"]
           table = {
             "table1" = ["SELECT"]
           }
         }
       }
     }
   },
 }
}

terragrunt apply

Important Factoids

this is interfacing with auroradb rds cluster in aws