Decouple provider mode detection from authentication parameters

[bobbyiliev]

The provider conflates "how you authenticate" with "what you're connecting to." Setting host means both "direct connection" and "self-managed instance", but those aren't the same thing.

Cloud users who configure with host/username/password get classified as self-hosted, blocking ~10 Frontegg-dependent resources (materialize_app_password, materialize_user, SSO/SCIM).

A Cloud customer hit this: configured with host/username/password, couldn't create app passwords or service users. Switching to Cloud auth mode isn't trivial because it changes state ID prefixes (self-hosted:u38 → aws/us-east-1:u38), potentially breaking the state file.

Root Cause

Two main concerns are coupled into one:

	Single-instance	Multi-instance
Cloud	Direct connection to one Cloud region (currently broken)	Multi-region via app password (works)
Self-managed	Direct connection (works)	N/A

Proposal

1. Add app_password provider parameter

Allow Frontegg authentication alongside direct connections:

# Self-managed (unchanged)
provider "materialize" {
  host     = "my-instance.local"
  username = "materialize"
  password = "pass"
}

# Direct to Cloud (new)
provider "materialize" {
  host           = "instance.materialize.cloud"
  username       = "user"
  password       = "pass"
  app_password   = var.app_password   # enables Frontegg/cloud resources
  default_region = "aws/us-east-1"    # required, used for state ID prefix
}

# Multi-region Cloud (unchanged)
provider "materialize" {
  password       = var.app_password
  default_region = "aws/us-east-1"
}

When app_password is present alongside host:

• Initialize the Frontegg client → cloud-only resources unlocked
• Require default_region → use region prefix in state IDs (forward-compatible with multi-region)
• Gate ValidateSaaSOnly() on "has Frontegg client" instead of "is SaaS mode"

2. Cloud host detection warning

If host matches *.materialize.cloud and no app_password is provided, emit a warning: "Looks like you're connecting to Materialize Cloud, consider adding app_password and default_region to enable cloud resource management."

Not an error but just a nudge.

3. Automatic state migration

When switching from direct → cloud mode (either adding app_password or switching to full multi-region), existing self-hosted: state IDs need to be migrated. Handle this transparently:

• In GetDBClientFromMeta: if in cloud mode and extracted region is self-hosted, fall back to default_region
• During resource Read: rewrite self-hosted:u38 → aws/us-east-1:u38
• Log clearly: "Migrating resource X from self-hosted to region aws/us-east-1"
• Next terraform plan picks up the clean state

This handles both migration paths:

• Direct → direct-to-cloud (add app_password, keep host)
• Direct → full multi-region (drop host, switch to app password auth)

No manual state surgery needed.

Scope

• No breaking changes for existing self-managed users
• No changes to multi-region Cloud mode
• self-hosted: prefix stays for actual self-managed instances

Key Files

• pkg/provider/provider.go — providerConfigure(), configureSelfHosted(), configureSaaS()
• pkg/utils/provider_meta.go — ProviderMode, ValidateSaaSOnly(), GetDBClientFromMeta()