PostgreSQL Operations — psql-secops-prod

Quick Reference

Item	Value
Server	psql-secops-prod
Type	Azure Database for PostgreSQL Flexible Server
Subscription	Logging
Resource group	rg-logging-logs
Database	secops
Access	Managed identity (app), Azure AD (Rory)
Network	Private endpoint only — no public internet access
Backups	Azure automated, 7-day PITR retention
Schema manager	Alembic (runs on Container App startup)
Diagnostic logs	cirius-logging-law-central (ID: 5d76d1f2)

Key tables: incidents, findings, known_good_rules, changes, evidence

---

1. Connecting to psql-secops-prod

Prerequisites

• Azure CLI installed and logged in to the PROD tenant (az login --tenant d477c9f8)
• psql client installed (apt install postgresql-client or brew install libpq)
• Network path to the private endpoint — you must be on Twingate or inside the Azure VNET

Get an Azure AD Access Token

The server requires Azure AD authentication. No password exists for Rory's account — auth is token-based.

# Acquire a token scoped to Azure Database for PostgreSQL
az account get-access-token \
  --resource-type oss-rdbms \
  --query accessToken \
  --output tsv

Save that token — it is your password for the session. Tokens expire after ~60 minutes.

Connect with psql

# Set token as a variable to avoid re-running the command
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)

# Connect — username is your full UPN
psql "host=psql-secops-prod.postgres.database.azure.com \
  port=5432 \
  dbname=secops \
  user=rgarshol@ciriusgroup.com \
  sslmode=require"

Or as a single connection string:

psql "postgresql://rgarshol%40ciriusgroup.com:${PGPASSWORD}@psql-secops-prod.postgres.database.azure.com:5432/secops?sslmode=require"

Note: The @ in the username must be URL-encoded as %40 in connection string format.

Verify You Are Connected to the Right Server

SELECT current_database(), current_user, inet_server_addr(), version();

Expected output: secops, your UPN, private IP in the Azure VNET, PostgreSQL 16.x.

Connection String Format (for reference/docs)

The app uses managed identity — there is no connection string with a password for the application. For documentation purposes, the host format is:

host=psql-secops-prod.postgres.database.azure.com
port=5432
dbname=secops
sslmode=require

The Container App authenticates by fetching an Azure AD token at startup via its managed identity, not a stored credential.

---

2. Common Operational Queries

Row Counts for All Key Tables

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('incidents', 'findings', 'known_good_rules', 'changes', 'evidence')
ORDER BY n_live_tup DESC;

Recent Incidents (last 24 hours)

SELECT
  id,
  title,
  severity,
  status,
  source_system,
  created_at
FROM incidents
WHERE created_at >= NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC
LIMIT 50;

Incidents by Status

SELECT status, severity, COUNT(*) AS count
FROM incidents
GROUP BY status, severity
ORDER BY status, severity;

Open Incidents Older Than 24 Hours (backlog check)

SELECT id, title, severity, source_system, created_at
FROM incidents
WHERE status = 'OPEN'
  AND created_at < NOW() - INTERVAL '24 hours'
ORDER BY severity, created_at;

Known-Good Rule Hit Counts

SELECT
  id,
  name,
  hit_count,
  last_hit_at,
  created_at,
  enabled
FROM known_good_rules
ORDER BY hit_count DESC
LIMIT 25;

Rules with zero hits in the last 30 days may be stale:

SELECT id, name, hit_count, last_hit_at
FROM known_good_rules
WHERE (last_hit_at < NOW() - INTERVAL '30 days' OR last_hit_at IS NULL)
  AND enabled = true
ORDER BY last_hit_at NULLS FIRST;

Changes Table — Verify Recent CM Entries

SELECT
  id,
  title,
  source_system,
  status,
  created_at,
  effective_at,
  expires_at
FROM changes
WHERE created_at >= NOW() - INTERVAL '48 hours'
ORDER BY created_at DESC;

Check whether a CM entry covers a specific time window (e.g., for suppression debugging):

SELECT id, title, source_system, effective_at, expires_at
FROM changes
WHERE effective_at <= '2026-05-25 18:00:00+00'
  AND expires_at >= '2026-05-25 16:00:00+00'
  AND status = 'APPROVED';

