search

linz / bde-fdw-rds

Python AWS cdk to create rds with fdw for BDE analytica queries
MIT License
0 stars 0 forks source link

readme

AWS RDS with FDW module enabled for BDE analytics queries

GitHub Actions Status Coverage: 100% branches Kodiak Dependabot Status License Conventional Commits Code Style Imports: isort Checked with mypy Code Style: prettier

This CDK creates an RDS instance in AWS with Postgres FDW module enabled. Its main purpose is to allow LINZ analysts to query the production BDE database. Direct connection to the production RDS instance is not ideal since users should not be allowed to write or alter the database directly. This repository works around this limitation by creating a separate RDS with FDW module, to allow analysts to query the production BDE database remotely and save their changes locally (RDS instance created by this repo).

How it works

Each analyst will have access to their own schema in the RDS instance deployed by this repository. They can perform a cross database query to the production BDE database and save their changes locally within their own designated schema. Such separation of concern will limit the risk of an analyst accidentally taking down the production BDE database.

Prerequisite

A read-only user needs to exist in the production BDE database to allow connection from the RDS instance created here.

Resources created by this repository should be deployed in the same VPC and subnets hosting the production BDE processor database. Deploying this CDK in another AWS account has been considered, but ultimately decided against since doing so will only add additional technical debt to the existing legacy application.