Audit Service - RDS

Audit Service or Round 2 Fight

Description

When I started to think more critically about how interactions would work inside

dynamodb for auditing I quickly learned about pain. Lots and lots of pain. Including

the fact id probably end up having to use dynamodb streams and couple that with elasticsearch.

So im not going to even entertain that idea and am going down the ol trusty sql route.

Creating the database

Im still on an older version of terraform, so public shamming for being on 0.12.6.

IaaC section

tfenv install 0.12.6
tfenv use 0.12.6

I had already done an tf import from a previous db table, so I just copied and pasted then

modified the database values. Note the xxxx's below are redacted text so change that if you

are copy and pasting.

resource "random_password" "password" {
  length           = 16
  special          = true
  override_special = "_%@"
}

resource "aws_db_instance" "aduit_db" {
    allocated_storage                     = 20
    auto_minor_version_upgrade            = true
    availability_zone                     = "xxxxx"
    backup_retention_period               = 7
    backup_window                         = "09:12-09:42"
    copy_tags_to_snapshot                 = true
    db_subnet_group_name                  = "default-vpc-d7c845b3"
    deletion_protection                   = true
    enabled_cloudwatch_logs_exports       = []
    engine                                = "postgres"
    engine_version                        = "12.5"
    iam_database_authentication_enabled   = false
    identifier                            = "audit"
    instance_class                        = "db.t3.micro"
    max_allocated_storage                 = 1000
    monitoring_interval                   = 60
    multi_az                              = false
    option_group_name                     = "default:postgres-12"
    parameter_group_name                  = "default.postgres12"
    performance_insights_enabled          = true
    performance_insights_retention_period = 7
    port                                  = 5432
    publicly_accessible                   = true
    security_group_names                  = []
    skip_final_snapshot                   = true
    storage_encrypted                     = true
    storage_type                          = "gp2"
    tags                                  = {}
    username                              = "postgres"
    password                              = random_password.password.result
    vpc_security_group_ids                = [
        "sg-0267a264",
    ]

    timeouts {}
}

output "password" {
  description = "The password is:"
  value = random_password.password.*.result
}

Goose time / Local Dev

Inside the project, im creating a table's folder then setting up goose.

=> goose

its as easy as running go get -u github.com/pressly/goose/cmd/goose. Also, I needed to set up a testing

postgres instance.

Quick Docker Setup

localdb.compose.yaml

version: '3.5'

volumes:
  pgdata: {}

services:
  db:
    image: postgres
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=shhh
      - POSTGRES_DB=audit_dev
    volumes:
      - pgdata:/var/lib/postgresql/data

  adminer:
    image: adminer
    ports:
      - 8000:8080

then run docker-compose -f localdb.compose.yaml up and you should have a local copy of postgres running

Back to goose

Please note I cheated and created the database manually, I just don't like accidentally dropping the entire db if

it can be helped. Otherwise... a quick mkdir for 'tables' and then added

001_create_events_table.sql

-- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE events (
    id            INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id       UUID NOT NULL,
    company_name  VARCHAR(255) NOT NULL,
    event         TEXT NOT NULL,
    created_on    TIMESTAMP NOT NULL,
    updated_on    TIMESTAMP NOT NULL
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
DROP TABLE events;
-- +goose StatementEnd

The extension is for using UUID

So now that the database can be mucked with it was easy enough to run