Evidence Table — Recent Entries

SELECT id, source_system, evidence_type, s3_path, created_at
FROM evidence
ORDER BY created_at DESC
LIMIT 20;

Findings — Recent by Source System

SELECT source_system, COUNT(*) AS count, MAX(created_at) AS latest
FROM findings
WHERE created_at >= NOW() - INTERVAL '24 hours'
GROUP BY source_system
ORDER BY count DESC;

Check Last Agent Run (via findings timestamps)

SELECT source_system, MAX(created_at) AS last_finding_at
FROM findings
GROUP BY source_system
ORDER BY last_finding_at DESC;

If a source system's last finding is older than 6 hours, the agent may have failed.

---

3. Alembic Migration Operations

Alembic migrations run automatically when ca-soc-prod starts. The entrypoint runs alembic upgrade head before starting the FastAPI app. Under normal operation you do not need to run migrations manually.

Check Current Database Revision

From inside the bedrock-soc repo directory:

# Requires DATABASE_URL set or alembic.ini pointing at the server
# Easiest approach: set the URL with the Azure AD token
export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)
export DATABASE_URL="postgresql://rgarshol%40ciriusgroup.com:${PGPASSWORD}@psql-secops-prod.postgres.database.azure.com:5432/secops?sslmode=require"

alembic current

Or query directly in psql — Alembic stores its state in the alembic_version table:

SELECT version_num FROM alembic_version;

Compare Against Head

alembic heads

If alembic current shows a different hash than alembic heads, pending migrations exist.

Show Pending Migrations

alembic history --indicate-current

Run a Migration Manually

Only do this if the Container App startup failed mid-migration and the app is down. Confirm with alembic current before running.

# Upgrade to the latest revision
alembic upgrade head

# Upgrade to a specific revision
alembic upgrade <revision_id>

# Upgrade one step at a time (safer for debugging)
alembic upgrade +1

Roll Back a Migration

# Roll back one step
alembic downgrade -1

# Roll back to a specific revision
alembic downgrade <revision_id>

# Roll back to the very beginning (destroys all schema — do not run in prod without explicit intent)
alembic downgrade base

Always snapshot the database (or confirm PITR is current) before rolling back in production.

What to Do if Startup Migration Fails

1. Check Container App logs immediately:

az containerapp logs show \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --subscription <logging-subscription-id> \
  --tail 200

2. Look for the Alembic error. Common causes:

Error	Cause	Fix
Can't locate revision identified by...	alembic_version table has a hash that doesn't exist in the migration tree	Manual alembic stamp <correct_revision> then redeploy
relation already exists	Migration tried to create a table that exists (duplicate migration run)	alembic stamp head if schema is correct; investigate why the revision table is behind
column ... of relation ... does not exist	A previous migration partially applied then failed	Manually apply the missing DDL or roll back to pre-migration state
could not connect to server	Network or auth failure — not an Alembic error	See Section 7, Container App can't connect
ERROR: permission denied for table ...	Managed identity missing rights on the table	Check role grants; managed identity may have lost its PostgreSQL role

3. If the migration is partially applied and the app won't start, do not let the app keep restarting in a loop. Scale the Container App to 0 replicas while you fix:

az containerapp update \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --min-replicas 0 \
  --max-replicas 0

4. Fix the migration or schema state manually via psql, then scale back up:

az containerapp update \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --min-replicas 1 \
  --max-replicas 3

---

4. Backup and PITR

Azure Automated Backup Configuration

• Retention: 7 days
• Type: Azure managed — full, differential, transaction log backups automatically
• Storage: Geo-redundant backup storage (Azure default for Flexible Server)
• RPO: ~5 minutes (continuous transaction log backup)

Check Backup Status in the Portal

1. Go to portal.azure.com → search psql-secops-prod
2. Left nav: Backups
3. Verify the latest restore point timestamp. It should be within the last 15 minutes.
4. If no recent restore point shows: open a support ticket — this is a platform issue.

Via CLI:

az postgres flexible-server backup list \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --subscription <logging-subscription-id>

