Simpler.Grants.gov Public Wiki
Grants.govSimpler.Grants.govGitHubDiscourse
  • 👋Welcome
  • GET INVOLVED
    • Why open source?
    • How to contribute code
    • How to file issues
      • Report a bug
      • Request a feature
      • Report a security vulnerability
    • Community guidelines
      • Code of Conduct
      • Reporting and removing content
      • Incident response protocol
    • Community events
      • Fall 2024 Coding Challenge
        • Event Submissions & Winners
      • Spring 2025 Collaborative Coding Challenge
        • Event Submissions & Winners
    • Communication channels
  • Product
    • Roadmap
    • Deliverables
      • 🏁Static site soft launch
      • 🏁Static site public launch
      • 🏁GET Opportunities
      • 🏁Open source onboarding
      • 🏁Co-Design Group planning
    • Decisions
      • ADR Template
      • ADRs
        • Dedicated Forum for Simpler.Grants.gov Community
        • Recording Architecture Decisions
        • Task Runner for the CI / CD Pipeline
        • API Language
        • Use Figma for design prototyping
        • ADR: Chat
        • DB Choices
        • API Framework and Libraries
        • Back-end Code Quality Tools
        • Front-end Language
        • Communications Tooling: Wiki Platform
        • Use Mural for design diagrams and whiteboarding
        • Ticket Tracking
        • Front-end Framework
        • Front-end Code Quality Tools
        • Front-end Testing & Coverage
        • Backend API Type
        • Front-end Testing & Coverage
        • Deployment Strategy
        • Use U.S. Web Design System for components and utility classes
        • FE server rendering
        • Use NPM over Yarn Architectural Decision Records
        • U.S. Web Design System in React
        • Communications Tooling: Video Conferencing
        • Back-end Production Server
        • Communications Tooling: Analytics Platform
        • Commit and Branch Conventions and Release Workflow
        • Cloud Platform to Host the Project
        • Infrastructure as Code Tool
        • Data Replication Strategy & Tool
        • HHS Communications Site
        • Communications Tooling: Email Marketing
        • Communications Tooling: Listserv
        • Use Ethnio for design research
        • Uptime Monitoring
        • Database Migrations
        • 30k ft deliverable reporting strategy
        • Public measurement dashboard architecture
        • Method and technology for "Contact Us" CTA
        • E2E / Integration Testing Framework
        • Logging and Monitoring Platform
        • Dashboard Data Storage
        • Dashboard Data Tool
        • Search Engine
        • Document Storage
        • Document Sharing
        • Internal Wiki ADR
        • Shared Team Calendar Platform
        • Cross-Program Team Health Survey Tool
        • Adding Slack Users to SimplerGrants Slack Workspace
        • Repo organization
        • Internal knowledge management
        • Migrate Existing API Consumers
      • Infra
        • Use markdown architectural decision records
        • CI/CD interface
        • Use custom implementation of GitHub OIDC
        • Manage ECR in prod account module
        • Separate terraform backend configs into separate config files
        • Database module design
        • Provision database users with serverless function
        • Database migration architecture
        • Consolidate infra config from tfvars files into config module
        • Environment use cases
        • Production networking long term state
    • Analytics
      • Open source community metrics
      • API metrics
  • DESIGN & RESEARCH
    • Brand guidelines
      • Logo
      • Colors
      • Grid and composition
      • Typography
      • Iconography
      • Photos and illustrations
    • Content guidelines
      • Voice and tone
    • User research
      • Grants.gov archetypes
  • REFERENCES
    • Glossary
  • How to edit the wiki
Powered by GitBook
On this page
  • Context and Problem Statement
  • Decision Drivers
  • Considered Options
  • Decision Outcome: AWS Lambda function
  • Pros and Cons of the Options
  • Terraform
  • Shell scripts
  • Jump host using EC2
  • Container task using ECS
  • Serverless function using Lambda

Was this helpful?

Edit on GitHub
  1. Product
  2. Decisions
  3. Infra

Provision database users with serverless function

PreviousDatabase module designNextDatabase migration architecture

Last updated 1 year ago

