Confirm Schema provisioning sql commands

[JasonEb]

Dependency

• The Terraform Migration Epic #20

Overview

For the People Depot database to be provisioned on to the Incubator rds, it'll need it's own schema to isolate it's data. We would like for People Depot developers to run and confirm the sql commands for provisioning a schema and database on a local instance.

SQL Commands

  -- Create new schema for new DB
  CREATE SCHEMA {new_db} AUTHORIZATION {db_owner};
  -- Set search_path for the new user
  ALTER ROLE {db_owner} SET search_path TO {new_db};

  -- Remove ability for all users to do everything in public schema
  REVOKE ALL ON SCHEMA public FROM public;
  -- Ensure users can list down objects in public schema
  GRANT USAGE ON SCHEMA public TO public;

Action Items

• Test SQL Commands
• Collect preferred schema name, and database username for People-Depot
  • NB: The intended environment is dev. So the schema name would be like people-depot-dev

Resources/Instructions

https://github.com/hackforla/incubator/blob/main/terraform-modules/multi-tenant-database/files/database.py#L41

• Related issue Cognito client secrets - API client peopledepot#174

[ExperimentsInHonesty]

@drakeredwind01 Please ask DevOps if this is still relevant or required by incubator. Then talk to us at PD about it at our next meeting

[fyliu]

Background: the goal of this issue was to figure out how database multi tenancy worked in the terragrunt setup. We wanted to be able to get new projects deployed ourselves (Ops) without having to bother Darren Pham for it.

If the current Terraform setup already does this or have an alternative solution for multi tenancy, then this issue is not needed.

[ale210]

This should be a terraform resource. No SQL commands necessary. Credentials should be scoped to this schema

[ale210]

Incubator provisions PostgreSQL databases now through terraform, onto the shared RDS instance. Using the owner credentials generated, a user can create multiple schemas.

If the purpose of this question is to ask how database resources are isolated across projects in incubator, terraform creates databases with roles attached for that database. If data isolation is the issue, no additional commands are necessary. However, within their own database, users can create as many schemas as they need.