Delay Deletion of Connection Details

[bradkwadsworth-mw]

What happened?

I'm currently writing a composition that creates a GCP Cloud SQL database instance, users, databases, and connection details. I'm also creating database grants using provider-sql. After the database instance is created, a providerconfig using the connection details secret is used for the sql-provider credentials. This works as expected for creation, but on deletion of the XRD the connection detail secret is deleted before the grant resources that depend on it are removed. This leaves the grant resources hanging and erroring because its providerconfig cannot find the required secret.

How can we reproduce it?

definition.yaml

apiVersion: apiextensions.crossplane.io/v1
kind: CompositeResourceDefinition
metadata:
  name: xdatabases.dbaas.idp.example.edu
spec:
  claimNames:
    categories:
      - crossplane
      - composition
      - database
    kind: DatabaseClaim
    plural: databaseclaims
  defaultCompositionRef:
    name: xdatabases-v1alpha1.dbaas.idp.example.edu
  defaultCompositionUpdatePolicy: Automatic
  group: dbaas.idp.example.edu
  names:
    categories:
      - crossplane
      - composition
      - database
    kind: XDatabase
    plural: xdatabases
  versions:
    - name: v1alpha1
      referenceable: true
      schema:
        openAPIV3Schema:
          type: object
          properties:
            spec:
              type: object
              required:
                - parameters
              properties:
                parameters:
                  type: object
                  required:
                    - name
                    - tenantCode
                    - engine
                    - rootPasswordSecret
                    - diskSize
                  properties:
                    name:
                      type: string
                      description: database instance name
                    tenantCode:
                      type: string
                      description: database instance tenant
                    deletionPolicy:
                      type: string
                      description: DeletionPolicy specifies what will happen to the underlying external when this managed resource is deleted - either "Delete" or "Orphan" the external resource.
                      default: Orphan
                      enum:
                        - Orphan
                        - Delete
                    engine:
                      type: string
                      description: The MySQL, PostgreSQL or SQL Server version to use. Supported values include POSTGRES_14, POSTGRES_15, SQLSERVER_2019_STANDARD, and SQLSERVER_2022_STANDARD . Database Version Policies includes an up-to-date reference of supported versions.
                      enum:
                        - POSTGRES_14
                        - POSTGRES_15
                        - SQLSERVER_2019_STANDARD
                        - SQLSERVER_2022_STANDARD
                    region:
                      type: string
                      description: database region location
                      default: us-central1
                    rootPasswordSecret:
                      type: object
                      required:
                        - key
                        - name
                      description: secret containing root password
                      properties:
                        key:
                          type: string
                        name:
                          type: string
                    diskSize:
                      type: number
                      description: database disk size
                    diskType:
                      type: string
                      description: database disk type, slow or fast
                      default: fast
                      enum:
                        - slow
                        - fast
                    authorizedNetworks:
                      type: array
                      description: list of authroized networks
                      items:
                        type: object
                        required:
                          - name
                          - cidr
                        properties:
                          name:
                            type: string
                          cidr:
                            type: string
                    publicAccess:
                      type: boolean
                      description: enable public access to the database
                    sslCert:
                      type: object
                      required:
                        - name
                      description: ssl certificate configuration
                      properties:
                        name:
                          type: string
                          description: common name for certificate
                        required:
                          type: boolean
                          description: create and force use of ssl certificate
                        deletionPolicy:
                          type: string
                          description: DeletionPolicy specifies what will happen to the underlying external when this managed resource is deleted - either "Delete" or "Orphan" the external resource.
                          default: Orphan
                          enum:
                            - Orphan
                            - Delete
                    preferredZone:
                      type: string
                      description: preferred availability zone for database
                    maintenance:
                      type: object
                      description: maintenance window
                      properties:
                        day:
                          type: number
                        hour:
                          type: number
                    machineType:
                      type: string
                      description: type of machine for database
                      default: db-custom-2-13312
                    labels:
                      type: object
                      description: labels to apply to database
                      additionalProperties:
                        type: string
                    usePublicIpConnection:
                      type: boolean
                      description: configure cluster using public ip
                    databases:
                      type: array
                      description: list of database to create in instance
                      items:
                        type: object
                        required:
                          - name
                        properties:
                          name:
                            type: string
                            description: database name
                          charset:
                            type: string
                          collation:
                            type: string
                          deletionPolicy:
                            type: string
                            description: DeletionPolicy specifies what will happen to the underlying external when this managed resource is deleted - either "Delete" or "Orphan" the external resource.
                            default: Orphan
                            enum:
                              - Orphan
                              - Delete
                          grants:
                            type: array
                            items:
                              type: object
                              required:
                                - user
                                - permissions
                              properties:
                                user:
                                  type: string
                                  description: database user name to apply permissions
                                permissions:
                                  type: array
                                  description: list of permissions to apply to user on database
                                  items:
                                    type: string
                    users:
                      type: array
                      description: list of database users
                      items:
                        type: object
                        required:
                          - name
                        properties:
                          name:
                            type: string
                            description: database user name
                          database:
                            type: string
                            description: Database to assign to user. Required for MSSQL
                          passwordSecret:
                            type: object
                            required:
                              - key
                              - name
                            description: secret containing password for BUILT_IN type users
                            properties:
                              key:
                                type: string
                              name:
                                type: string
                          type:
                            type: string
                            description: The user type. It determines the method to authenticate the user during login. The default is the database's built-in user type. Flags include "BUILT_IN", "CLOUD_IAM_USER", "CLOUD_IAM_GROUP" or "CLOUD_IAM_SERVICE_ACCOUNT".
                            default: BUILT_IN
                            enum:
                              - BUILT_IN
                              - CLOUD_IAM_USER
                              - CLOUD_IAM_GROUP
                              - CLOUD_IAM_SERVICE_ACCOUNT
                          deletionPolicy:
                            type: string
                            description: DeletionPolicy specifies what will happen to the underlying external when this managed resource is deleted - either "Delete" or "Orphan" the external resource.
                            default: Orphan
                            enum:
                              - Orphan
                              - Delete
            status:
              type: object
              properties:
                database:
                  type: object
                  description: Freeform field containing status information for database
                  x-kubernetes-preserve-unknown-fields: true
      served: true

