I am getting this error when trying to send and SNS email via lambda function:

"errorMessage": "Connect timeout on endpoint URL: \"https://sns.us-west-1.amazonaws.com/\"",
  "errorType": "ConnectTimeoutError"

I have all the policies set up with SNS full access to the respective role tied to function. Here is the full function:

import json
import psycopg2
import boto3
import time
import requests
import pandas as pd
import numpy as np
from datetime import datetime
import sys
import logging
import os
import csv
import smtplib
from base64 import b64decode


#bucket = 's3://data-lake-020192/'

credential = {
    'dbname' : 'main',
    'host_url' : 'test.us-west-1.redshift.amazonaws.com',
    'port' : '5439',
    'user' : '####',
    'password' : '########'
}


redshift_role = {
    'dev': 'arn:aws:lambda:us-west-1:##########:function:test_function'
}

def lambda_handler(event, context):
  ## S3 CONNECTIVITY ## 

  s3 = boto3.resource('s3')
  #client = boto3.client('s3')

  # TODO implement
  conn_string = "dbname='{}' port='{}' user='{}' password='{}' host='{}'"\
  .format(credential['dbname'], credential['port'], credential['user'], credential['password'], credential['host_url'])
  
  sql_query = """with
  tbl as (
  select    
        case 
          when (sa.parentid like '001i0000023STBY%' or sa.ultimate_parent_account__c like '001i0000023STBY%') --Parent OR Ultimate Parent is <Department of Defense> 
          then sa.id 
          else 
            coalesce(sa.ultimate_parent_account__c, sa.parentid, sa.id) end as cust_id,
          (select name from salesforce.account where id=cust_id) as cust_name,
          sa.name as acct_name,
          sa.id as acct_id,
          sa.parentid,
          (select name from salesforce.account where id=sa.parentid) as par_name,
          (select name from salesforce.account where id=sa.ultimate_parent_account__c) as ult_par_name,
          so.id as opp_id,
          so.name as opp_name,
          so.stagename as stg_name,
          so.type as opp_type,
          so.Manager_Commit__c as mgr_commit,
          so.renewal_risk__c as opp_risk,
          so.isclosed as cls
          salesforce.opportunity so
      join 
          salesforce.account sa on
          so.accountid = sa.id
      join salesforce.user su on
        so.ownerid = su.id
      join salesforce.opportunitylineitem sol on
        so.id = sol.opportunityid
      join salesforce.product2 sp on
        sol.product2id = sp.id
      join salesforce.customasset__c sca on
        so.id = sca.opportunity__c
      where 
          so.isdeleted = false
          and sa.isdeleted = false
          and sol.isdeleted = false
  )
  

  select * from 
  (select
      tbl.acct_name as acct,
     '[' || 'Link' || '](' || concat('https://vectranetworks.lightning.force.com/', tbl.opp_id) || ')' as opp_link,     
      tbl.ca_name,
      tbl.ca_pr_name,
      tbl.ca_mode,
      date(tbl.ca_last_seen) as ca_last_seen,
      tbl.ca_sw_version,
      tbl.ca_tot_hosts,
      tbl.ca_active_hosts,
      tbl.ca_x95_hosts_tot,
      tbl.ca_traffic,
      tbl.ca_uiconfig
  from
      tbl
    where
      tbl.stg_name like 'Closed Won%'
      and tbl.arr is not null
    group by
    tbl.acct_name,
    tbl.opp_id,
    tbl.ca_name,
    tbl.ca_pr_name,
    tbl.ca_mode,
    tbl.ca_last_seen,
    tbl.ca_sw_version,
    tbl.ca_tot_hosts,
    tbl.ca_active_hosts,
    tbl.ca_x95_hosts_tot,
    tbl.ca_traffic,
    tbl.ca_uiconfig) df 

  WHERE ca_last_seen >= DATEADD(MONTH, -3, GETDATE())
  limit 5"""



  con = psycopg2.connect(conn_string)
  client2 = boto3.client('sns')
  with con.cursor() as cur:
    # Enter the query that you want to execute
    cur.execute(sql_query)
    for row in cur:
      df = pd.DataFrame.from_records(cur.fetchall(), columns = [desc[0] for desc in cur.description])
      df['Time_Stamp'] = pd.to_datetime('now', utc=True)
      df['ca_active_hosts'] = df['ca_active_hosts'].astype('Int64', errors='ignore')
      df['ca_active_hosts'].fillna(0, inplace=True)
      #print(df.iloc[0])
      #if (df.iloc[0]['ca_active_hosts'].notna()):
      if (df['ca_active_hosts'] >= 0).all():
        print('the file is present, going to send notifaction')
        response = client2.publish(
        TopicArn = 'arn:aws:sns:us-west-1:##########:email-data-lake',
        Message = 'Warning User active_hosts is '   str(df['Time_Stamp']),
        Subject = 'User Warning')
      else:
        print('the file is not present')
    #cur.close()

Is there anything else in code/connection I need to change? Feel I have exhausted all that I can find online being new to SNS

CodePudding user response：

I imagine that your lambda function does not have any internet connectivity.

Thus, a connection timeout issue indicates that the network interface associated with your lambda function is unable to talk to the service.

To fix this, create a VPC interface endpoint for sns.us-west-1.amazonaws.com in the same subnet as that of the lambda's network interface.