PostgreSQL

• Outcome: Review
• Latest audit: Sep 10, 2026 · 4:11 PM UTC
• Version: 1.0.3

Overview

This PostgreSQL operations skill is coherent, but it needs Review because it automatically uses persistent local database context and includes guidance that could expose production-derived data if followed as written.

Review this skill before installing if your PostgreSQL work involves production, regulated data, or sensitive infrastructure details. It is not malicious, but users should treat its persistent memory as sensitive local state, confirm any writes to config or memory, avoid storing credentials or hostnames there, and not copy production pg_statistic data into lower-trust environments without sanitization and access controls.

A.I.G

Vulnerability Patterns

• Agent Memory Poisoning: Writes attacker-controlled rules into memory that affect later sessions
• 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
• 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 (2)

T02 · Agent Memory Poisoning

Warning

• Location: setup.md:11

Finding

Persistent User and Database Context Can Poison Future Agent Sessions

Content

View full analysis

Vulnerability Details

File Location: setup.md:11-27; related storage schema in memory-template.md:3-22 and persistence declaration in SKILL.md:31
Vulnerability Type: Persistent attacker-influenced state
Risk Level: Medium

Vulnerable Code

markdown

1. Read `~/Clawic/data/pg/config.yaml` if it exists. Apply its values.
2. For anything absent, use the defaults in the Configuration table of `SKILL.md` — do not ask.
   - `server_version: 16`, `deployment: self-hosted`, `client: psql`, `pooler: app-pool`, `id_style: bigint-identity`, `naming_convention: snake_plural`, `lock_timeout_default: 2s`, `destructive_confirm: true`.
3. Read `~/Clawic/data/pg/memory.md` for prior context (their schema shape, recurring pain points). Absence is fine; proceed without comment.
4. Universal values (units, locale, timezone) fall back to `~/Clawic/profile.yaml` when this skill has no key of its own.

If the live server is reachable, `SHOW server_version` and `SELECT current_setting('is_superuser')` beat any stored value — the stored one is a fallback for when it is not.

Work from defaults immediately. Never open with questions about their stack, their scale, or how proactive to be.

## Recording Preferences (only when the user declares one)

Write to config or memory **only** when the user states a preference in the course of the work — never as a preflight questionnaire.

- User names a version, provider, client, pooler, key style, or naming convention → update the matching key in `~/Clawic/data/pg/config.yaml`.
- User expresses a habit or stance (migration tooling, whether DDL may be run directly, how much plan detail they want, which extensions are unavailable to them) → record it under the relevant preference area (tooling, thresholds, conventions, platform, risk posture, output format, integrations, restrictions, cadence) in `~/Clawic/data/pg/memory.md`.
- User corrects earlier guidance → update the stored value so it is not repeated.

The assoc ...[truncated 3242 chars]

Remediation

View remediation

Remediation Suggestions

1. Require explicit user confirmation before creating or updating persistent memory.
2. Display the exact fields and values proposed for persistence.
3. Treat loaded memory as untrusted contextual data rather than authoritative instructions.
4. Prevent persisted values from disabling destructive-operation confirmation, authorization checks, or other safety controls.
5. Validate config.yaml against a strict schema, including enumerated values, type checks, and safe bounds.
6. Attach provenance and update timestamps to each stored field, and expire or reconfirm security-sensitive preferences.
7. Never store credentials, connection strings, hostnames, customer data, query output, tokens, or sampled production values.
8. Minimize stored infrastructure details and encrypt or permission-restrict files containing sensitive operational metadata.
9. Verify live server properties before privileged operations rather than relying on stored deployment, version, or privilege assumptions.
10. Avoid reading the global ~/Clawic/profile.yaml unless the current task specifically requires those values.

T09 · Insecure Skill Coding Practices

Warning

• Location: test-databases.md:43

Finding

Production PostgreSQL Statistics May Expose Real Sensitive Values

Content

View full analysis

Vulnerability Details

File Location: test-databases.md:43
Vulnerability Type: Sensitive production-data exposure through database statistics
Risk Level: Medium

Vulnerable Code

markdown

For plan work, copying only the statistics is enough and much cheaper than copying data: `pg_dump --schema-only` plus the source's `pg_statistic` content (or `ANALYZE` on a representative sample) lets the planner produce production-like plans on a small database. Confirm the plan you get matches the production plan before trusting the experiment.

Technical Analysis

The recommendation treats pg_statistic as if it contained only anonymous planner metadata. PostgreSQL statistics can contain actual values sampled from source columns, including most-common values and histogram bounds. Depending on the schema, these values may include email addresses, names, tenant identifiers, account references, location data, tokens, or other sensitive production content.

