[Feature]: Display OAuth expiration for security integration

[joscha]

Company Name

Planet A GmbH

Use Cases or Problem Statement

Airtable support synchronizing data from Snowflake: https://support.airtable.com/docs/airtable-sync-integration-snowflake
This uses an external OAuth token to do so. This token is not refreshed by Airtable, the onus is on the person setting up the integration.
We would love to display when the token expires. The data is available in Snowflake and also in terraform, but it's not entirely usable right now.

Given an integration like this:

resource "snowflake_oauth_integration_for_custom_clients" "airtable_data" {
  provider                   = snowflake.ingest_role
  enabled                    = "true"
  name                       = "airtable_data"
  oauth_client_type          = "CONFIDENTIAL"
  oauth_redirect_uri         = "https://airtable.com/integration/authorize/eatY89uUyfSoAncQS/callback"
  oauth_issue_refresh_tokens = "true"
  # Extended to 365 days via support
  oauth_refresh_token_validity = 31535000
  oauth_use_secondary_roles    = "IMPLICIT"
}

I was hoping to be able to just read the created_on output and add the oauth_refresh_token_validity to it, i.e.:

output "oauth_secret_expiration" {
  value = timeadd(snowflake_oauth_integration_for_custom_clients.airtable_data.show_output[0].created_on, "${snowflake_oauth_integration_for_custom_clients.airtable_data.oauth_refresh_token_validity}s")
}

however, created_on is some mixture of ISO 8601 with a timezone abbreviation or so (YYYY-MM-DD HH24:MI:SS.FF3 TZH TZM)? Thus this fails with:

│ Error: Error in function call
│ 
│   on airtable_sync.tf line 38, in output "oauth_secret_expiration":
│   38:   value = timeadd(snowflake_oauth_integration_for_custom_clients.airtable_data.show_output[0].created_on, "${snowflake_oauth_integration_for_custom_clients.airtable_data.oauth_refresh_token_validity}s")
│     ├────────────────
│     │ while calling timeadd(timestamp, duration)
│     │ snowflake_oauth_integration_for_custom_clients.airtable_data.oauth_refresh_token_validity is 31535000
│     │ snowflake_oauth_integration_for_custom_clients.airtable_data.show_output[0].created_on is "2000-01-01 08:00:50.076 -0800 PST"
│ 
│ Call to function "timeadd" failed: not a valid RFC3339 timestamp: missing required time introducer 'T'.

It would be great if:

• The resource could provide a expires_on
• Or there would be a data source for SHOW INTEGRATIONS (where created_on is an actual terraform timestamp, Snowflake itself returns some ISO 8601 but with a TZ identifier and it can't be queried via SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())); so using TO_TIMESTAMP_TZ is out of the question, too.)

Category

category:resource

Object type(s)

resource:oauth_integration

Proposal

In order of preference:

• a synthetic expires_on is exposed
• there is a integrations data source that has a timestamp for the created_on column.

How much impact is this issue causing?

Low

Additional Information

No response

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jcieslak]

Hey @joscha 👋
Thank you for reporting the issue, we'll take a look at it during the revisit of security integration. Did you try to utilize the execute resource somehow? You can pass a custom query there and retrieve results from it, and maybe that would be enough to get something that Terraform can parse (?). If that would be something more challenging, also creating a Snowflake function could be a better approach (at least for now).

[joscha]

Hey @joscha 👋 Thank you for reporting the issue, we'll take a look at it during the revisit of security integration. Did you try to utilize the execute resource somehow? You can pass a custom query there and retrieve results from it, and maybe that would be enough to get something that Terraform can parse (?). If that would be something more challenging, also creating a Snowflake function could be a better approach (at least for now).

Hi @sfc-gh-jcieslak

I tried:

resource "snowflake_execute" "integrations" {
  execute = "SHOW INTEGRATIONS"
  revert  = ""
}

but whilst that executes, it doesn't seem to yield anything.

I also tried:

resource "snowflake_execute" "integrations" {
  execute = "SHOW INTEGRATIONS"
  revert  = "SHOW INTEGRATIONS"
  query = "SHOW INTEGRATIONS"
}

and a few combinations with SELECT 1, but it seems to throw:

│ Error: 000900 (42601): SQL compilation error:
│ Empty SQL statement.

in those cases.

Do I misunderstand?

[sfc-gh-jcieslak]

Nvm, this would probably end up in the same place. Snowflake allows you to adjust the timestamp output. Check parameters page and search for _output_format. There are a number of parameters (e.g. TIMESTAMP_NTZ_OUTPUT_FORMAT) you can modify to make Snowflake return standard timestamp format. You can use params to set those on the session level (if they support it; it should be documented in the parameters page). Let me know if that helps.

[joscha]

Nvm, this would probably end up in the same place. Snowflake allows you to adjust the timestamp output. Check parameters page and search for _output_format. There are a number of parameters (e.g. TIMESTAMP_NTZ_OUTPUT_FORMAT) you can modify to make Snowflake return standard timestamp format. You can use params to set those on the session level (if they support it; it should be documented in the parameters page). Let me know if that helps.

Thank you!

Just so I understand this right:

I would use the execute statement in a resource, depend the oauth one on it, set the output format in the execute, apply the oauth and then swap it back afterwards with another execute?

In that case I might actually just implement a custom Terraform function to parse the odd date format string, that sounds possibly less fragile.

[sfc-gh-jcieslak]

You should be able to use the original approach, this one:

output "oauth_secret_expiration" {
  value = timeadd(snowflake_oauth_integration_for_custom_clients.airtable_data.show_output[0].created_on, "${snowflake_oauth_integration_for_custom_clients.airtable_data.oauth_refresh_token_validity}s")
}

When you change the parameter output format of create_on should be usable in timeadd (you have to adjust the format of it):

show databases like 'test_database'; -- created_on = 2025-02-14 03:06:04.556 -0800
alter session set timestamp_ltz_output_format = 'YYYY-MM-DD';
show databases like 'test_database'; -- created_on = 2025-02-14

Note, that may affect how other resources are behaving as some of them potentially leverage timestamp fields.

I believe that even a better solution would be to use the execute resource to modify the output of snowflake_oauth_integration_for_custom_clients.airtable_data.show_output[0].created_on into something that terraform builtin timeadd could accept. An example of this:

resource "snowflake_execute" "time" {
  execute = "select 1"
  revert = "select 1"
  query = "SELECT TO_TIMESTAMP('2019-02-28T23:59:59', 'YYYY-MM-DD\"T\"HH24:MI:SS') AS TIME"
}

output "time_output" {
  value = snowflake_execute.time.query_results.0["TIME"]
}

But you have to replace the arguments in TO_TIMESTAMP with snowflake_oauth_integration_for_custom_clients.airtable_data.show_output[0].created_on and the corresponding format. This is not a clean solution, but it should work.

This could be a good use case for custom Terraform functions. cc: @sfc-gh-jmichalak @sfc-gh-asawicki