[ADAP-1034] [Bug] on-run-start hook not run during docs generate step and causes failure

[crystalro0]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Customer is unable to run docs generate manually or with the generate docs button due to using the following hooks in dbt_project.yml:
+on-run-start: "USE SECONDARY ROLES <role_name>"
+pre-hook: "USE SECONDARY ROLES <role_name>"

docs fails with Database "MY_DB" does not exist or not authorized since current role doesn’t have access to that database. via the catalog.sql macros when attempting to pull from information_schema.

Expected Behavior

Expecting docs generate step to succeed with on-run-start hook or pre-hook running.

Steps To Reproduce

1. Setup prod env with one role.
2. Add on-run-start hook with different role (one that has access to everything).
3. Run job with Generate Docs button enabled OR a manually added docs generate step.
4. Should fail on docs step.

Relevant log output

Database "MY_DB" does not exist or not authorized since current role doesn’t have access to that database.

Environment

- OS: macOS/dbt Cloud
- dbt-core: 1.6
- dbt-snowflake: 1.6

Additional Context

No response

[dataders]

hiya @crystalro0 thanks for writing this up. I have a few follow-up questions

why does this fail?

does the role you prescribe in the pre-hook have access to MY_DB? If you were to make a separate profile and invoke dbt docs generate with a --target secondary_role it would work without issue?

This would help me understand where the issue is happening

what is the goal of doing this?

Perhaps this is related to @seub's #803?

[mathissen]

Hi
We reported this error.

So it works if we do it like you described with using --target secondary_role after setting up the secondary_role in the profile.

It looks like it is related to: #803

In the documentation it is stated that sql statements that are defined in on-run-start will run before the dbt commands: dbt build, dbt docs generate etc. However when we use use secondary role in on-run-start then it only seems to run before the dbt build command.

[colin-rogers-dbt]

So while we still need to investigate and plan if/how we can support secondary roles in #803 we will definitely need to investigate why on-run-start isn't behaving as expected. Realistically with the holidays we probably won't get to this for a couple of weeks but will keep queue this up for estimation/prioritization.

[dataders]

my current summary

1. use secondary role isn't working as expected when used as a on-run-start hook and dbt docs generate is called. however, other hooks do work as expected with dbt docs generate

2. I wouldn't call this a bug really just a consequence of not explicitly supporting Snowflake secondary roles ([ADAP-944] [Feature] Support secondary roles #803)

3. imo, the real user story here is

   as an analytics engineer shipping data products for end users, I want to be able to test my products from the auth/RBAC perspective of end users so that I can have confidence in the quality of my deliverables

4. the workaround to (1) of using a secondary profile works, but Snowflake's secondary_roles seems to suggest there exists a better, more-integrated experience.

5. this particular "bug" could be addressed, but it isn't a high-priority for us, especially given that there is a workaround.

6. My suggestion would be to continue fleshing out and making the case for [ADAP-944] [Feature] Support secondary roles #803, perhaps the new unit testing capabilities landing soon in dbt-core 1.8 allow for an easier path for integration.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.