composition.yaml

apiVersion: apiextensions.crossplane.io/v1
kind: Composition
metadata:
  labels:
    provider: gcp
  name: xdatabases-v1alpha1.dbaas.idp.example.edu
spec:
  compositeTypeRef:
    apiVersion: dbaas.idp.example.edu/v1alpha1
    kind: XDatabase
  mode: Pipeline
  pipeline:
    - functionRef:
        name: function-environment-configs
      input:
        apiVersion: environmentconfigs.fn.crossplane.io/v1beta1
        kind: Input
        spec:
          environmentConfigs:
            - type: Selector
              selector:
                matchLabels:
                  - key: tenant
                    type: FromCompositeFieldPath
                    valueFromFieldPath: spec.claimRef.namespace
                  - key: type
                    type: Value
                    value: tenant
      step: environmentConfigs
    - functionRef:
        name: function-kcl
      input:
        apiVersion: krm.kcl.dev/v1alpha1
        kind: KCLRun
        metadata:
          name: basic
        spec:
          source: "# Read the XR\nimport base64\n\noxr = option(\"params\").oxr\nocds = option(\"params\").ocds\nctx = option(\"params\").ctx\ncompLabels = {\n    \"database.dbaas.idp.example.edu/name\" = oxr.spec.claimRef?.name\n    \"database.dbaas.idp.example.edu/namespace\" = oxr.spec.claimRef?.namespace\n    \"database.dbaas.idp.example.edu/id\" = oxr.spec.parameters.name\n}\nlabels = {\n    **compLabels\n    **oxr.metadata.labels\n}\n# Contruct database instance\ninstance = {\n    apiVersion = \"sql.gcp.upbound.io/v1beta1\"\n    kind = \"DatabaseInstance\"\n    metadata.annotations: {\n        **oxr.metadata.annotations\n        \"krm.kcl.dev/composition-resource-name\" = \"dbinstance\"\n        \"crossplane.io/external-name\" = \"mcp-sql-{}-{}\".format(oxr.spec.parameters.tenantCode, oxr.spec.parameters.name)\n    }\n    metadata.labels: labels\n    spec.deletionPolicy = oxr.spec.parameters.deletionPolicy or \"Orphan\"\n    spec.providerConfigRef.name = \"{}-services\".format(oxr.spec.claimRef?.namespace)\n    spec.forProvider = {\n        databaseVersion = oxr.spec.parameters.engine\n        deletionProtection = False if oxr.spec.parameters.deletionPolicy == \"Delete\" else True\n        region = oxr.spec.parameters.region\n        rootPasswordSecretRef = {\n            key = oxr.spec.parameters.rootPasswordSecret?.key\n            name = oxr.spec.parameters.rootPasswordSecret?.name\n            namespace = oxr.spec.claimRef?.namespace\n        }\n        settings = [{\n            activationPolicy = \"ALWAYS\"\n            availabilityType = \"REGIONAL\"\n            backupConfiguration = [{\n                backupRetentionSettings = [{\n                    retainedBackups = oxr.spec.parameters.backup?.retain or 7\n                    retentionUnit = \"COUNT\"\n                }]\n                if oxr.spec.parameters.engine.startswith(\"MYSQL\"):\n                    binaryLogEnabled = True\n                \n                enabled = True\n                location = ctx[\"apiextensions.crossplane.io/environment\"]?.database?.backup?.location or \"us-east1\"\n                if oxr.spec.parameters.engine.startswith(\"POSTGRES\") or oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n                    pointInTimeRecoveryEnabled = True\n                \n                startTime = \"03:00\"\n                transactionLogRetentionDays = 7\n            }]\n            databaseFlags = []\n            if oxr.spec.parameters.engine.startswith(\"POSTGRES\"):\n                databaseFlags += [{\n                    name = \"cloudsql.iam_authentication\"\n                    value = \"on\"\n                }]\n            \n            deletionProtectionEnabled = False if oxr.spec.parameters.deletionPolicy == \"Delete\" else True\n            diskAutoresize = True\n            diskAutoresizeLimit = 500\n            diskSize = oxr.spec.parameters.diskSize\n            if oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n                diskType = \"PD_SSD\"\n            else:\n                diskType = \"PD_SSD\" if oxr.spec.parameters.diskType == \"fast\" else \"PD_HDD\"\n            ipConfiguration = [{\n                authorizedNetworks = [{\n                    name = net.name\n                    value = net.cidr\n                } for net in oxr.spec.parameters.authorizedNetworks]\n                ipv4Enabled = True if oxr.spec.parameters.publicAccess else False\n                privateNetwork = ctx[\"apiextensions.crossplane.io/environment\"]?.database?.privateNetwork\n                privateNetworkSelector.matchLabels = ctx[\"apiextensions.crossplane.io/environment\"]?.database?.privateNetworkSelectorLabels\n                pscConfig = [{\n                    pscEnabled = True if ctx[\"apiextensions.crossplane.io/environment\"]?.database?.psc?.enabled else False\n                    allowedConsumerProjects = ctx[\"apiextensions.crossplane.io/environment\"]?.database?.psc?.allowedProjects or []\n                }]\n                requireSsl = oxr.spec.parameters.sslCert?.required\n                if oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n                    sslMode = \"ENCRYPTED_ONLY\" if oxr.spec.parameters.sslCert?.required else \"ALLOW_UNENCRYPTED_AND_ENCRYPTED\"\n                \n                if oxr.spec.parameters.engine.startswith(\"POSTGRES\"):\n                    sslMode = \"TRUSTED_CLIENT_CERTIFICATE_REQUIRED\" if oxr.spec.parameters.sslCert?.required else \"ALLOW_UNENCRYPTED_AND_ENCRYPTED\"\n                \n            }]\n            locationPreference = [{zone = oxr.spec.parameters.preferredZone}]\n            maintenanceWindow = [{\n                day = oxr.spec.parameters.maintenance?.day or 6\n                hour = oxr.spec.parameters.maintenance?.hour or 3\n                updateTrack = \"stable\"\n            }]\n            tier = oxr.spec.parameters.machineType\n            userLabels = oxr.spec.parameters.labels\n        }]\n    }\n    spec.writeConnectionSecretToRef = {\n        name = \"{}-{}\".format(oxr.spec.claimRef?.name, oxr.metadata.uid)\n        namespace = oxr.spec.claimRef?.namespace\n    }\n}\ninstanceStat = {\n    gcpcloudSqlInstance = {\n        id = ocds.dbinstance?.Resource?.status?.atProvider?.id\n        connectionName = ocds.dbinstance?.Resource?.status?.atProvider?.connectionName\n        privateIpAddress = ocds.dbinstance?.Resource?.status?.atProvider?.privateIpAddress\n        publicIpAddress = ocds.dbinstance?.Resource?.status?.atProvider?.publicIpAddress\n        region = ocds.dbinstance?.Resource?.status?.atProvider?.region\n        selfLink = ocds.dbinstance?.Resource?.status?.atProvider?.selfLink\n        serviceAccountEmailAddress = ocds.dbinstance?.Resource?.status?.atProvider?.serviceAccountEmailAddress\n        conditions = ocds.dbinstance?.Resource?.status?.conditions\n    }\n}\n# Construct ProviderConfig\nschema ProviderConfigType:\n    dbType: \"mssql\" | \"postgresql\"\n    source: \"MSSQLConnectionSecret\" | \"PostgreSQLConnectionSecret\"\n\ndbProviderConfig = lambda a: ProviderConfigType {\n    {\n        apiVersion = \"{}.sql.crossplane.io/v1alpha1\".format(a.dbType)\n        kind = \"ProviderConfig\"\n        metadata.name = \"{}-{}\".format(oxr.spec.claimRef?.namespace, oxr.spec.claimRef?.name)\n        metadata.annotations: {\n            **oxr.metadata.annotations\n            \"krm.kcl.dev/composition-resource-name\" = \"providerconfig\"\n            \"krm.kcl.dev/ready\": \"True\"\n        }\n        metadata.labels: labels\n        spec.credentials = {\n            connectionSecretRef = {\n                name = oxr.metadata.uid\n                namespace = \"crossplane-system\"\n            }\n            source = a.source\n        }\n        if a.dbType == \"postgresql\":\n            spec.sslMode = \"require\" if oxr.spec.parameters.sslCert?.required else \"disable\"\n        \n    }\n}\n# Construct SSL cert\nsslCert = {\n    apiVersion = \"sql.gcp.upbound.io/v1beta1\"\n    kind = \"SSLCert\"\n    metadata.annotations: {\n        **oxr.metadata.annotations\n        \"krm.kcl.dev/composition-resource-name\" = \"sslCert\"\n    }\n    metadata.labels: labels\n    spec.deletionPolicy = oxr.spec.parameters.sslCert?.deletionPolicy or \"Orphan\"\n    spec.providerConfigRef.name = \"{}-services\".format(oxr.spec.claimRef?.namespace)\n    spec.forProvider = {\n        commonName = oxr.spec.parameters.sslCert?.name\n        instanceSelector.matchControllerRef = True\n    }\n    spec.publishConnectionDetailsTo.name = \"{}-{}-sslcert\".format(oxr.spec.claimRef?.namespace, oxr.spec.claimRef?.name)\n}\nsslCertStat = {\n    gcpcloudSqlSslCert = {\n        conditions = ocds.sslCert?.Resource?.status?.conditions\n        certSerialNumber = ocds.sslCert?.Resource?.status?.atProvider?.certSerialNumber\n        expirationTime = ocds.sslCert?.Resource?.status?.atProvider?.expirationTime\n        id = ocds.sslCert?.Resource?.status?.atProvider?.id\n        sha1Fingerprint = ocds.sslCert?.Resource?.status?.atProvider?.sha1Fingerprint\n    }\n}\n# Construct databases\n_databases = oxr.spec.parameters.databases or []\ndatabases = [{\n    apiVersion = \"sql.gcp.upbound.io/v1beta1\"\n    kind = \"Database\"\n    metadata.annotations: {\n        **oxr.metadata.annotations\n        \"krm.kcl.dev/composition-resource-name\" = \"db-{}\".format(db.name)\n        \"crossplane.io/external-name\" = db.name\n    }\n    metadata.labels: labels\n    spec.deletionPolicy = db.deletionPolicy or \"Orphan\"\n    spec.providerConfigRef.name = \"{}-services\".format(oxr.spec.claimRef?.namespace)\n    spec.forProvider = {\n        charset = db.charset\n        collation = db.collation\n        deletionPolicy = \"ABANDON\" if db.deletionPolicy == \"Orphan\" else \"DELETE\"\n        instanceSelector.matchControllerRef = True\n    }\n} for db in _databases]\ndbStats = {gcpcloudSqlDatabases = [{\n    conditions = ocds[db.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.conditions\n    id = ocds[db.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.atProvider?.id\n    selfLink = ocds[db.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.atProvider?.selfLink\n} for db in databases]}\n# Construct Database Users\n_dbUsers = oxr.spec.parameters.users or []\ndbUsers = [{\n    _username = user.name.rstrip(\".gserviceaccount.com \") if user.type == \"CLOUD_IAM_SERVICE_ACCOUNT\" else user.name\n    apiVersion = \"sql.gcp.upbound.io/v1beta1\"\n    kind = \"User\"\n    metadata.annotations: {\n        **oxr.metadata.annotations\n        \"krm.kcl.dev/composition-resource-name\" = \"dbUser-{}\".format(_username)\n        \"crossplane.io/external-name\" = _username\n    }\n    metadata.labels: labels\n    spec.deletionPolicy = user.deletionPolicy or \"Orphan\"\n    spec.providerConfigRef.name = \"{}-services\".format(oxr.spec.claimRef?.namespace)\n    spec.forProvider = {\n        deletionPolicy = \"ABANDON\" if user.deletionPolicy == \"Orphan\" else \"DELETE\"\n        instanceSelector.matchControllerRef = True\n        type = user.type or \"BUILT_IN\"\n        if type == \"BUILT_IN\":\n            passwordSecretRef = {\n                key = user.passwordSecret?.key\n                name = user.passwordSecret?.name\n                namespace = oxr.spec.claimRef?.namespace\n            }\n        \n    }\n} for user in _dbUsers]\ndbUserStats = {gcpcloudSqlUsers = [{\n    conditions = ocds[user.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.conditions\n    id = ocds[user.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.atProvider?.id\n} for user in dbUsers]}\nmssqlUsers = [{\n    apiVersion = \"mssql.sql.crossplane.io/v1alpha1\"\n    kind = \"User\"\n    metadata.annotations: {\n        **oxr.metadata.annotations\n        \"krm.kcl.dev/composition-resource-name\" = \"mssqlUser-{}-{}\".format(user.database, user.name)\n        \"crossplane.io/external-name\" = user.name\n    }\n    metadata.labels: labels\n    spec.deletionPolicy = user.deletionPolicy or \"Orphan\"\n    spec.providerConfigRef.name = \"{}-{}\".format(oxr.spec.claimRef?.namespace, oxr.spec.claimRef?.name)\n    spec.forProvider = {\n        database = user.database or \"nodb\"\n        passwordSecretRef = {\n            key = user.passwordSecret?.key\n            name = user.passwordSecret?.name\n            namespace = oxr.spec.claimRef?.namespace\n        }\n    }\n} for user in _dbUsers]\nmssqlUsages = [{\n    _resource = ocds[user.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.metadata?.name\n    apiVersion = \"apiextensions.crossplane.io/v1alpha1\"\n    kind = \"Usage\"\n    metadata.annotations: {\n        **oxr.metadata.annotations\n        \"krm.kcl.dev/composition-resource-name\" = \"mssqlUserUsage-{}-{}\".format(ocds.dbinstance?.Resource?.metadata?.name, _resource)\n    }\n    spec = {\n        replayDeletion = True\n        of = {\n            apiVersion = \"sql.gcp.upbound.io/v1beta2\"\n            kind = \"DatabaseInstance\"\n            resourceRef.name = ocds.dbinstance?.Resource?.metadata?.name\n        }\n        by = {\n            apiVersion = \"mssql.sql.crossplane.io/v1alpha1\"\n            kind = \"User\"\n            resourceRef.name = _resource\n        }\n    }\n} for user in mssqlUsers]\nmssqlUserStats = {mssqlUsers = [{conditions = ocds[user.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.conditions} for user in mssqlUsers]}\n# Construct grants\nschema Grant:\n    dbType: \"mssql\" | \"postgresql\"\n\n_dbGrants = [{\n    **_db\n    grants = _db.grants or []\n} for _db in _databases]\ndb_grants = lambda a: Grant {\n    [{\n        apiVersion = \"{}.sql.crossplane.io/v1alpha1\".format(a.dbType)\n        kind = \"Grant\"\n        metadata.annotations: {\n            **oxr.metadata.annotations\n            \"krm.kcl.dev/composition-resource-name\" = \"grant-{}-{}-{}-{}\".format(oxr.metadata.name, _db.name, _grant.user, \"-\".join([i.replace(\" \", \"-\") for i in _grant.permissions]).lower())\n        }\n        metadata.labels: labels\n        spec.deletionPolicy = \"Delete\"\n        spec.providerConfigRef.name = \"{}-{}\".format(oxr.spec.claimRef?.namespace, oxr.spec.claimRef?.name)\n        spec.forProvider = {\n            database = _db.name\n            if a.dbType == \"mssql\":\n                permissions = _grant.permissions\n                user = _grant.user\n            \n            if a.dbType == \"postgresql\":\n                privileges = _grant.permissions\n                role = _grant.user\n            \n        }\n    } for _db in _dbGrants for _grant in _db.grants]\n}\nif oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n    _providerConfig = dbProviderConfig(ProviderConfigType {dbType = \"mssql\", source = \"MSSQLConnectionSecret\"})\n    _grants = db_grants(Grant {dbType = \"mssql\"})\n\nif oxr.spec.parameters.engine.startswith(\"POSTGRES\"):\n    _providerConfig = dbProviderConfig(ProviderConfigType {dbType = \"postgresql\", source = \"PostgreSQLConnectionSecret\"})\n    _grants = db_grants(Grant {dbType = \"postgresql\"})\n\nproviderConfig = _providerConfig\ngrants = _grants\ngrantStats = {sqlGrant = [{conditions = ocds[grant.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.status?.conditions} for grant in grants]}\ngrant_usages = lambda a: Grant {\n    [{\n        _resource = ocds[grant.metadata.annotations[\"krm.kcl.dev/composition-resource-name\"]]?.Resource?.metadata?.name\n        apiVersion = \"apiextensions.crossplane.io/v1alpha1\"\n        kind = \"Usage\"\n        metadata.annotations: {\n            **oxr.metadata.annotations\n            \"krm.kcl.dev/composition-resource-name\" = \"grantUsage-{}-{}\".format(ocds.dbinstance?.Resource?.metadata?.name, _resource)\n        }\n        spec = {\n            replayDeletion = True\n            of = {\n                apiVersion = \"sql.gcp.upbound.io/v1beta2\"\n                kind = \"DatabaseInstance\"\n                resourceRef.name = ocds.dbinstance?.Resource?.metadata?.name\n            }\n            by = {\n                apiVersion = \"{}.sql.crossplane.io/v1alpha1\".format(a.dbType)\n                kind = \"Grant\"\n                resourceRef.name = _resource\n            }\n        }\n    } for grant in grants]\n}\nif oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n    _grantUsages = grant_usages(Grant {dbType = \"mssql\"})\n\nif oxr.spec.parameters.engine.startswith(\"POSTGRES\"):\n    _grantUsages = grant_usages(Grant {dbType = \"postgresql\"})\n\ngrantUsages = _grantUsages\n# Construct Connection secrets\ndetails = {\n    apiVersion = \"meta.krm.kcl.dev/v1alpha1\"\n    kind = \"CompositeConnectionDetails\"\n    data: {\n        password = ocds.dbinstance?.ConnectionDetails?[\"attribute.root_password\"]\n        if oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n            username = base64.encode(\"sqlserver\")\n        \n        if oxr.spec.parameters.engine.startswith(\"POSTGRES\"):\n            username = base64.encode(\"postgres\")\n        \n        privateIP = ocds.dbinstance?.ConnectionDetails?.privateIP\n        publicIp = ocds.dbinstance?.ConnectionDetails?.publicIP\n        if oxr.spec.parameters.usePublicIpConnection:\n            endpoint = ocds.dbinstance?.ConnectionDetails?.publicIP\n        else:\n            endpoint = ocds.dbinstance?.ConnectionDetails?.privateIP\n        connectionName = ocds.dbinstance?.ConnectionDetails?.connectionName\n        serverCACert = ocds.dbinstance?.ConnectionDetails?.serverCACertificateCert\n        if ocds.dbinstance?.Resource?.status?.atProvider?.serviceAccountEmailAddress:\n            serviceAccountEmailAddress = base64.encode(ocds.dbinstance?.Resource?.status?.atProvider?.serviceAccountEmailAddress)\n        \n    }\n}\n# Patch the XR with the status field\ndxr = {\n    **oxr\n    status.database = {\n        **instanceStat\n        if len(databases) > 0:\n            **dbStats\n        \n        if len(dbUsers) > 0:\n            if oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n                **mssqlUserStats\n            else:\n                **dbUserStats\n        \n        if oxr.spec.parameters.sslCert?.required and not oxr.spec.parameters.engine.startswith(\"SQLSERVER\"):\n            **sslCertStat\n        \n        if len(grants) > 0:\n            **grantStats\n        \n    }\n}\n_items = [instance, providerConfig, details, dxr] + databases + dbUsers + grants + grantUsages\n_items += [sslCert] if oxr.spec.parameters.sslCert?.name and not oxr.spec.parameters.engine.startswith(\"SQLSERVER\") else []\n_items += mssqlUsers + mssqlUsages if oxr.spec.parameters.engine.startswith(\"SQLSERVER\") else []\nitems = _items\n\n"
      step: database
    - functionRef:
        name: function-auto-ready
      step: automatically-detect-ready-composed-resources
  writeConnectionSecretsToNamespace: crossplane-system

