For security request in companies, Snowflake end users are required to change their password regularly for example every 90 days. Snowflake admins need to send email reminder multiple times before password expiration.
How can we send email using Snowflake? One approach is to use Snowflake External Function to call an Email sending application. This application provides REST API hosted by AWS API Gateway which invokes Lambda sending emails via AWS SES. Snowflake External Function passes email list from database to POST method. Let’s see how we can do it.
1 Create an Email sending application as REST service in AWS
1.1 Create IAM Role for Lambda to access SES service
This IAM role, named “LambdaSendEmailRole”, will be used by Lambda function to call SES service.
Important Note: we’ll create multiple roles and set multiple permissin/policies. Make sure you have checked this chart out for the relationship of all the user/roles/permissions used in this post.
From IAM -> Roles, choose “Create role”, select “AWS service” and “Lambda” as Trusted entity type.
Make sure we added two permission policies to this role: AmazonSESFullAccess and CloudWatchFullAccess.
1.2 Create Lambda function
Create Lambda function “SendEmailLambda” as following — specifying Python 3.9 as Runtime and above IAM role as execution role.
Use following code to send email via SES service. Lambda receives four email parameters: sender, receiver, subject and body. This will have to match the parameters Snowflake External Function sends. Lambda orgnizes the parameters and call SES service to send email.
import json
import boto3
client = boto3.client("ses", region_name="us-east-1")
def lambda_handler(event, context):
status_code = 200
status_to_return = [ ]
try:
event_body = event["body"]
payload = json.loads(event_body)
rows = payload["data"]
for row in rows:
row_number = row[0]
sender = row[1]
receiver = row[2]
subject = row[3]
email_body = row[4]
email_message = {
"Subject": {"Data": subject,},
"Body": {"Html": {"Data": email_body,},},
}
response = client.send_email(Source = sender,
Destination = {"ToAddresses": [receiver],},
Message = email_message,)
status_to_return.append([row_number, "Email Sent to "+receiver])
total_status_to_return = json.dumps({"data" : status_to_return})
except Exception as err:
status_code = 400
total_status_to_return = event_body
return {
'statusCode': status_code,
'body': total_status_to_return
}1.3 Create REST API endpoint in API Gateway
1.3.1 Create REST API, resource and method
From API Gateway console, choose “REST API” and click “Build”. Name our API as “SendEmailRESTAPI”.
Use “Actions” dropdown list to create “resource” in this API. The resource name will be last part of API endpoint URL. We create a resource called “sendemail”.
Continue using “Actions” dropdown list to create a POST method for “sendemail” resource. Choose Integration type to be “Lambda Function” which this POST method will call. Check “Use Lambda Proxy integration” checkbox and key in our Lambda function name “SendEmailLambda” created in 1.2.
Click “Ok” when asked to let this API call our Lambda function in popped out window.
1.3.2 Deploy the API
Again, using “Actions” dropdown list to “Deploy API” to “v1” stage.
Ok, now we have our API deployed, click on POST method and take note for the “Invoke URL”.
The “Invoke URL” is something like following. We’ll need it to configure API integration and external function in Snowflake. This is the REST endpoint Snowflake calls to send email.
https://gps5jlzahg.execute-api.us-east-1.amazonaws.com/v1/sendemailOk, now we have setup an application(API “SendEmailRESTAPI” on AWS) that sends emails. Next we’ll setup Snowflake to call this application.
2 Create Snowflake External Function
2.1 First, in AWS, create another IAM role for Snowflake external function to assume when it calls sending email application API.
Go to AWS IAM and click “Create role”. This time choose “AWS account” as “Trusted entity type” which means this role will be assumed by a given AWS account/role/user. (Note, in 1.1, we created IAM role with SES access permission for Lambda service to send email. That role has “AWS service” as “Trusted entity type”.)
Also choose “current account” radio box meaning that resources in “current account” can be used by whoever (AWS role/user) assumes this role. We’re limiting who can assume this role in a minute.
We’ll also require providing external ID when whoever assumes this role. For now, we use “0000” as external ID placeholder. We’ll modify trust policy with actual user and external ID info of Snowflake API integration later in 2.3.
This role will be assumed by Snowflake External Function to invoke “sendemail” API. But here we don’t add APIGateway Invoke permission… instead we’ll set Snowflake External Function’s user/external ID in role’s trust policy later.
We then name our role as “SnowflakeExternalFunctionRole”. Now we’re ready to move on to Snowflake for External Function.
2.2 Create Snowflake API integration
Snowflake API integration will be used by this External Function. It provides a way for authenticating/trusting Snowflake when accessing external resources (AWS API endpoint in this case).
This is similar to storage integration which provides trust relationship for storage external stage and S3. See details in one previous post.
Back to creating API integration, we need to define what role and what API Snowflake External Function will be using . So we use role ARN from 2.1 and “sendemail” API endpoint from 1.3.2. We named this API integration as “send_email_api_integration”.
// API integration
create or replace api integration send_email_api_integration
api_provider = aws_api_gateway
api_aws_role_arn = 'arn:aws:iam::1xxxx7:role/SnowflakeExternalFunctionRole'
enabled = true
api_allowed_prefixes = ('https://4xxxxh.execute-api.us-east-1.amazonaws.com/v1/sendemail');
desc integration send_email_api_integration;
Take note for API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID of this API integration. Snowflake API integration created AWS IAM user and external ID to invoke AWS API. In next 2.3 we’ll set them in trust policy of the AWS IAM role “SnowflakeExternalFunctionRole”.
2.3 Update “Trusted Relationship” in IAM role “SnowflakeExternalFunctionRole” using user and external ID of the API integration
Configure “Trust relationships” in role “SnowflakeExternalFunctionRole” using above user ARN for Principal and external ID for ExternalID when editing trust policy.
Now this role “SnowflakeExternalFunctionRole” trusts Snowflake API integration so the role can be used by API integration to invoke our SendEmailRESTAPI. But wait we have not secured our API yet so by now anyone can invoke it! Let’s secure it right now.
2.4 Secure API using IAM Role authentication and set resource access policy
Next, secure our API by choosing IAM Role authentication and adding resource policy. We want only Snowflake external function role “SnowflakeExternalFunctionRole” can invoke this API method.
Go to “Resources” -> POST, at right panel in Method Request box, take a note for method request ARN and then click on “Method Request” title text.
On next screen, find “Authentication” in “Settings” and use the small “pen” icon to edit it to be “AWS IAM”. Now the invokes to this POST method requires IAM role verification.
So which role/s can invoke this POST method? We’ll define in API’s resource access policy for our “sendemail” resource.
Click “Resource Policy” from left side and paste in following policy. Remember replacing three places with actual values: AWS account number, IAM role name for external function and method request ARN from 1.3.2 note.
{
"Version": "2012-10-17",
"Statement":
[
{
"Effect": "Allow",
"Principal":
{
"AWS": "arn:aws:sts::<12-digit-aws-account>:assumed-role/<IAM_role_for_external_function>/snowflake"
},
"Action": "execute-api:Invoke",
"Resource": "<resource_method_request_ARN>"
}
]
}It’ll look like this…
Now we’re ready to create external function!
2.5 Create Snowflake External Function
// External Function to call API sending email.
create or replace external function send_reminder_email_external_function
(sender varchar, receiver varchar, subject varchar, body varchar)
returns variant
api_integration = send_email_api_integration
as 'https://4xxxxh.execute-api.us-east-1.amazonaws.com/v1/sendemail';By now, we’ve gotten our external function created after all these setup. But before AWS SES can send Email, it needs to verify Email address first.
2.6 In SES service, create identity and verify Email address
Provide your Email address when creating identity. You’ll receive confirmation Email and need to verify by clicking the link in the Email.
3 Use the Snowflake External Function to send Email
Finally let’s try it out…
3.1 Base test
use “select” to call external function providing four parameters: sender, receiver, subject and body. External function passes the parameter to “sendemail” API endpoint. “sendemail” API triggers Lambda function which calls SES to send email accordingly.
// call the external function
select send_reminder_email_external_function('Snowflake Admin <aaaa@gmail.com>',
'xxxx@gmail.com',
'Reminder Subject',
'This is a reminder email body.');3.2 Simulation Test
Use case is to send password expiring emails to Snowflake users whose passwords are expiring in 14 days and less as security policy requires lifetime of 90 days.
We have a database table having user email address and last update date etc. So we can run following “select” statement to call external function with the column values from database table as parameters.
create or replace table email_list_table (email varchar, last_update date);
insert into email_list_table values ('xxxx@gmail.com', '2022-09-15');
insert into email_list_table values ('yyyy@gmail.com', '2022-12-01');
select * from email_list_table;
select send_reminder_email_external_function('Snowflake Admin <aaaa@gmail.com>',
email,
concat('Password will expire in ',(90 - datediff(day, last_update, current_date())), ' days'),
'This is a reminder email body.')
from email_list_table where 90 - datediff(day, last_update, current_date()) <= 14;
Email looks like following:
3.3 Create/schedule Snowflake task to send emails automatically
Now we can create a task which runs every day (every 1 minute in following sample code) to look for whoever has password expiring in 14 days and send reminder emails!
// Create task to call the external function every 1 minute
create or replace task send_password_reminder_email_task
warehouse = feng_warehouse
schedule = '1 minute'
as
select send_reminder_email_external_function('Snowflake Admin <aaaa@gmail.com>',
email,
concat('Password will expire in ',(90 - datediff(day, last_update, current_date())), ' days'),
'This is a reminder email body.')
from email_list_table where 90 - datediff(day, last_update, current_date()) <= 14;
show tasks;
// activate task
alter task send_password_reminder_email_task resume;
// Monitor task execution history
select * from table(information_schema.task_history()) order by name asc, scheduled_time desc;
// suspend task
alter task send_password_reminder_email_task suspend;
4 More discussion
One more detail about what external function parameter “email” look like in above “select” statement…
If we have two users in table “email_list_tab”
Given following “select” statement:
select send_reminder_email_external_function('Snowflake Admin <aaaa@gmail.com>',
email,
concat('Password will expire in ',(90 - datediff(day, last_update, current_date())), ' days'),
'This is a reminder email body.')
from email_list_table where 90 - datediff(day, last_update, current_date()) <= 14;Event body in Lambda will be as follows as of today Dec 9:
// Lambda Code
event_body = event["body"]
payload = json.loads(event_body)
rows = payload["data"]
for row in rows:
...
receiver = row[2]
...
response = client.send_email(Source = sender,
Destination = {"ToAddresses": [receiver],},
Message = email_message,)
...
// Value of "rows" from event body
[
[
0,
"Snowflake Admin <aaaa@gmail.com>",
"xxxx@gmail.com",
"Password will expire in 3 days",
"This is a reminder email body."
],
[
1,
"Snowflake Admin <aaaa@gmail.com>",
"yyyy@gmail.com",
"Password will expire in 5 days",
"This is a reminder email body."
]
]So now we know “select” statement generates the SQL results first
// Snowflake does this first without external function, then passes SQL results
// to external function
select 'Snowflake Admin <fengliplatform@gmail.com>',
email,
concat('Password will expire in ',(90 - datediff(day, last_update, current_date())), ' days'),
'This is a reminder email body.'
from email_list_table where 90 - datediff(day, last_update, current_date()) <= 14;
Then it calls external function once passing the results as an array to external function: each row from SQL results is one element in the array. Lambda function in turn needs to loop the array to get each user.
Happy Reading!
