Database Setup

ICC assumes that a postgres DB server is available, with these DBs:

• control_plane
• activities
• user_manager
• cron
• compliance
• scaler
• cold_storage
• trafficante

Prerequisites

• PostgreSQL server running on your target server
• Superuser access to PostgreSQL (typically postgres user)
• Replace YOUR_SERVER_URL with your actual server hostname/IP

1. 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 plt_activities;
CREATE DATABASE plt_cluster_manager;
CREATE DATABASE plt_cold_storage;
CREATE DATABASE plt_compliance;
CREATE DATABASE plt_control_plane;
CREATE DATABASE plt_cron;
CREATE DATABASE plt_scaler;
CREATE DATABASE plt_trafficante;
CREATE DATABASE plt_user_manager;

-- Get a lsit of databases and generate the usernames
CREATE TEMPORARY TABLE dbs AS
  SELECT
    datname as dbname,
    CONCAT(datname, '_user') as username
  FROM pg_database
  WHERE
    datistemplate = false
    AND datname LIKE 'plt_%';

-- Temporarily store credentials
CREATE TEMPORARY TABLE user_creds (
  dbname TEXT,
  username TEXT,
  password TEXT
);

-- Configure databases and users
DO $$
DECLARE
  db record;
  password text;
BEGIN
  FOR db IN SELECT * from dbs LOOP
    -- Create user
    password := encode(gen_random_bytes(24), 'base64');
    EXECUTE format('CREATE USER %I WITH PASSWORD %L', db.username, password);

    -- Grant full privileges to user on their database
    EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', db.dbname, db.username);

    -- Make users owners of their respective databases
    EXECUTE format('ALTER DATABASE %I OWNER TO %I', db.dbname, db.username);

    -- Store username and password
    INSERT INTO user_creds (dbname, username, password) VALUES (db.dbname, db.username, password);
  END LOOP;
END $$;

-- Configure db permissions
DO $$
DECLARE
    db RECORD;
    cmd TEXT;
BEGIN
  FOR db IN SELECT * FROM dbs 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

1. Add additional databases to the top of the file
2. At PERFORM dblink_exec , update with the Superuser credentials
3. If necessary, increase the number in gen_random_bytes

2. 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.

6. 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://activities_user:ACT_SecurePass123!@YOUR_SERVER_URL:5432/activities"
PLT_CLUSTER_MANAGER_DATABASE_URL="postgresql://plt_cluster_manager_user:somepassword@YOUR_SERVER_URL:5432/cluster_manager"
PLT_COLD_STORAGE_DATABASE_URL="postgresql://cold_storage_user:CS_SecurePass123!@YOUR_SERVER_URL:5432/cold_storage"
PLT_COMPLIANCE_DATABASE_URL="postgresql://compliance_user:COMP_SecurePass123!@YOUR_SERVER_URL:5432/compliance"
PLT_CONTROL_PLANE_DATABASE_URL="postgresql://control_plane_user:CP_SecurePass123!@YOUR_SERVER_URL:5432/control_plane"
PLT_CRON_DATABASE_URL="postgresql://cron_user:CRON_SecurePass123!@YOUR_SERVER_URL:5432/cron"
PLT_SCALER_DATABASE_URL="postgresql://scaler_user:SCALER_SecurePass123!@YOUR_SERVER_URL:5432/scaler"
PLT_TRAFFICANTE_DATABASE_URL="postgresql://trafficante_user:TRAF_SecurePass123!@YOUR_SERVER_URL:5432/trafficante"
PLT_USER_MANAGER_DATABASE_URL="postgresql://user_manager_user:UM_SecurePass123!@YOUR_SERVER_URL:5432/user_manager"

7. Verification Commands

Test each connection:

# Test connections
psql "postgresql://control_plane_user:CP_SecurePass123!@YOUR_SERVER_URL:5432/control_plane" -c "SELECT current_database(), current_user;"
psql "postgresql://activities_user:ACT_SecurePass123!@YOUR_SERVER_URL:5432/activities" -c "SELECT current_database(), current_user;"
psql "postgresql://user_manager_user:UM_SecurePass123!@YOUR_SERVER_URL:5432/user_manager" -c "SELECT current_database(), current_user;"
psql "postgresql://cron_user:CRON_SecurePass123!@YOUR_SERVER_URL:5432/cron" -c "SELECT current_database(), current_user;"
psql "postgresql://compliance_user:COMP_SecurePass123!@YOUR_SERVER_URL:5432/compliance" -c "SELECT current_database(), current_user;"
psql "postgresql://scaler_user:SCALER_SecurePass123!@YOUR_SERVER_URL:5432/scaler" -c "SELECT current_database(), current_user;"
psql "postgresql://cold_storage_user:CS_SecurePass123!@YOUR_SERVER_URL:5432/cold_storage" -c "SELECT current_database(), current_user;"
psql "postgresql://trafficante_user:TRAF_SecurePass123!@YOUR_SERVER_URL:5432/trafficante" -c "SELECT current_database(), current_user;"
psql "postgresql://cluster_manager_user:CM_SecurePass123!@YOUR_SERVER_URL:5432/cluster_manager" -c "SELECT current_database(), current_user;"