example XR

---
apiVersion: apiextensions.crossplane.io/v1alpha1
kind: EnvironmentConfig
metadata:
  name: default
  labels:
    tenant: default
    type: tenant
data:
  database:
    privateNetwork: test-cluster
---
apiVersion: v1
data:
  root: c2VjcmV0cGFzc3dvcmQ=
  someuser: MkxHa2xFYXZRUW89
  anotheruser: cDFMMmxscW9lUGM9
kind: Secret
metadata:
  name: test-postgres-passwords
---
apiVersion: dbaas.idp.example.edu/v1alpha1
kind: DatabaseClaim
metadata:
  name: test-postgres
  annotations:
    foo: bar
  labels:
    biz: bar
spec:
  writeConnectionSecretToRef:
    name: test-postgres-secret
  parameters:
    name: test-postgres
    tenantCode: demo
    deletionPolicy: Delete
    engine: POSTGRES_15
    region: us-central1
    rootPasswordSecret:
      key: root
      name: test-postgres-passwords
    diskSize: 10
    authorizedNetworks: []
    publicAccess: false
    sslCert:
      name: my-cert
      required: false
      deletionPolicy: Delete
    preferredZone: us-central1-a
    maintenance:
      day: 1
      hour: 12
    machineType: db-custom-1-4096
    labels:
      biz: baz
    usePublicIpConnection: false
    databases:
      - name: somedb
        grants:
          - user: someuser
            permissions:
              - ALL
        deletionPolicy: Delete
      - name: anotherdb
        grants:
          - user: anotheruser
            permissions:
              - ALL
        deletionPolicy: Delete
    users:
      - name: someuser
        deletionPolicy: Delete
        passwordSecret:
          key: someuser
          name: test-postgres-passwords
      - name: anotheruser
        deletionPolicy: Delete
        passwordSecret:
          key: anotheruser
          name: test-postgres-passwords

