AI MCP Gateway

-- Phase 1.3: Security & Role-Based Access Control (RBAC) -- User roles and permissions -- ============================================ -- Security Tables -- ============================================ CREATE TABLE IF NOT EXISTS user_roles ( user_id TEXT NOT NULL, project_id TEXT NOT NULL, role TEXT NOT NULL CHECK (role IN ('admin', 'developer', 'viewer', 'readonly')), permissions JSONB NOT NULL DEFAULT '[]'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), PRIMARY KEY (user_id, project_id) ); -- API keys table for authentication CREATE TABLE IF NOT EXISTS api_keys ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id TEXT NOT NULL, project_id TEXT NOT NULL, key_hash TEXT NOT NULL UNIQUE, name TEXT NOT NULL, permissions JSONB NOT NULL DEFAULT '[]'::jsonb, last_used_at TIMESTAMPTZ, expires_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), revoked_at TIMESTAMPTZ, FOREIGN KEY (user_id, project_id) REFERENCES user_roles(user_id, project_id) ON DELETE CASCADE ); -- Audit log for security events CREATE TABLE IF NOT EXISTS audit_logs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id TEXT NOT NULL, project_id TEXT, action TEXT NOT NULL, resource_type TEXT NOT NULL, resource_id TEXT, status TEXT NOT NULL CHECK (status IN ('success', 'failure', 'denied')), ip_address INET, user_agent TEXT, metadata JSONB DEFAULT '{}', timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- Indexes CREATE INDEX idx_user_roles_user ON user_roles(user_id); CREATE INDEX idx_user_roles_project ON user_roles(project_id); CREATE INDEX idx_api_keys_user ON api_keys(user_id); CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash); CREATE INDEX idx_api_keys_expires ON api_keys(expires_at) WHERE revoked_at IS NULL; CREATE INDEX idx_audit_logs_user ON audit_logs(user_id); CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp DESC); CREATE INDEX idx_audit_logs_action ON audit_logs(action); -- Trigger to update updated_at CREATE TRIGGER update_user_roles_updated_at BEFORE UPDATE ON user_roles FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Default permissions by role COMMENT ON TABLE user_roles IS 'User roles and permissions for RBAC'; COMMENT ON TABLE api_keys IS 'API keys for authentication'; COMMENT ON TABLE audit_logs IS 'Security audit trail'; COMMENT ON COLUMN user_roles.role IS 'Role: admin (full access), developer (read/write), viewer (read-only analytics), readonly (read traces only)'; COMMENT ON COLUMN api_keys.key_hash IS 'SHA-256 hash of API key';