Was this helpful?

  • Status: proposed

  • Deciders: @lorenyu @kyeah @shawnvanderjagt @rocketnova

  • Date: 2023-05-25

Context and Problem Statement

What is the best method for setting up database users and permissions for the application service and the migrations task?

Decision Drivers

  • Minimize number of steps

  • Security and compliance

Considered Options

  • Terraform – Define users and permissions declaratively in Terraform using the . Apply changes from infrastructure engineer's local machine or from the CI/CD workflow. When initially creating database cluster, make database cluster publicly accessible and define security group rules to allow traffic from local machine or GitHub actions. After creating database users, reconfigure database cluster to make database private.

  • Shell scripts – Define users and permissions through a shell script. This could use tools like psql or not. It could also define the permissions in a .sql file that gets executed. Similar to the terraform option, the database would need to be made accessible to the machine running the script. One way to do this is for the script itself to temporarily enable access to the database using AWS CLI.

  • Jump host using EC2 – Run terraform or a shell script but from an EC2 instance within the VPC. Create the EC2 instance and set up network connectivity between the EC2 instance and the database cluster as part of creating the database infrastructure resources.

  • Container task using ECS – Build a Docker image that has the code and logic to provision users and permissions and run the code as an ECS task.

  • Serverless function using Lambda – Write code to provision database users and permissions and run it as a Lambda function.

Decision Outcome: AWS Lambda function

A Lambda function is the simplest tool that can operate within the VPC and therefore get around the obstacle of needing network access to the database cluster. EC2 instances are too expensive to maintain for rarely used operations like database user provisioning, and ECS tasks add complexity to the infrastructure by requiring an additional ECR image repository and image build step.

Pros and Cons of the Options

Terraform

Pros

  • Declarative

  • Could create database cluster and database users in a single terraform apply

Cons

  • The database needs to be publicly accessible to the machine that is running the script – either the infrastructure engineer's local machine or the continuous integration service (e.g. GitHub Actions). First, this causes the database setup process to take a minimum of three steps: (1) create the database cluster with publicly accessible configuration, (2) provision the database users, (3) make the database cluster private. Second, even if it is an acceptable risk to make the database publicly accessible when it is first created and before it has any data, it may not be an acceptable risk to do so once the system is in production. Therefore, after the system is in production, there would no longer be a way to reconfigure the database users or otherwise maintain the system using this approach.

  • Need to modify the database cluster configuration after creating it in order to make it private. Modifications requires an additional step, and may also require manual changes to the terraform configuration.

Shell scripts

Pros

  • Simple

  • Can represent user configuration as a .sql script which could simplify database management by keeping it all within SQL

Cons

  • Same as the cons for Terraform – the database needs to be accessible to the machine running the script

Jump host using EC2

Pros

  • Can leverage the Terraform and Shell scripts approaches

  • Can access the database securely from within the VPC without making the database cluster publicly accessible

Cons

  • Added infrastructure complexity due to the need to maintain an EC2 instance

Container task using ECS

Pros

  • Flexible: can build everything needed in a Docker container, including installing necessary binaries and bundling required libraries and code

  • Can access the database securely from within the VPC without making the database cluster publicly accessible

Cons

  • Increases complexity of terraform module architecture. There needs to be an ECR repository to store the Docker images. The ECR repository could be in a separate root module, which adds another layer to the module architecture. The ECR repository could be put in the build-repository root module, which would would clutter the build-repository since it's not related to application builds. Or it could be put in the database root module and be manually created using terraform's -target flag, but that adds complexity to the setup process.

  • Increases number of steps needed to set up the database by at least two, one to create the ECR repository and one to build and publish the Docker image to the ECR repository, before creating the database cluster resources.

Serverless function using Lambda

Pros

  • Flexible: can build many things in a Lambda function

  • Can access the database securely from within the VPC without making the database cluster publicly accessible

  • Relatively simple

Cons

  • Adds a new dependency to the application setup process. The setup process will now rely on the programming language used by the Lambda function (Python in this case).

  • Can't easily use custom external binaries in AWS Lambda. So will rely mostly on code rather than lower level scripts like psql.

PostgreSQL provider