What environment did it happen in?

Crossplane version: v1.15.2

[bobh66]

See the Usages alpha feature to define deletion dependencies between resources: https://docs.crossplane.io/latest/concepts/usages/

[bradkwadsworth-mw]

See the Usages alpha feature to define deletion dependencies between resources: https://docs.crossplane.io/latest/concepts/usages/

I actually tried Usages, but from what I can see you cannot point it to a Connection Detail secret.

[bobh66]

I actually tried Usages, but from what I can see you cannot point it to a Connection Detail secret.

Right - you want to point it at the MR or XR resource that creates the Connection Detail

[bradkwadsworth-mw]

If I set a Usage for XDatabase which is my XR, it prevents Grants from being deleted.

cannot delete bound composite resource: admission webhook "nousages.apiextensions.crossplane.io" denied the request: This resource is in-use by 2 Usage(s), including the Usage "test-postgres-g6qlx-tv9zr" by resource Grant/test-postgres-g6qlx-b65wp

Here's one of my usages

spec:
  by:
    apiVersion: postgresql.sql.crossplane.io/v1alpha1
    kind: Grant
    resourceRef:
      name: test-postgres-g6qlx-b65wp
  of:
    apiVersion: dbaas.idp.example.edu/v1alpha1
    kind: XDatabase
    resourceRef:
      name: test-postgres-g6qlx
  replayDeletion: true