Initiate a PITR Restore (to a New Server)

PITR always creates a new server — it does not restore in-place. The original server stays running.

az postgres flexible-server restore \
  --name psql-secops-prod-restore-20260525 \
  --resource-group rg-logging-logs \
  --source-server psql-secops-prod \
  --restore-time "2026-05-25T14:00:00Z" \
  --subscription <logging-subscription-id>

--restore-time must be in UTC ISO 8601 format, within the 7-day retention window.

The restore operation takes 15–45 minutes depending on database size. Monitor via:

az postgres flexible-server show \
  --name psql-secops-prod-restore-20260525 \
  --resource-group rg-logging-logs \
  --query "state"

State transitions: Creating → Updating → Ready

Verify Restored Data

1. Connect to the restored server (same Azure AD token method — replace hostname):

export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)
psql "host=psql-secops-prod-restore-20260525.postgres.database.azure.com \
  port=5432 \
  dbname=secops \
  user=rgarshol@ciriusgroup.com \
  sslmode=require"

2. Verify row counts match expectation for the restore point:

SELECT
  (SELECT COUNT(*) FROM incidents) AS incidents,
  (SELECT COUNT(*) FROM findings) AS findings,
  (SELECT COUNT(*) FROM known_good_rules) AS known_good_rules,
  (SELECT COUNT(*) FROM changes) AS changes,
  (SELECT COUNT(*) FROM evidence) AS evidence;

3. Check the most recent records align with the target restore time:

SELECT MAX(created_at) AS latest_incident FROM incidents;
SELECT MAX(created_at) AS latest_finding FROM findings;

4. Once verified, if you need to cut over: update the Container App's DATABASE_URL environment variable (or Key Vault secret if it is stored there) to point at the restored server's hostname.

Delete the Restore Server When Done

Restore servers incur cost. Delete when no longer needed:

az postgres flexible-server delete \
  --name psql-secops-prod-restore-20260525 \
  --resource-group rg-logging-logs \
  --yes

---

5. Performance

Enable pg_stat_statements (if not already enabled)

Check first:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

If not present, enable via Azure portal:

• psql-secops-prod → Server parameters → search shared_preload_libraries → add pg_stat_statements → Save → restart server

Then in psql:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Check Slow Queries

SELECT
  query,
  calls,
  total_exec_time / 1000 AS total_sec,
  mean_exec_time / 1000 AS mean_sec,
  rows,
  stddev_exec_time / 1000 AS stddev_sec
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Reset stats after tuning:

SELECT pg_stat_statements_reset();

Check Active Connections

SELECT
  state,
  wait_event_type,
  wait_event,
  COUNT(*) AS count
FROM pg_stat_activity
WHERE datname = 'secops'
GROUP BY state, wait_event_type, wait_event
ORDER BY count DESC;

See all active queries running longer than 30 seconds:

SELECT
  pid,
  usename,
  application_name,
  state,
  wait_event,
  query_start,
  NOW() - query_start AS duration,
  LEFT(query, 120) AS query_snippet
FROM pg_stat_activity
WHERE datname = 'secops'
  AND state != 'idle'
  AND query_start < NOW() - INTERVAL '30 seconds'
ORDER BY duration DESC;

Kill a stuck query (use pid from above):

SELECT pg_cancel_backend(<pid>);
-- If cancel doesn't work:
SELECT pg_terminate_backend(<pid>);

Connection Limit by SKU

Azure Database for PostgreSQL Flexible Server connection limits by SKU tier:

vCores	Max Connections
2 vCores (Burstable B2s)	50
2 vCores (General Purpose)	214
4 vCores	429
8 vCores	859
16 vCores	1718

Check current SKU:

az postgres flexible-server show \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --query "sku"

Check connection headroom:

SELECT
  max_conn,
  used,
  max_conn - used AS available
