How to transfer ownership of database to another role using Terraform resource?

[hongbo-miao]

I created my Terraform user by

use role USERADMIN;
create user if not exists MY_TERRAFORM_READ_WRITE_USER RSA_PUBLIC_KEY='xxx' default_role=PUBLIC must_change_password=false;

use role SECURITYADMIN;
create role if not exists MY_TERRAFORM_READ_WRITE_ROLE;
grant role MY_TERRAFORM_READ_WRITE_ROLE to user MY_TERRAFORM_READ_WRITE_USER;
grant create role on account to role MY_TERRAFORM_READ_WRITE_ROLE;
grant create user on account to role MY_TERRAFORM_READ_WRITE_ROLE;
grant manage grants on account to role MY_TERRAFORM_READ_WRITE_ROLE;

use role SYSADMIN;
grant create database on account to role MY_TERRAFORM_READ_WRITE_ROLE;
grant create warehouse on account to role MY_TERRAFORM_READ_WRITE_ROLE;

Note the role does not drop database permission.
However, the database got created by Terraform, so the ownership is MY_TERRAFORM_READ_WRITE_ROLE, which means Terraform can still destroy the database.

I am hoping to prevent database got deleted by accident.

I have added prevent_destroy.

resource "snowflake_database" "my_snowflake_database" {
  name                        = var.snowflake_database_name
  data_retention_time_in_days = var.data_retention_days
  lifecycle {
    prevent_destroy = true
  }
}

But for person who has MY_TERRAFORM_READ_WRITE_ROLE can still comment out prevent_destroy can continue destroying the databases if run locally.

I did create an XXX_DB_ADMIN_ROLE for each database.

I am wondering if there is a way to do either of these?

• switch to XXX_DB_ADMIN_ROLE role in Terraform and create database
• transfer database ownership to XXX_DB_ADMIN_ROLE using Terraform resource
• assign to a role that is not executer during creating the database using Terraform resource

Thanks!

[hongbo-miao]

This works to me.

https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/grant_ownership

resource "snowflake_grant_ownership" "snowflake_transfer_database_ownership_to_role" {
  account_role_name  = "XXX_DB_ADMIN_ROLE"
  outbound_privileges = "REVOKE"
  on {
    object_type = "DATABASE"
    object_name = "XXX_DB"
  }
}

After changing ownership of database, now Terraform can never drop the database ☺️

[hongbo-miao]

I also opened two additional tickets, as I believe they offer a better long-term solution:

• Please avoid destroying Snowflake database if not empty #2713
• Please avoid destroying Snowflake schema if not empty #2714