postgres-aiops

• Outcome: Review
• Latest audit: Oct 15, 2026 · 9:44 AM UTC
• Version: 0.12.0

Overview

This PostgreSQL DBA skill is transparent about its database access, but its MCP write tools can make production-impacting changes without an enforced approval gate.

Review before installing. Use a read-only PostgreSQL role by default, and only configure write-capable credentials for a narrow target during an approved maintenance window. Do not connect this skill with a superuser or broad production role unless your agent environment has a separate approval process for MCP writes. Protect POSTGRES_AIOPS_HOME, the master password, audit.db, and undo.db because they can contain sensitive operational metadata.

A.I.G

Vulnerability Patterns

• Insecure Skill Coding Practices: Finds exploitable flaws such as hardcoded secrets or command injection
• Skill Instruction Hijacking: Alters the agent's session goals or safety constraints when the skill loads
• Agent Memory Poisoning: Writes attacker-controlled rules into memory that affect later sessions
• Remote Payload Retrieval and Execution: Fetches external code whose behavior can change after review
• Embedded Malicious Code: Ships malicious scripts inside the skill and executes them locally

Findings (1)

T09 · Insecure Skill Coding Practices

Error

• Location: SKILL.md:138

Finding

State-Changing MCP Operations Lack an Enforced Authorization Gate

Content

View full analysis

Vulnerability Details

File Location: SKILL.md:138-149; corroborated by references/agent-guardrails.md:23-27 and references/cli-reference.md:87-94
Vulnerability Type: Missing authorization and confirmation enforcement for destructive operations
Risk Level: High

Relevant snippets:

SKILL.md:138-149:

text

The skill delivers reads and writes and records them; it does **not** decide whether a write is
permitted. That is your agent's judgement, or the permission of the account you connect it with
(connect with a PostgreSQL role that has no write privileges (a read-only role, or one without
INSERT/UPDATE/DELETE/DDL) — writes then fail at the server). There is no read-only switch, policy
file, or approval gate.