FROM
  (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc,
  (SELECT COUNT(*) AS used FROM pg_stat_activity WHERE datname = 'secops') ua;

If connection count is consistently above 80% of the limit, consider PgBouncer or scaling the SKU.

Check Index Usage

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname IN ('incidents', 'findings', 'known_good_rules', 'changes', 'evidence')
ORDER BY idx_scan ASC;

Indexes with idx_scan = 0 that are not primary keys may be unused overhead — review before dropping.

---

6. pgaudit Setup

pgaudit is planned but not yet enabled. This section documents the enable procedure when ready.

Step 1 — Enable the Extension via Server Parameters

In the Azure portal:

1. Navigate to psql-secops-prod → Server parameters
2. Search for shared_preload_libraries
3. Add pgaudit to the list (alongside any existing entries)
4. Click Save — this requires a server restart

Via CLI:

az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name shared_preload_libraries \
  --value pgaudit

Restart the server:

az postgres flexible-server restart \
  --name psql-secops-prod \
  --resource-group rg-logging-logs

Step 2 — Create the Extension

After restart, connect and run:

CREATE EXTENSION IF NOT EXISTS pgaudit;

Step 3 — Configure Audit Scope

For HIPAA/HITRUST compliance, audit DDL and role changes at minimum:

-- Audit DDL (CREATE, ALTER, DROP) and ROLE changes globally
ALTER SYSTEM SET pgaudit.log = 'ddl, role';
SELECT pg_reload_conf();

For object-level auditing of the key tables (write to PostgreSQL log):

-- Audit all writes to the incidents table
ALTER TABLE incidents ENABLE ROW LEVEL SECURITY;

-- pgaudit object logging (tracks SELECT/INSERT/UPDATE/DELETE per table)
ALTER SYSTEM SET pgaudit.log_catalog = 'on';
SELECT pg_reload_conf();

To audit specific tables via a dedicated audit role:

CREATE ROLE audit_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON incidents, findings, known_good_rules, changes, evidence TO audit_role;
ALTER SYSTEM SET pgaudit.role = 'audit_role';
SELECT pg_reload_conf();

Step 4 — Forward pgaudit Logs to LAW

Azure Database for PostgreSQL Flexible Server does not have a built-in pgaudit → LAW connector. Audit entries land in the PostgreSQL log stream, which Azure routes to LAW via diagnostic settings.

Verify diagnostic settings are configured:

az monitor diagnostic-settings list \
  --resource /subscriptions/<logging-sub-id>/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secocs-prod

Confirm PostgreSQLLogs is enabled and the target workspace is cirius-logging-law-central.

If diagnostic settings are missing, create them:

az monitor diagnostic-settings create \
  --name psql-secops-prod-diag \
  --resource /subscriptions/<logging-sub-id>/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secops-prod \
  --workspace /subscriptions/<logging-sub-id>/resourceGroups/rg-logging-logs/providers/Microsoft.OperationalInsights/workspaces/cirius-logging-law-central \
  --logs '[{"category":"PostgreSQLLogs","enabled":true}]' \
  --metrics '[{"category":"AllMetrics","enabled":true}]'

KQL — Query pgaudit Events in LAW

Once pgaudit is enabled and logs are flowing to LAW, use this KQL:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where ResourceType == "FLEXIBLESERVERS"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| extend
    audit_type = extract(@"AUDIT: (\w+)", 1, Message),
    audit_object = extract(@"ON (\S+)", 1, Message),
    audit_command = extract(@"AUDIT: \w+,\d+,\d+,(\w+)", 1, Message)
| project TimeGenerated, audit_type, audit_command, audit_object, Message
| order by TimeGenerated desc

DDL events only:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:" and Message contains "DDL"
| project TimeGenerated, Message
| order by TimeGenerated desc

---

7. Troubleshooting

Container App Can't Connect to Database

Symptoms: ca-soc-prod is in a crash loop, logs show connection refused or authentication failure.

Check 1 — Managed Identity Has the PostgreSQL Role

The Container App's managed identity must have been granted a login role in PostgreSQL. This is a one-time setup step, but it can break if the server is restored or the managed identity is recreated.

In psql (connected as Rory):

-- List all roles
\du

-- Check if the managed identity's app ID or display name exists
SELECT rolname FROM pg_roles WHERE rolname LIKE '%ca-soc-prod%';

If the role is missing, re-grant it. The managed identity name should match the Container App's system-assigned identity display name:

-- Replace <managed-identity-name> with the actual display name from the portal
CREATE ROLE "<managed-identity-name>" LOGIN;
GRANT ALL PRIVILEGES ON DATABASE secops TO "<managed-identity-name>";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "<managed-identity-name>";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "<managed-identity-name>";

Check 2 — Private Endpoint DNS Resolution

The Container App must resolve psql-secops-prod.postgres.database.azure.com to the private endpoint IP, not the public IP.

From inside a Container App shell (if exec is available):

az containerapp exec \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --command "nslookup psql-secops-prod.postgres.database.azure.com"

Expected: resolves to a 10.x.x.x private IP. If it resolves to a public IP (e.g., 52.x.x.x), the private DNS zone is not linked to the Container App's VNET.

Fix: In the Azure portal, navigate to the private DNS zone privatelink.postgres.database.azure.com → Virtual network links → verify the Container App's VNET is linked. If not, add the link.

Check 3 — Firewall Rules

The server should be configured to deny all public access (private endpoint only). If someone added a firewall rule that conflicts, or if the private endpoint was deleted:

# Check firewall rules (should be empty if private endpoint only)
az postgres flexible-server firewall-rule list \
  --name psql-secops-prod \
  --resource-group rg-logging-logs

# Check private endpoint connection status
az network private-endpoint-connection list \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --type Microsoft.DBforPostgreSQL/flexibleServers

Private endpoint connection state should be Approved.

Check 4 — Container App VNET Integration

The Container App must be in a subnet with connectivity to the PostgreSQL private endpoint subnet:

az containerapp show \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --query "properties.configuration.ingress"

Verify the Container App is using VNET-integrated egress.

Migration Fails on Startup

See Section 3 for specific error handling. General approach:

1. Get the logs: az containerapp logs show --name ca-soc-prod --resource-group rg-logging-logs --tail 200
2. Identify whether the error is in Alembic itself or in the database connection before Alembic runs
3. If connection fails: see Container App can't connect above
4. If Alembic errors: check alembic_version table matches your migration tree
5. Never run alembic upgrade head twice concurrently — scale to 0 replicas first

Database Full or Storage Warning

Check current storage usage:

az postgres flexible-server show \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --query "storage"

Check table sizes from psql:

SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

If the findings table is large, check whether old findings are being purged:

SELECT COUNT(*), MIN(created_at), MAX(created_at) FROM findings;

To increase storage (Azure Flexible Server supports online storage scale-up, no downtime):

az postgres flexible-server update \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --storage-size 256

Storage size is in GB. Azure Flexible Server supports online storage increase only — you cannot decrease storage size.

KQL — PostgreSQL Connection Errors in LAW

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "connection" or Message contains "authentication failed" or Message contains "FATAL"
| project TimeGenerated, Message
| order by TimeGenerated desc
| take 50

// Connection count over time
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where MetricName == "active_connections"
| summarize avg(Average) by bin(TimeGenerated, 5m)
| render timechart

---

8. Maintenance

VACUUM and ANALYZE

Azure Flexible Server runs autovacuum by default. For manual runs after bulk deletes or imports:

-- Analyze statistics only (no locking)
ANALYZE VERBOSE incidents;

-- Vacuum and analyze
VACUUM ANALYZE incidents;

-- Full vacuum (takes exclusive lock — only during maintenance windows)
VACUUM FULL incidents;

Check autovacuum status:

SELECT
  relname,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
WHERE relname IN ('incidents', 'findings', 'known_good_rules', 'changes', 'evidence')
ORDER BY last_autovacuum NULLS FIRST;

If autovacuum hasn't run on a table in more than 24 hours, check autovacuum settings:

SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

Check Table and Index Bloat

-- Bloat estimate per table
SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 1)
    ELSE 0
  END AS dead_row_pct
