Skip to main content

3. Database Setup

Roost stores the status of the GPT workflow and other relevant information in Database. Roost supports MySQL, Postgres and Amazon Aurora DB. Any one database is needed for RoostGPT to work.

Below are the steps to setup an RDS in AWS

Amazon Aurora (MySQL Compatible) OR MySQL

  1. Select RDS

  2. Choose Create Database

  3. Select “Easy Create” for “Amazon Aurora with MYSQL compatibility" or "MySQL"

  4. Modify the RDS Security Group to allow TCP port 3306 access to the Control plane Instance security group only

  5. Make a note of the writer instance database end-point, user, and password (It is needed later in the config below)

Create a new user with read-write privileges and avoid using an admin login.

# Sample command to create a user using MySQL CLI 
# Provide password on prompt 

mysql -h <SQL Host URL> -u <root|master|admin> -p
CREATE USER 'Roost'@'%' identified WITH mysql_native_password by 'Roost#123'; 
CREATE DATABASE roostio;
GRANT ALL on roostio.* to 'Roost'@'%';

# Execute the Roost Schema file, if available
\. /var/tmp/Roost/db/roost.sql

 

Amazon Aurora (PostgreSQL Compatible) OR PostgreSQL

  1. Select RDS

  2. Choose Create Database

  3. Select “Easy Create” for “Amazon Aurora with PostgreSQL compatibility" or "PostgreSQL"

  4. Modify the RDS Security Group to allow TCP port 5432 access to the Control plane Instance security group only

  5. Make a note of the writer instance database end-point, user, and password (It is needed later in the config below)

  6. Create a new user with read-write privileges and avoid using an admin login.

psql "host=<PG Host URL> user=<admin> dbname=postgres port=5432 sslmode=require"

CREATE DATABASE roostio;                                  -- creates app database [5]
CREATE USER roost WITH PASSWORD 'Roost#123';              -- creates login role [3]
GRANT ALL PRIVILEGES ON DATABASE roostio TO roost;        -- DB-level grant [4]

-- Connect to the new DB (reconnect as admin or roost), then set schema privileges
\c roostio                                        
GRANT USAGE ON SCHEMA public TO roost;                   
GRANT ALL ON ALL TABLES IN SCHEMA public TO roost;     
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO roost;   
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT ALL ON TABLES TO roost;                       
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT USAGE ON SEQUENCES TO roost;                   

-- Execute the Roost Schema file, if available
\i /var/tmp/Roost/db/roost.sql

 

Back to top