[bobh66]

That Usage will block deletion of the XDatabase resource as long as the Grant resource exists. Once the Grant has been deleted the XDatabase can be deleted. If the Grant is being created by the XDatabase composite you probably want the Usage to point at an MR within the composite rather than the composite itself.

[bradkwadsworth-mw]

What would you point to for a Connection Details secret, though? It's composed of secrets from multiple MRs and the secret is deleted as soon as the XR is deleted.

[bobh66]

Are there MRs within the Composite that are consuming the Connection Details secret? Or are the consumers of the secret external to the XR?

[bobh66]

The reconciler removes the ConnectionDetails as soon as the Composite is marked deleted: https://github.com/crossplane/crossplane/blob/master/internal/controller/apiextensions/composite/reconciler.go#L504

[bradkwadsworth-mw]

Yes, the providerconfig for the sql-provider is using the Connection Details secret for its credentials. I wonder if like providerConfigUsage, there should be a connectionDetailsUsage to determine if the secret is being referenced in any provider credentials?

[bobh66]

I would create a Usage that specifies the ProviderConfig is using the base DB resource that is generating the credentials. That should block deletion of the DB resource as long as the ProviderConfig exists and is in use.

[bobh66]

Actually that might not help since it's the connectiondetails Secret that is being used. You may need to create a dedicated Secret for the ProviderConfig to reference (and a Usage to define the dependency), or put all of the DB instance resources into a separate composite from the ProviderConfig and then specify the dependency between the ProviderConfig and the DB composite.

[bradkwadsworth-mw]

hmm.. that may be something to think about.