fix(snowflake-driver): Add support for IAM roles with IRSA for S3 export buckets

[KSDaemon]

This is a followup PR of the original one by @morford-brex with the CI fixes.

Fixes #10010
Closes #10011

Check List

• Tests have been run in packages where changes made if available
• Linter has been run for changed code
• Tests for the changes have been added if not covered yet
• Docs have been added / updated if required

[KSDaemon]

@morford-brex could you help me fix CI: what common AWS Envs need to be set up to make this work? I can prepare the storage integration on the Snowflake's side. But what needs to be done on the cube's side?

[KSDaemon]

I think I've found a solution:

      - name: Configure AWS credentials via IRSA
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: ${{ secrets.DRIVERS_TESTS_AWS_ROLE_ARN_FOR_SNOWFLAKE }}
          aws-region: us-west-1

[morford-brex]

I think I've found a solution:

      - name: Configure AWS credentials via IRSA
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: ${{ secrets.DRIVERS_TESTS_AWS_ROLE_ARN_FOR_SNOWFLAKE }}
          aws-region: us-west-1

@KSDaemon - this fix makes sense to me. was the last run intentionally cancelled?

[KSDaemon]

@morford-brex I think I still might need your help here.... I was able to successfully set up a trusted relationship between GitHub and AWS, so tokens are issued and the cube can successfully authorize. But I faced a problem with the storage integration on Snowflake's side (surprisingly).

I did as usual with other integrations:

CREATE STORAGE INTEGRATION drivers_tests_preaggs_s3
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::*****'
  STORAGE_ALLOWED_LOCATIONS = ('s3://snowflake-drivers-tests-preaggs/');

DESC INTEGRATION drivers_tests_preaggs_s3;

Shows all the related info.

But running:

SELECT
  SYSTEM$VALIDATE_STORAGE_INTEGRATION(
    'drivers_tests_preaggs_s3',
    's3://snowflake-drivers-tests-preaggs/t1/',
    'validate_all.txt', 'all');

gives en error: Object 'drivers_tests_preaggs_s3' does not exist or not authorized.

For test purposes I created a stage:

CREATE OR REPLACE STAGE drivers_tests_preaggs_s3_stage
  URL='s3://snowflake-drivers-tests-preaggs/'
  STORAGE_INTEGRATION = drivers_tests_preaggs_s3;

And I'm able to list files via LIST @drivers_tests_preaggs_s3_stage; - so it seems that S3 is accessible.

But drivers test fails with Could not load credentials from any providers. So something definitely is not properly configured. I think I missed some little thing that hides from my eyes. Maybe you can help me with that?

[KSDaemon]

Aggrhh.... I totally forgot about Snowflakes cases.
Running:

SELECT
  SYSTEM$VALIDATE_STORAGE_INTEGRATION(
    'DRIVERS_TESTS_PREAGGS_S3',
    's3://snowflake-drivers-tests-preaggs/t1/',
    'validate_all.txt', 'all');

results in success

But the test still fails wit the error I mentioned. Triggered another run.

[morford-brex]

@KSDaemon - taking a look now!

[morford-brex]

@KSDaemon - i don't have write perms for your branch so i merged an attempted fix to my branch/pr. i believe the issue was that the ci containers couldn't access the credentials you configured in the actions (i.e. they were just in the runner environment) so i'm attempting to mount them dynamically into the containers when irsa is detected

[KSDaemon]

@morford-brex Ah! Got it! Yeah, regularly forget that drivers tests run via docker :) Because locally I run them in local mode, so all my envs are available out of the box

[KSDaemon]

@morford-brex Based on your idea - I think I made it easier. Have a look at https://github.com/cube-js/cube/pull/10024/files#diff-da4910ef3a08a71f36fe0b45009e9e56e782225b55ec634a2b45fd514a70e764

Hoping it will pass this time 🤞🏻

And we also forgot about adding a snapshot for the new test case - added.

[morford-brex]

@morford-brex Based on your idea - I think I made it easier. Have a look at https://github.com/cube-js/cube/pull/10024/files#diff-da4910ef3a08a71f36fe0b45009e9e56e782225b55ec634a2b45fd514a70e764

Hoping it will pass this time 🤞🏻

And we also forgot about adding a snapshot for the new test case - added.

passed 🚀 - appreciate the help here @KSDaemon !

[KSDaemon]

@morford-brex I would say - it's been a great team collaboration! :) So thank you too!
Let's merge it. I'll publish a new release after merging a few more PRs, probably tomorrow.