week4.md

Week 4 — Postgres and RDS

Table of Contents

• Required Homework

  • Create RDS Postgres Instance
  • Implement Security Group for RDS
  • Bash scripting for common database actions
  • Install Postgres Driver in Backend Application
  • Connect Gitpod to RDS Instance
  • Create Cognito Trigger to insert user into database
  • Create new activities with a database insert

  Required Homework

  Create RDS Postgres Instance

  • Provisioned a AWS RDS Postgres instance

  

  

  ---

  Implement Security Group for RDS

  • Security Group was created to allow for inbound traffic via the Postgres port 5432 which from the GITPOD endpoint

  

  ---

  Bash scripting for common database actions

  • Created bash scripts for e.g. inserts, create and activities that require database interactions
    • home
    • create
    • object
    • schema

  ---

  Install Postgres Driver in Backend Application

  • Setup Postgres client / driver

    • Added the RDS endpoint via an env var to GITPOD

      backend-flask:
    environment:
      CONNECTION_URL: "${CONNECTION_URL}"
    

    • Added the postgres drivers to the requirements.txt file

    psycopg[binary]
    psycopg[pool]
    

    • Installed the drivers using pip install -r requirements.txt

  ---

  Connect Gitpod to RDS Instance

  • To connect the RDS Postgres instannce to GITPOD, an AWS Security Group had to be created

  

  • Added the endpoint into the docker-compose.yml file via the entry below, where PROD_CONNECTION_URL is the AWS RDS endpoint

      backend-flask:
      environment:
        CONNECTION_URL: "${PROD_CONNECTION_URL}"
    

• Script to automatically connect GITPOD to the AWS RDS instance

  • Map GITPOD endpoint to Security Group & Autoconnect to RDS Instance

• Updated AWS Security Group automatically based on the backend and map the GITPOD endpoint to the AWS RDS Security Group

    command: |
     export GITPOD_IP=$(curl ifconfig.me)
     source "$THEIA_WORKSPACE_ROOT/backend-flask/db-update-sg-rule"

--security-group-rules "SecurityGroupRuleId=$DB_SG_RULE_ID,SecurityGroupRule={Description=GITPOD,IpProtocol=tcp,FromPort=5432,ToPort=5432,CidrIpv4=$GITPOD_IP/32}"

---

Create Cognito Trigger to insert user into database

• Created Lambda function for this task

• Newly created user after signup via the Cruddur page as well as validation and confirmation of email (username)

---

Create new activities with a database insert

• Newly created posts via the Cruddur page

• Post Creation of activity (by checking the database)