On serverless, authentication impacts DDL generation (database vs IAM)

[Fleid]

dbt generates different DDL statements on serverless depending on the authentication method. This is widely unexpected.
Same behavior is not observed on provisioned clusters.

Serverless with database auth, expected behavior

~/.dbt/profiles.yml

my_project:
  target: serverless_database
  outputs:
    serverless_database:
      dbname: dev
      host: workgroup.account.region.redshift-serverless.amazonaws.com
      port: 5439
      schema: ccschema
      threads: 1
      type: redshift
      sslmode: prefer
      user: admin
      password: PASSWORD

DDL

create  table  "dev"."ccschema"."my_first_dbt_model__dbt_tmp" ...
alter table "dev"."ccschema"."my_first_dbt_model" rename to "my_first_dbt_model__dbt_backup"
alter table "dev"."ccschema"."my_first_dbt_model__dbt_tmp" rename to "my_first_dbt_model"
drop table if exists "dev"."ccschema"."my_first_dbt_model__dbt_backup" cascade
...

Serverless with IAM auth, bad behavior resulting in error:

~/.dbt/profiles.yml

my_project:
  target: serverless_user_profile
  outputs:
      dbname: dev
      host: workgroup.account.region.redshift-serverless.amazonaws.com
      method: iam
      port: 5439
      schema: ccschema
      threads: 1
      type: redshift
      sslmode: prefer
      user: admin
      iam_profile: cc_iam

DDL:

create  table "dev"."ccschema"."my_first_dbt_model__dbt_tmp" ...
drop external if exists "dev"."ccschema"."my_first_dbt_model" cascade
Redshift adapter: Redshift error: syntax error at or near "if"

dbt switched to drop external, which is unexpected (and not supported on Serverless).

This is installing from source:

18:17:42  Running with dbt=1.9.0-a1
18:17:42  dbt version: 1.9.0-a1
18:17:42  python version: 3.10.5

[colin-rogers-dbt]

On further investigation this is actually an issue with database privileges between users, tho it highlights that we may want to explicitly warn users that the user field is not who their db user will be