Database Security Strategies

Database Security Strategies

TMI uses GORM AutoMigrate to manage database schema, which requires DDL permissions (CREATE TABLE, ALTER TABLE). Running the application server with admin-level database access is a security risk -- if the server is compromised, the attacker inherits those permissions. Defense-in-depth means limiting the blast radius by giving the server only what it needs (SELECT, INSERT, UPDATE, DELETE).

This page describes two strategies for managing database privileges. Choose the one that fits your deployment model.

Strategy 1: Cloud-Isolated Database

Recommended for cloud deployments.

Deploy the database in an isolated subnet, accessible only by the TMI server:

• Do not allow any other systems or users to connect to the database -- no reporting connections, no additional user accounts
• Enable DDL auditing on the database server (full auditing is better if practical)
• Configure the server with admin-level database credentials -- the server handles schema maintenance automatically at startup via AutoMigrate

Best for: Heroku, AWS RDS in private subnet, OCI Autonomous Database with private endpoint, Azure Database for PostgreSQL in VNet, GCP Cloud SQL with private IP.

Trade-off: The server has DDL permissions, but the database is network-isolated so the attack surface is minimal.

Upgrade workflow:

1. Deploy new server version
2. Server auto-migrates on startup
3. Done

Strategy 2: Least-Privilege Server

Recommended for on-premises deployments.

Create a limited-privilege database user for the server with only DML access:

• Enable full auditing on the database server
• Create a limited-privilege database user for the server with only SELECT, INSERT, UPDATE, DELETE permissions
• On first install and on each server upgrade, the operator runs tmi-dbtool --schema --config=<config-file> with admin-privileged database credentials
• The server detects missing DDL permissions at startup: if the schema is current, it proceeds with a warning; if the schema needs updates, it stops with a clear error message directing the operator to run tmi-dbtool

Best for: Shared database servers, on-premises PostgreSQL or Oracle, environments with compliance requirements for privilege separation.

Trade-off: Adds a manual step to the upgrade workflow.

Upgrade workflow:

1. Run tmi-dbtool --schema --config=<config-file> with admin credentials
2. Deploy new server version
3. Server validates schema and starts

Creating a Limited-Privilege Database User

PostgreSQL

-- Create the application user
CREATE USER tmi_app WITH PASSWORD 'your-secure-password';

-- Grant connection
GRANT CONNECT ON DATABASE tmi TO tmi_app;
GRANT USAGE ON SCHEMA public TO tmi_app;

-- Grant DML on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO tmi_app;

-- Grant DML on future tables (created by tmi-dbtool)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tmi_app;

-- Grant sequence usage (needed for auto-increment columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO tmi_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO tmi_app;

Oracle

-- Create the application user
CREATE USER tmi_app IDENTIFIED BY "your-secure-password"
  DEFAULT TABLESPACE DATA
  QUOTA UNLIMITED ON DATA;

-- Grant basic connection
GRANT CREATE SESSION TO tmi_app;

-- Grant DML on all tables owned by the admin schema
-- (Run this for each table, or use a PL/SQL block)
BEGIN
  FOR t IN (SELECT table_name FROM user_tables) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || t.table_name || ' TO tmi_app';
  END LOOP;
END;
/

Verifying Your Setup

Run tmi-dbtool --config=<config-file> with no operation flags for a health check that reports:

• Database type and version
• Schema status (current or needs migration, with missing table details)
• System data health (built-in groups, webhook deny list)

tmi-dbtool --config=config-production.yml

If the schema needs migration, the output includes a reminder to run tmi-dbtool --schema.

See Also

• Configuration-Reference -- Full list of configuration keys and defaults
• Database-Setup -- Database installation and connection setup
• Database-Tool-Reference -- Complete CLI reference for tmi-dbtool