FROM pg_stat_user_tables
WHERE relname IN ('incidents', 'findings', 'known_good_rules', 'changes', 'evidence')
ORDER BY dead_row_pct DESC;

Dead row percentage above 20% warrants a manual VACUUM. Above 50% is a problem.

Index Maintenance

Rebuild a bloated index without taking the table offline:

REINDEX INDEX CONCURRENTLY <index_name>;

Find invalid indexes (can happen if a concurrent reindex was interrupted):

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
JOIN pg_index ON indexrelid = pg_stat_user_indexes.indexrelid
WHERE NOT indisvalid;

Drop and recreate any invalid index.

Scheduled Maintenance Window

Azure Flexible Server has a configurable maintenance window for system updates. Set it to off-hours:

az postgres flexible-server update \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --maintenance-window "Sunday:02:00"

Day of week: Sunday, Monday, etc. Time is UTC.

---

9. Break-Glass — Managed Identity Auth Breaks

If the Container App's managed identity loses its PostgreSQL role (e.g., server restored from backup, role accidentally dropped, identity recreated), the app goes down immediately. This is the emergency recovery path.

Step 1 — Connect as Rory (Azure AD)

Use the token-based connection from Section 1. Rory's Azure AD account has admin rights on the server by virtue of the Entra admin configuration on the Flexible Server.