Copying raw pg_statistic content into a development, CI, analyst, or laptop database can therefore move production data across a trust boundary. This contradicts the same document's requirement to anonymize production copies before they leave a controlled scratch environment.

Statistics catalogs also require careful handling because their internal representation and direct manipulation are not intended as a general portable backup interface. The primary security concern, however, is that the catalog may contain real production-derived values.

Attack Path

1. An operator follows the guidance and exports the production schema together with raw pg_statistic content.
2. Statistics for sensitive columns contain most-common values or histogram bounds copied from actual production rows.
3. The export is restored into a lower-trust development, test, or CI environment.
4. A developer, CI administrator, compromised build worker, or other user with catalog access queries the restored statistic ...[truncated 867 chars]

Remediation

View remediation

Remediation Suggestions

1. Remove the recommendation to copy raw production pg_statistic content.
2. Prefer synthetic datasets that reproduce row counts, null fractions, cardinality, skew, and value-length distributions without retaining source values.
3. If statistics transfer is operationally necessary, perform a column-level data-classification review first.
4. Exclude all sensitive, secret-bearing, free-text, identity, and tenant-specific columns.
5. Replace most-common values and histogram bounds with sanitized synthetic equivalents while preserving approximate selectivity.
6. Keep any statistics-processing operation inside a production-equivalent controlled environment.
7. Apply production-level access controls, encryption, retention limits, and audit logging to any resulting artifact.
8. Add an explicit warning that PostgreSQL statistics can contain real sampled values and must be treated as production data.
9. Review generated artifacts before transfer and ensure they cannot be retained in CI caches, logs, or developer backups.

SkillSpector

Vulnerability Patterns

• Prompt Injection: Instruction Override, Hidden Instructions, Exfiltration Commands
• Excessive Agency: Unrestricted Tool Access, Autonomous Decision Making, Scope Creep
• Output Handling: Unvalidated Output Injection, Cross-Context Output, Unbounded Output
• Tool Misuse: Tool Parameter Abuse, Chaining Abuse, Unsafe Defaults
• Rogue Agent: Self-Modification, Session Persistence

Findings (11)

Missing User Warnings

Medium

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

Finding

The skill explicitly guides users through live PostgreSQL operations such as DDL, migrations, backfills, lock handling, and incident response, but the surfaced summary area lacks an explicit top-level safety warning that these actions can block traffic, cause outages, or risk data loss if executed against production without verification. Although the document contains many safe-operating rules later, a user could still act on early guidance or generated commands before absorbing those safeguards, making this a real operational safety weakness.

Content

No source excerpt is available for this finding.

Unbounded Resource Access

Medium

• Category: Excessive Agency
• Confidence: 75% confidence

Finding

Skill allows unbounded resource consumption (API calls, storage, compute). Without rate limits or quotas, a compromised or misbehaving agent can cause denial-of-service or cost overruns.

Content

Scanner excerpt · bulk-load.md (reported line 19)May include surrounding context.

sql

SET maintenance_work_mem = '2GB';     -- index builds after the load
SET synchronous_commit = off;         -- per session; a crash loses this load, which you can rerun
SET statement_timeout = 0;            -- a load is not a runaway query

Session-scoped, not global. synchronous_commit = off is safe here specifically because the recovery action is "run the load again"; the bounded loss window is in the replication guide's table.

Missing User Warnings

Medium

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

Finding

The guidance explicitly states that statements over 1s are logged 'with parameters' but does not warn that bound values may contain secrets, tokens, personal data, or other regulated information. In a PostgreSQL operations skill, this is more dangerous than in generic documentation because readers may enable the setting directly in production and unintentionally create a durable sensitive-data exposure path via database logs and downstream log aggregation systems.

Content

No source excerpt is available for this finding.

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 · psql.md (reported line 38)May include surrounding context.

• -X ignores ~/.psqlrc so the script behaves the same on every machine — without it, someone's local \set AUTOCOMMIT off changes your deployment.
• ON_ERROR_STOP=1 is mandatory in automation. Without it, psql reports the error, continues to the next statement, and exits 0: a half-applied migration that CI calls green.
• --single-transaction makes the whole file atomic. It is incompatible with statements that cannot run in a transaction block (CREATE INDEX CONCURRENTLY, VACUUM) — those files run without it.
• Variables: -v tenant=42 then :'tenant' (quoted literal) or :"tenant" (quoted identifier). Bare :tenant interpolates raw text and is an injection vector in generated scripts.
• \gexec executes the result set of a query as SQL — the idiomatic way to generate DDL over many objects:

  sql

