Skip to content
skillsafebeta
← Report library
Purpose / Data analysis

Postgresql Table Design Skill Security Audit

What the author says it does (original text)

Use this skill when designing or reviewing a PostgreSQL-specific schema. Covers best-practices, data types, indexing, constraints, performance patterns, and advanced features

Independent security check

Do not install or run it yet

Files checked
2
Risks found
3
Could it run dangerous commands?Looks for programs run straight after downloading, remote control of your computer, and hidden commands.Risks found: 1
Medium risk

Calling transactional rollback “safe testing” omits production locking risk from ALTER TABLE

Source references: 2
What we found

The guidance describes `BEGIN; ALTER TABLE...; ROLLBACK;` as safe testing. Rollback can undo most schema changes, but it does not prevent `ALTER TABLE` from taking strong locks, blocking other sessions, or performing lengthy scans. The main file also explicitly scopes the Skill to live-database changes.

Why this matters

When tried on a busy production database, the operation may block reads or writes, accumulate connections, and cause service timeouts even though it is eventually rolled back.

The main file explicitly includes live, no-downtime schema changes, while the detailed guidance calls a rollback transaction “safe testing.” Rollback can undo most DDL results, but the visible guidance does not warn that `ALTER TABLE` may still acquire strong locks, block sessions, or perform lengthy work. Following it on production therefore presents an availability risk. Users can ask for lock-level checks, timeouts, rehearsal, and maintenance-window conditions.

SKILL.md:13In the instructionsOpen original file
- Deciding whether and how to partition a large table, or how to store semi-structured data.- Planning a schema change on a live database without downtime.
Show 1 other places
references/details.md:62In the instructionsOpen original file
### Safe schema evolution- **Transactional DDL**: most DDL operations can run in transactions and be rolled back—`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing.- **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions.- **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast.
Could it expose your files or keys?Looks for uploads of files containing passwords or keys, and keys written directly in the code.No risks found
Could it delete files or keep running?Looks for broad file deletion, disk overwrites, and programs set to start automatically.Risks found: 1
Medium risk

Bulk-load guidance includes an environment-unscoped index deletion

Source references: 1
What we found

To speed bulk loading, the reference directly recommends “drop index, load data, recreate indexes,” without limiting this to a maintenance window or rebuildable staging table and without ensuring restoration after a failed load. Dropping an index is a persistent schema change.

Why this matters

If followed on a production table, queries may become much slower and parent-table updates or deletes may encounter worse lock waits. If the workflow fails before recreation, the lost performance protection persists.

This is direct guidance for insert-heavy workloads, and dropping an index is a persistent schema change. The context legitimately concerns bulk-load optimization, but it does not limit the operation to staging tables or maintenance windows, nor ensure recreation after a failed load. If an agent applies it to a production table, query performance, uniqueness enforcement, or recovery may be affected. Users can require explicit approval for each table and verification of an index's purpose before removal.

references/details.md:46In the instructionsOpen original file
### Insert-heavy workloads- **Minimize indexes**—only create what you query; every index slows inserts.- **Use `COPY` or multi-row `INSERT`** instead of single-row inserts.- **UNLOGGED tables** for rebuildable staging data—much faster writes.- **Defer index creation** for bulk loads—>drop index, load data, recreate indexes.- **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data.- **Use a natural key for primary key** such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.
Could it bypass safety checks?Looks for skipped website security checks, excessive file access, or actions that skip your approval.Risks found: 1
High risk

The row-level security example may trust a user ID that a database session can spoof

Source references: 1
What we found

The guidance suggests comparing row ownership with `current_setting('app.user_id')`, but does not require that only trusted database-side code can set it or explain that table owners and some privileged roles can bypass RLS. If an ordinary application session can change the custom setting, the policy treats a self-declared identity as authorization.

Why this matters

A database client able to run `SET app.user_id` or an equivalent operation could impersonate another user and access or modify that user's rows. An application role that bypasses RLS may evade the policy entirely.

This is active design guidance, not a warning or negated example. It permits an RLS policy to trust an application-set `app.user_id`, but the visible text does not require that only a trusted role can set it or explain which database roles may bypass RLS. If an ordinary application session can change the setting, a user could claim another ID and access its rows. Users can ask the author to specify role separation, connection-pool handling, and forced-RLS requirements.

references/details.md:33In the instructionsOpen original file
## Row-level securityEnable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (owner = current_user)` when database roles map to users, or compare against an app-set setting such as `current_setting('app.user_id')::bigint`. Built-in user-based access control at the row level.
Could it mislead the AI or hide text?Checks the skill instructions for requests to ignore you, influence the report, or hide text in invisible characters.No risks found
Could it change links or payment recipients without asking?Looks for forced referral or payment changes combined with instructions to hide the change.No risks found

Inside this skill

8 instruction sections

This Skill is a PostgreSQL schema-design reference covering data types, constraints, indexes, partitioning, row-level security, and schema changes. It provides advice and SQL examples but does not instruct the agent to connect to a database, read credentials, access the network, or execute commands automatically.

View source
SKILL.md:10In the instructionsOpen original file
- Designing a new PostgreSQL schema, or reviewing one before it ships.- Choosing column types, keys, constraints, or indexes for PostgreSQL specifically.- Deciding whether and how to partition a large table, or how to store semi-structured data.- Planning a schema change on a live database without downtime.

The main file directs the agent to open the bundled reference only when a relevant section points to it; the supplied target is present and remains PostgreSQL design guidance.

View source
SKILL.md:15In the instructionsOpen original file
The rules and decision points for a PostgreSQL schema. The full data-type catalog, workloadpatterns (update-heavy, insert-heavy, upsert, schema evolution), extensions, JSONB indexing,and worked DDL examples are in `references/details.md`; open it when a section below points there.
references/details.md:3In the instructionsOpen original file
Deep material for the `postgresql-table-design` skill. `SKILL.md` carries the rules andthe decision points; this file carries the full data-type catalog, workload patterns,extensions, JSONB indexing, and worked DDL.
Start here · InstructionsSKILL.md
postgresql-table-design
Lines connect the instruction file to its sections, not an observed execution order. Select a section to read the source. 1 more sections are available in the original file.

File reference map

References: 1
Files making referencesReferenced content
Lines show actual file references, not execution order. Select a node to highlight its connections and inspect the files and source locations. Dashed lines include files that still need locating.
Files and check records2 files

Coverage and gaps

Content covered in each file

These are the source ranges included in this check, not a guarantee that every issue has been resolved.

  • SKILL.mdFull text included
  • references/details.mdFull text included

This report is for the version above. We read the available code and instructions without running the skill or checking extra packages it installs. This is not a promise of safety: a different version or setup may behave differently.

  • SKILL.mdInstructions
  • references/details.mdSupporting file
Lines read
292
File checksum (to compare versions)
d176dd6356e5e758b88afbceae5b7c26c764631b9a96829a3df73b7bb82c6052