I tried ChatOps with Slack x API Gateway x Lambda (Python) x RDS

background

--There was a request to input Seed data (re-insert test data) into RDS of the verification environment (AWS) at any time. ――Since it is easiest to do from slack (it is easy to not depend on the development environment of the developer!), We created a mechanism to reflect the data from slack to RDS of the verification environment.

Technology stack used

• Lambda
• S3
• RDS --slack (technical stack ..?)

Premise

For the sake of brevity, the following has already been done and we will proceed.

--Seed file already exists in S3 --I think there is a way to clone the Seed file from github, but since the implementation around authentication seems to be troublesome, I put the Seed file in S3 --The RDS instance to which Seed is sent already exists.

Overall picture

By executing the command from slack to the bot with the following feeling, the data is reflected in the verification environment and the completion notification is returned to slack.

Implementation

① Make lambda

Create a function

--Open Create Function from lambda to create a new function --Select Create from scratch --Create a new execution role --I want to access S3, so I will grant S3 access authority after creating it (such as Amazon S3 FullAccess)

-** [Stumbling point] ** Since lambda accesses RDS, open the edit screen and specify the same VPC as RDS from Network at the bottom. --Even if you access RDS from lambda without setting this, it will time out ...

-** [Stumbling point] ** I installed Lambda in the same VPC as RDS to access RDS above, but since I can not access S3 from that state by default, VPC end for S3 referring to the following Make points -How to access S3 from Lambda in VPC

-** [Stumbling point] ** When returning the execution result from lambda to slack, lambda returns a message via the Internet, but for that purpose the specified VPC is a NAT gateway (or NAT instance) to the public subnet. You need to have, so please set by referring to the following --How do I allow internet access to my VPC's Lambda function? --If you do not do this, you will not be able to access the Internet from lambda and you will not be able to notify slack of the processing result (in lambda it will be timeout ..)

Write python code

As a process, it looks like this. The constant is like reading the value set in the environment variable of lambda.

• Because I am new to Python, please do not use Masakari ..

lambda_function.py

import json
import logging
import os
import psycopg2
import boto3
import re
import urllib.request

logger = logging.getLogger()
logger.setLevel(logging.DEBUG)

#PostgreSQL connection settings
def get_pg_con():
  return psycopg2.connect(
    host=os.getenv('DB_HOST'),
    database=os.getenv('DB_NAME'),
    port=os.getenv('DB_PORT'),
    user=os.getenv('DB_USER'),
    password=os.getenv('DB_PASSWORD'))

#Get Seed files from S3
def get_seed_file_from_s3():
  bucket_name = os.getenv('S3_BUCKECT_NAME')
  file_name = os.getenv('S3_TARGET_FILE_NAME')
  s3 = boto3.client('s3')
  response = s3.get_object(Bucket=bucket_name, Key=file_name)
  return response['Body'].read().decode('utf-8')

#Run Seed file to RSD
def exec_seed():
  str_sql = get_seed_file_from_s3()
  conn = get_pg_con()
  cur = conn.cursor()
  cur.execute("BEGIN")
  #If you insert the file obtained from S3, the SQL in the file will be executed from above.
  cur.execute(str_sql)
  conn.commit()
  cur.close()
  conn.close()

#Reply to slack
def post_message_to_channel(channel, message):
  url = "https://slack.com/api/chat.postMessage"
  headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer {0}".format(os.getenv('SLACK_BOT_USER_ACCESS_TOKEN'))
  }

  data = {
    "token": os.getenv('SLACK_BOT_USER_ACCESS_TOKEN'),
    "channel": channel, #Notify the channel that executed Seed
    "text": message,
  }
  req = urllib.request.Request(url, data=json.dumps(data).encode("utf-8"), headers=headers, method="POST")
  with urllib.request.urlopen(req) as response:
    #Write the execution result in the log
    response_body = response.read().decode("utf-8")
    logger.info(response_body)

#Check the token sent when slack hits lambda
def is_verified_token(event):
  token = event.get("token")
  if token != os.environ["SLACK_BOT_VERIFY_TOKEN"]:
    return False
  return True

#Check if you put Seed execution keyword in slack
#This time"run"Execute Seed if the wording is included
def is_mached_pattern(text):
  pattern = r"run"
  result = re.search(pattern, text)
  if result:
    return True
  return False

#Entry point
def lambda_handler(event, context):
  #For the time being, plunge the request contents into the log
  logging.info(json.dumps(event))

  #Authentication of Slack's Event API
  if "challenge" in event:
    return event["challenge"]

  #Check token
  if not is_verified_token(event):
    logger.info("token is not verified!!!")
    return "OK"

  target_channel = event.get("event").get("channel")
  #In the message received from slack"run"Returns INFO information if there is no wording
  request_text = event.get("event").get("text")
  if not is_mached_pattern(request_text):
    post_message_to_channel(target_channel, "*[INFO]*Write how to use lightly:muscle:")
    return

  post_message_to_channel(target_channel, "I'll do it!")

  try:
    exec_seed()
    post_message_to_channel(target_channel, "Has completed!:rocket:")
  except Exception as e:
    logger.info(e)
    post_message_to_channel(event.get("event").get("channel"), e)

--This time, I want to access RDS (assuming PostgreSQL), so I need a library called psycopg2. Except for the standard library that Python has, it is necessary to zip it together and upload it to lambda, so refer to the following and zip the above Python file and psycopg2 in the local environment and upload it to lambda.

• No module named 'psycopg2._psycopg': ModuleNotFoundError in AWS Lambda --It is easy to get the psycopg2 library by referring to here (introduced in the answer) -Load external module in lambda --How to create a zip file for uploading to lambda

--When uploading with zip, specify the entry point to be executed at lambda startup in handler with file name.method name. --You can see that the uploaded file contains psycopg2.

② Make API Gateway

--In order to execute lambda from slack, it is necessary to access via API Gateway, so create API Gateway --At this time, API Gateway and lambda will also be linked. ――Specifically, I think it is very easy to understand if you refer to the following -[Try ChatOps with Slack and Lambda Step 2: Create API Gateway](https://qiita.com/yasuabe2613/items/6219d243ffcfe7690e78#%E3%82%B9%E3%83%86%E3%83%83 % E3% 83% 97% EF% BC% 92-api-gateway-% E4% BD% 9C% E6% 88% 90)

--The created API Gateway tests against lambda and returns a response, which is ready.

③ Make a slack app

--Create an app for bots from https://api.slack.com/apps --At this time, we will also link slack and API Gateway. --The specific procedure is Slack of [AWS (API Gateway + Lambda (Python)) + Bot creation using Slack API](https://nmmmk.hatenablog.com/entry/2018/10/10/001548) The app creation is very easy to understand

④ Check the operation

――With the following feeling, hit the bot from slack and get a reply and you're done! --Invite the bot to the target channel and type the command (this time I made a bot named @ genkan-dev-seed)

Impressions

――By installing lambda in a specific VPC, I stumbled upon various things around the network (basic important!) .. ――If you can execute it from slack, it is very convenient because there is no environment difference for each executor!

Other references

Around psycopg2 (where Seed is executed from Python to RDS)

-I tried connecting to PostgreSQL with Python -Python sample that executes the contents of the SQL file given as an argument to the PostgreSQL DB -I tried throwing a variable SQL statement from Python code to PostgreSQL

Cooperation between slack and lambda

-Slack app_mention specifications (official)