[Feature]: New resource for Warehouse assignment to Resource Monitor

[jarach]

Use Cases or Problem Statement

Any assignment to Resource Monitor requires ACCOUNTADMIN role. There is no "ALTER WAREHOUSE" in Snowflake-Labs provider, that can be executed separately with ACCOUNTADMIN role. It would be very convenient to have such option in order to:

• assign an existing warehouse to a resource monitor
• create warehouse with different role than ACCOUNTADMIN
• not re-create a warehouse if a resource monitor was re-created

Category

category:resource

Object type(s)

resource:resource_monitor, resource:warehouse

Proposal

Create a new resource for a resource assignment to a Resource Monitor

How much impact is this issue causing?

Low

Additional Information

Current workarounds are:

1. create warehouse with desired/chosen role (to keep desired ownership) + use snowflake_unsafe_execute to alter warehouse (assign resource monitor with ACCOUNTADMIN)
2. create warehouse (with resource assignment) using ACCOUNTADMIN + change ownership of the warehouse with snowflake_grant_ownership

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jcieslak]

Hey @jarach 👋
Thank you for reporting this issue. I'll discuss it with the team and get back to you.

[sfc-gh-jcieslak]

Hey @jarach 👋
Still, the topic couldn't be discussed with the team for various reasons, but I'll get back to you when we go through this. For now, the workaround could be to use ignore changes on the resource_monitor field in warehouse resource (otherwise warehouse resource will try to unset the resource monitor on the warehouse) and set the resource monitor through unsafe_execute resource with ACCOUNTADMIN role.

[sfc-gh-jcieslak]

Hey @jarach
During recent planning, we took the topic of "attachment" type resources as a whole. Since this is a bigger topic, we would like to discuss overall what our approach should be to such resources. Those discussions are postponed for now. For now, our main goal is to finish other bigger topics and focus on providing changes that contribute to the provider's V1 release.

[Ssvenkerud]

I will throw my hat inn on this issue, to demonstrate furter intresst for this feature.

As @jarach I would highly appreciate that this be solved. Snowflakes own guidance on what roles to use for what aspect are quite clear, and currently we are forced by the provider to use a role with excessive privileges to create the warehouses. The currently proposed workarounds create a cascading set of privilege and/or timing issues in the provider. For this reason i see neither of the workarounds as viable from a security perspective.

[sfc-gh-asawicki]

Hey @Ssvenkerud.

Let's say we conditionally remove resource monitor management from the warehouse resource and introduce an entirely new resource that is responsible solely for warehouse-resource monitor attachments. This resource will still:

• need a privileged role either by using an aliased provider or e.g. by running USE ROLE directly in this resource (it has some caveats in the current provider's architecture, but let's say we work around them).
• need to be run after warehouse is created (direct/indirect dependency over the warehouse resource).

This option is really close to the proposed workaround, i.e.:

• setting the ignore changes on the resource_monitor field in warehouse resource now
• setting up an aliased provider with a privileged role
• using unsafe_execute to attach the resource monitor (still, having a dependency on the warehouse resource)

The only con I see is that unsafe execute does not provide a way to detect external changes, whereas the dedicated resource would.

[Ssvenkerud]

Hi @sfc-gh-asawicki
That could work. Having a differnet resource in charge of resorurce monitor assignment, wold allow some degree of separation. while avoiding the Execute unsafe, and retaining external change management.

If the resource is truly stand alone, the order of work can be managed with the depends_on meta argument.
A seperate resource also would allow us to decouple the assignment and creation, in those cases where the we need a warehouse, without a resource monitor, because the monitor is not determined in advance, but will be added later.
The provider already assumes elevated privileges for the monitor management, so that is handled, but not needing to use elevated privileges for the resource creation is much better.

As an alternative to not having it at all. I like the proposed solution.

What caveats would be attached to this?