If Rory's Azure AD auth is also broken (e.g., the Entra admin setting was lost on the server):

# Check who is configured as the Entra admin
az postgres flexible-server ad-admin list \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs

If the Entra admin is missing or wrong, reassign it:

az postgres flexible-server ad-admin create \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --display-name "Rory Garshol" \
  --object-id <rory-entra-object-id>

This requires Azure RBAC — specifically Contributor on the PostgreSQL resource. If you cannot run this command, escalate to an Azure subscription owner.

Step 2 — Restore the Managed Identity Role

Once connected to psql as Rory:

-- Find the managed identity display name (check Azure portal: ca-soc-prod → Identity → System assigned → Display name)
-- It typically looks like: ca-soc-prod or a GUID

-- Check existing roles
\du

-- If the role is missing, create it
CREATE ROLE "ca-soc-prod" WITH LOGIN;

-- Grant database access
GRANT ALL PRIVILEGES ON DATABASE secops TO "ca-soc-prod";

-- Grant table access
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "ca-soc-prod";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "ca-soc-prod";
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO "ca-soc-prod";

-- Ensure future tables are covered
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "ca-soc-prod";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO "ca-soc-prod";

Step 3 — Restart the Container App

az containerapp update \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --min-replicas 0 \
  --max-replicas 0

az containerapp update \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --min-replicas 1 \
  --max-replicas 3

Wait 60 seconds, then verify the app is healthy:

az containerapp show \
  --name ca-soc-prod \
  --resource-group rg-logging-logs \
  --query "properties.runningStatus"

And confirm it can reach the database by hitting the health endpoint:

curl -s https://secops.bedrockcybersecurity.org/health

Step 4 — Root Cause

After restoring access, determine what caused the role loss:

• Was the server restored from backup? Restores create a new server — role grants from the original do not carry over automatically.
• Was the Flexible Server resource deleted and recreated? Same issue — roles are gone.
• Was the managed identity deleted and recreated in Entra? The object ID changes and the old role is now orphaned.

Document the incident in the SecOps platform (POST /api/incidents) and create a CM ticket for the emergency access.

---

LAW Diagnostic Queries (KQL Reference)

All PostgreSQL Log Events

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| project TimeGenerated, Category, Level, Message
| order by TimeGenerated desc
| take 100

Error Events Only

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Level == "Error" or Message contains "FATAL" or Message contains "ERROR"
| project TimeGenerated, Level, Message
| order by TimeGenerated desc

Long-Running Queries (from PostgreSQL log)

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "duration:" and Message contains "ms"
| extend duration_ms = toint(extract(@"duration: (\d+)", 1, Message))
| where duration_ms > 5000
| project TimeGenerated, duration_ms, Message
| order by duration_ms desc

Connection Count Over Time

AzureMetrics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where MetricName == "active_connections"
| summarize avg(Average), max(Maximum) by bin(TimeGenerated, 5m)
| render timechart

Storage Usage Trend

AzureMetrics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where MetricName == "storage_used"
| extend gb_used = Average / 1073741824
| summarize avg(gb_used) by bin(TimeGenerated, 1h)
| render timechart