Database Setup

ICC requires the following databases for the system to operate correctly.

control_plane
activities
user_manager
cron
compliance
scaler
risk_cold_storage
traffic_inspector

Prerequisites

PostgreSQL server running on your target server
Superuser access to PostgreSQL
Replace YOUR_SERVER_URL with your actual server hostname/IP

Save the install script to your local

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS dblink;

-- Create databases
CREATE DATABASE activities;
CREATE DATABASE cluster_manager;
CREATE DATABASE compliance;
CREATE DATABASE control_plane;
CREATE DATABASE cron;
CREATE DATABASE risk_cold_storage;
CREATE DATABASE scaler;
CREATE DATABASE traffic_inspector;
CREATE DATABASE user_manager;

-- Create single user for all databases
DO $$
DECLARE
  password text;
BEGIN
  password := encode(gen_random_bytes(24), 'base64');
  IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = 'platformatic') THEN
    EXECUTE format('CREATE USER platformatic WITH PASSWORD %L', password);
  END IF;
END $$;

-- Configure db permissions
DO $$
DECLARE
    db RECORD;
    cmd TEXT;
BEGIN
  FOR db IN SELECT 'platformatic' as username, dbname FROM ( values
  ('activities'), ('cluster_manager'), ('risk_cold_storage'), ('compliance'),
  ('control_plane'), ('cron'), ('scaler'), ('traffic_inspector'), ('user_manager')) as
  s(dbname) LOOP
    cmd := format(
      'GRANT ALL ON SCHEMA public TO %I; '
      'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I; '
      'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I; '
      'GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO %I; '
      'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I; '
      'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I; '
      'ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO %I;',
      db.username, db.username, db.username, db.username, db.username, db.username, db.username
    );

    -- TODO executor should update the credentials to the Superuser
    PERFORM dblink_exec(
      format('host=/tmp user=postgres password=postgres dbname=%s', db.dbname),
      cmd
    );
  END LOOP;
END $$;

-- base64 can have a / which is a special character in pg connection strings
-- so we do a simple replace with URL encoding
SELECT dbname, username, replace(password, '/', '%2F') FROM user_creds;

Modifications to make

Before running this script, make these changes for it to execute correctly.

At PERFORM dblink_exec, update with the Superuser credentials
If desired, increase the number in gen_random_bytes

Execute the script

psql \
  --host=YOUR_SERVER_URL \
  --username=super_username \
  --password \
  --file=installer.sql

After execution. the database credentials will be printed. These are required for connecting to the databases.

Secrets

Replace YOUR_SERVER_URL with your actual server hostname/IP and 5432 with your PostgreSQL port if different:

# Environment Variables for Database Connections
PLT_ACTIVITIES_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/activities"
PLT_CLUSTER_MANAGER_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/cluster_manager"
PLT_COLD_STORAGE_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/cold_storage"
PLT_COMPLIANCE_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/compliance"
PLT_CONTROL_PLANE_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/control_plane"
PLT_CRON_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/cron"
PLT_SCALER_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/scaler"
PLT_TRAFFICANTE_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/trafficante"
PLT_USER_MANAGER_DATABASE_URL="postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/user_manager"

If using the Helm chart for installation, set services.icc.database_url with the connection string, ignoring any database name. A partial Helm values YAML:

services:
    icc:
        database_url: postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432

Verification Commands

To verify that the user works, you can test the connection to each database.

# Test connections
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/control_plane" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/activities" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/user_manager" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/cron" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/compliance" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/scaler" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/cold_storage" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/trafficante" -c "SELECT current_database(), current_user;"
psql "postgresql://platformatic:SecurePass123@YOUR_SERVER_URL:5432/cluster_manager" -c "SELECT current_database(), current_user;"