Unbounded Output

Medium

• Category: Output Handling
• Confidence: 75% confidence

Finding

Output size or generation rate is not bounded. Unbounded output enables denial-of-service through resource exhaustion, log flooding, or context-window stuffing.

Content

Scanner excerpt · schema-design.md (reported line 47)May include surrounding context.

md

- Enum: fast and compact, but values cannot be removed or reordered, and `ADD VALUE` could not run inside a transaction block before PostgreSQL 12. Use it for a truly closed set (`'left' | 'right'`); use a lookup table with an FK the moment product owns the list.
- Arrays are correct for a small, ordered, always-read-together list (tags on a post). They are wrong as a substitute for a join table: no FK, no per-element constraints, and rewriting one element rewrites the whole row. Index containment with GIN.
- `TEXT` has no length limit but a row-level one: values above ~2 kB are compressed and moved to TOAST transparently. A B-tree index entry, however, is capped at about a third of an 8 kB page — index `md5(long_text)` or a prefix, not the column (SKILL.md Error Codes, 54000).
- Collation determines sort order and `LIKE` behaviour. `COLLATE "C"` is byte order: faster, stable across OS upgrades, and the right choice for identifiers, codes, and slugs. Human-facing sorting wants ICU (`"en-US-x-icu"`), which is versioned and survives glibc changes.

## Normalization, Denormalization, Materialization

Natural-Language Policy Violations

Medium

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

Finding

The examples and guidance specify 'english' for to_tsvector and websearch_to_tsquery, which imposes an English-language stemming/tokenization policy. Because the document presents this as the default implementation pattern and does not offer user opt-in or clearly constrain the skill to English-only content, it creates a natural-language locale policy concern.

Content

No source excerpt is available for this finding.

Unsafe Defaults

Medium

• Category: Tool Misuse
• Confidence: 70% 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 · security.md (reported line 24)May include surrounding context.

md

- Applications never own their tables. If the connecting role owns them, an SQL injection can `DROP` them; if it does not, the same injection cannot.
- Grant on the schema (`USAGE`) *and* the objects. Missing schema usage produces "permission denied for schema", which reads like a table problem and is not.
- Predefined roles instead of superuser: `pg_read_all_data` / `pg_write_all_data` (PostgreSQL >=14), `pg_monitor` for observability tooling, `pg_signal_backend` for an on-call role that must cancel queries. Superuser should be a break-glass account, not a service account.
- Since PostgreSQL 15 the `public` schema is no longer world-writable. Upgrades from 14 or earlier break scripts that assumed every role could create tables in `public`.

## Row-Level Security

Unsafe Defaults

Medium

• Category: Tool Misuse
• Confidence: 70% 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 · upgrades.md (reported line 37)May include surrounding context.

md

- Applications never own their tables. If the connecting role owns them, an SQL injection can `DROP` them; if it does not, the same injection cannot.
- Grant on the schema (`USAGE`) *and* the objects. Missing schema usage produces "permission denied for schema", which reads like a table problem and is not.
- Predefined roles instead of superuser: `pg_read_all_data` / `pg_write_all_data` (PostgreSQL >=14), `pg_monitor` for observability tooling, `pg_signal_backend` for an on-call role that must cancel queries. Superuser should be a break-glass account, not a service account.
- Since PostgreSQL 15 the `public` schema is no longer world-writable. Upgrades from 14 or earlier break scripts that assumed every role could create tables in `public`.

## Row-Level Security

Missing User Warnings

Medium

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

Finding

The skill explicitly instructs the agent to read local preference and memory files and later write back to them, but it provides no user-facing notice or consent step in the skill itself. This creates a privacy and integrity risk because an agent may access or persist potentially sensitive local data implicitly, including schema details, operational history, or user habits, beyond the immediate task.

Content

No source excerpt is available for this finding.

Missing User Warnings

Medium

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

Finding

This markdown file instructs users to apply settings with ALTER SYSTEM SET and pg_reload_conf() and discusses restart-required changes, which can affect running database services. The document does not include any warning about testing in staging, backup/recovery preparation, or possible performance/availability impact from incorrect tuning.

Content

No source excerpt is available for this finding.

Natural-Language Policy Violations

Low

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

Finding

The instruction 'Do not interview the user' and related guidance to avoid opening with questions forces behavior without an explicit opt-in, reducing transparency around assumptions and hidden state loading. In this context it is less severe than direct code execution, but it can still cause the agent to act on stale preferences or local memory without confirming that the user wants that behavior.

Content

No source excerpt is available for this finding.

Static analysis

No suspicious patterns detected.