- **Audit is the guarantee, and it is not bypassable.** Every operation — MCP and CLI alike — is logged to `~/.postgres-aiops/audit.db` (relocatable via `POSTGRES_AIOPS_HOME`): params, result, status, duration, and the risk tier. The CLI writes the same row the MCP path does.
- `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations recorded on the audit row (who/why); they are never required and never block.
- **Runaway guard** — a safety backstop, not authorization: the same call looped in a tight window trips a circuit breaker. Disable with `POSTGRES_RUNAWAY_MAX=0`.
- Writes support `--dry-run` / `dry_run=True` and double confirmation at the CLI.

references/agent-guardrails.md:23-27:

text

**Authorization is not this tool's job.** Whether a write is permitted is decided by the account
you connect it with (connect with a PostgreSQL role that has no write privileges — a read-only
role, or one without INSERT/UPDATE/DELETE/DDL — and the write fails at the server) or by your
agent's prompt. `POSTGRES_AUDIT_APPROVED_BY` / `POSTGRES_AUDIT_RATIONALE` are optional annotations
recorded on the audit row; they are never required and never block a call.

...[truncated 3036 chars]

Remediation

View remediation

Remediation Suggestions

• Default all MCP write operations to dry-run and reject execution unless a separate, explicit approval step has completed.
• Generate a short-lived, single-use approval token from the preview step. Bind it cryptographically to the target, operation, parameters, database identity, and requesting user.
• Require stronger approval for high-risk or irreversible operations such as backend termination, query cancellation, full vacuum, reindexing, and statistics reset.
• Add a code-enforced read-only mode and make it the default for new installations.
• Enforce authorization inside the MCP execution path rather than relying solely on an agent prompt or database-role permissions.
• Validate that the approving identity is authorized for the exact target and operation; do not treat optional audit annotations as approval.
• Preserve audit logging and least-privilege database roles as defense-in-depth, but do not use them as substitutes for pre-execution authorization.

SkillSpector

Vulnerability Patterns

• Privilege Escalation: Excessive Permissions, Sudo/Root Execution, Credential Access
• Tool Misuse: Tool Parameter Abuse, Chaining Abuse, Unsafe Defaults
• Rogue Agent: Self-Modification, Session Persistence
• MCP Tool Poisoning: Hidden Instructions, Unicode Deception, Parameter Description Injection
• Prompt Injection: Instruction Override, Hidden Instructions, Exfiltration Commands

Findings (10)

Tool Parameter Abuse

High

• Category: Tool Misuse
• Confidence: 70% confidence

Finding

Tool parameters are crafted to achieve unintended or unsafe behavior. Parameter abuse can bypass intended safety checks (e.g. shell=True, --force, dangerous glob patterns).

Content

Scanner excerpt · references/cli-reference.md (reported line 10)May include surrounding context.

bash

postgres-aiops init                      # interactive onboarding wizard
postgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())
postgres-aiops overview [--target <t>]   # one-shot cluster health snapshot
postgres-aiops mcp                       # start the MCP server (stdio transport)

Tool Parameter Abuse

High

• Category: Tool Misuse
• Confidence: 70% confidence

Finding

Tool parameters are crafted to achieve unintended or unsafe behavior. Parameter abuse can bypass intended safety checks (e.g. shell=True, --force, dangerous glob patterns).

Content

Scanner excerpt · references/setup-guide.md (reported line 97)May include surrounding context.

bash

postgres-aiops init                      # interactive onboarding wizard
postgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())
postgres-aiops overview [--target <t>]   # one-shot cluster health snapshot
postgres-aiops mcp                       # start the MCP server (stdio transport)

Credential Access

High

• Category: Privilege Escalation
• Confidence: 60% confidence

Finding

Code accesses credential files (SSH keys, AWS credentials, etc.). This could indicate credential theft attempts.

Content

Scanner excerpt · references/cli-reference.md (reported line 21)May include surrounding context.

postgres-aiops secret set [--value ] # store password (hidden prompt if no --value) postgres-aiops secret list # names only — values never shown postgres-aiops secret rm postgres-aiops secret migrate # import legacy plaintext .env (PG__PASSWORD) postgres-aiops secret rotate-password # re-encrypt under a new master password

text

Session Persistence

Medium

• Category: Rogue Agent
• Confidence: 60% confidence

Finding

Skill establishes unauthorized persistence across sessions via cron jobs, startup scripts, or state files. Session persistence allows an attacker to maintain access beyond the current interaction.

Content

Scanner excerpt · SKILL.md (reported line 13)May include surrounding context.

md

Use this skill whenever the user needs to operate or troubleshoot a PostgreSQL server/cluster as a DBA — a one-shot cluster health overview; server reads (version/uptime, settings, extensions, databases, roles); activity (sessions, idle-in-transaction, long-running queries, locks); query stats (pg_stat_statements top-N, EXPLAIN a statement); index health (unused indexes, missing-index hints, bloat, invalid/duplicate); table health (sizes, dead-tuple bloat, autovacuum status); replication (standby lag, replication slots, WAL); three flagship analyses — slow-query RCA (worst pg_stat_statements entry + EXPLAIN → cause/action), bloat & vacuum analysis (dead tuples + autovacuum lag → recommendation), and blocking lock-chain RCA (build the wait-for tree, name the root blocker); and guarded writes (terminate a backend, cancel a query, VACUUM/ANALYZE, create/drop an index, REINDEX, ALTER SYSTEM SET a parameter, reset query stats).
  Always use this skill for "postgres health check", "why is this query slow", "pg_stat_statements top queries", "EXPLAIN this", "table/index bloat", "which indexes are unused", "missing index", "autovacuum status", "who is blocking whom", "kill the backend holding the lock", "replication lag", "replication slots", "VACUUM this table", "create/drop an index", or "ALTER SYSTEM SET work_mem" when the context is a PostgreSQL database.
  Do NOT use when the target is OT / industrial equipment (Modbus, OPC-UA, PLCs — use industrial-aiops), a hypervisor, a storage appliance, a backup product, a container/cluster orchestrator, or a non-PostgreSQL database (negative routing hints only).
  Covers common PostgreSQL DBA operations with a built-in governance harness (audit, token budget, undo, risk-tiers). Beyond the mock suite, the reads plus a governed write and its undo have been exercised against a live PostgreSQL 16.14 instance (see docs/VERIFICATION.md).
installer:
  kind: uv
  package: postgres-aiops

Session Persistence

Medium

• Category: Rogue Agent
• Confidence: 82% confidence

Finding

The documented undo flow stores enough prior state to recreate dropped indexes from captured definitions, which is a form of persistent operational state. In a DBA tool this is contextually expected, but it can retain sensitive schema details and create a durable rollback capability that may be misused by anyone with access to the local audit/undo store.

Content

Scanner excerpt · SKILL.md (reported line 113)May include surrounding context.

md

2. `postgres-aiops table autovacuum` → check whether autovacuum is simply behind (last run, thresholds) before doing it by hand
3. `postgres-aiops remediate vacuum <table> --analyze --dry-run` → preview; then run for real to `VACUUM ANALYZE` (double confirmation)
4. `postgres-aiops index unused` and `postgres-aiops index bloat` → find indexes that cost writes and return nothing
5. `postgres-aiops remediate drop-index <name> --concurrently --dry-run`, then for real → the tool captures `pg_get_indexdef` **before** dropping and records an inverse recreate descriptor
6. **Failure branch**: dropped the wrong index — `postgres-aiops undo apply <id>` recreates it from the captured definition (not a guess). Note `--full` on `remediate vacuum` takes an **exclusive lock** and rewrites the table; it has no undo, so never reach for it as a first response on a live table.

### Break a blocking pile-up during an incident

Unsafe Defaults

Medium

• Category: Tool Misuse
• Confidence: 80% confidence

Finding

Tool defaults are unsafe or overly permissive (e.g. disabled TLS verification, no authentication, world-writable permissions). Unsafe defaults widen the attack surface.

Content

Scanner excerpt · references/cli-reference.md (reported line 10)May include surrounding context.

bash

postgres-aiops init                      # interactive onboarding wizard
postgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())
postgres-aiops overview [--target <t>]   # one-shot cluster health snapshot
postgres-aiops mcp                       # start the MCP server (stdio transport)

Unsafe Defaults

Medium

• Category: Tool Misuse
• Confidence: 80% confidence

Finding

Tool defaults are unsafe or overly permissive (e.g. disabled TLS verification, no authentication, world-writable permissions). Unsafe defaults widen the attack surface.

Content

Scanner excerpt · references/setup-guide.md (reported line 97)May include surrounding context.

bash

postgres-aiops init                      # interactive onboarding wizard
postgres-aiops doctor [--skip-auth]      # config + secret store + connectivity (SELECT version())
postgres-aiops overview [--target <t>]   # one-shot cluster health snapshot
postgres-aiops mcp                       # start the MCP server (stdio transport)

Session Persistence

Medium

• Category: Rogue Agent
• Confidence: 88% confidence

Finding

The guide instructs creation of a persistent login-capable database role with a literal example password (change-me). Even though this is common setup documentation, operators may copy it verbatim or fail to rotate it, leaving long-lived credentials that can be abused to access monitoring data and potentially assist later attacks against the PostgreSQL environment.

Content

Scanner excerpt · references/setup-guide.md (reported line 17)May include surrounding context.

pg_stat_* views. A least-privilege monitoring role works for the reads:

sql

CREATE ROLE aiops LOGIN PASSWORD 'change-me';
GRANT pg_monitor TO aiops;                 -- read visibility into pg_stat_*
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- for top_queries / slow_query_rca

Sudo/Root Execution

Medium

• Category: Privilege Escalation
• Confidence: 80% confidence

Finding

Commands invoke sudo or root privileges. Verify this elevated access is necessary and justified.

Content

Scanner excerpt · references/setup-guide.md (reported line 60)May include surrounding context.

md

- The password is **never** written to disk in plaintext. It lives only in
  `~/.postgres-aiops/secrets.enc`, encrypted with Fernet (AES-128-CBC + HMAC),
  the key derived from your master password via scrypt. Only a per-store random
  salt and the ciphertext are on disk (chmod 600); the master password itself is
  never stored.
- A legacy plaintext env var `PG_<TARGET_NAME_UPPER>_PASSWORD` is still honoured
  as a fallback with a deprecation warning — migrate with `postgres-aiops secret

Intent-Code Divergence

Low

• Category: Not specified by scanner
• Confidence: 81% confidence

Finding

The guide says a legacy plaintext environment variable is honored as a fallback, then states secret migrate 'imports then renames the old .env'. An environment variable fallback and a .env file migration are distinct mechanisms, so this wording appears internally inconsistent and may mislead users about what credential source is actually consumed or renamed.

Content

No source excerpt is available for this finding.

Static analysis

No suspicious patterns detected.