Skip to content
skillsafebeta
← Report library
Purpose / Data analysis

Supabase Postgres Best Practices Skill Security Audit

What the author says it does (original text)

Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and schedule

Independent security check

Do not install or run it yet

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

Global timeout examples persist server changes and terminate sessions or transactions

Source references: 1
What we found

The recommended solution uses `ALTER SYSTEM` to set a 30-second idle-in-transaction timeout and a 10-minute idle-session timeout, then reloads configuration. These are persistent server-wide changes, not temporary settings for the connection being diagnosed; terminated idle transactions roll back.

Why this matters

Legitimate interactive transactions, migrations, administration tools, or pooled sessions may disconnect or roll back unexpectedly, causing application errors, failed work, and outages.

The recommended code uses `ALTER SYSTEM` and reloads configuration, making a server-level change rather than limiting it to the diagnosed session. Once active, idle-in-transaction connections are terminated after 30 seconds and fully idle connections after 10 minutes; uncommitted work in the former rolls back and all applications on the instance may be affected. Users can require workload/pool assessment, session- or role-scoped alternatives, production approval, and a rollback plan.

references/conn-idle-timeout.md:25In the instructionsOpen original file
**Correct (automatic cleanup of idle connections):**```sql-- Terminate connections idle in transaction after 30 secondsalter system set idle_in_transaction_session_timeout = '30s';-- Terminate completely idle connections after 10 minutesalter system set idle_session_timeout = '10min';-- Reload configurationselect pg_reload_conf();```
Medium risk

General use of EXPLAIN ANALYZE can execute writes or expensive operations during diagnosis

Source references: 3
What we found

The guide explicitly says `EXPLAIN ANALYZE` executes the query, while the Skill applies broadly to slow-query diagnosis. Its example is a SELECT, but it does not warn agents that applying the same method to INSERT, UPDATE, DELETE, side-effecting functions, or a severely expensive query performs the real operation or load.

Why this matters

A diagnostic action could modify or delete production data, trigger external side effects, or rerun an expensive query and worsen an overloaded database.

The guide explicitly says `EXPLAIN ANALYZE` executes the query and presents it as the “correct” slow-query diagnostic, while the Skill broadly triggers for slow queries. Its command is only a SELECT and gives no warning that applying the pattern to INSERT, UPDATE, DELETE, side-effecting functions, or extremely expensive queries can modify data or load production. Users can restrict it to read-only statements and require write analysis in a rollback transaction or replica with timeouts.

SKILL.md:3In the instructionsOpen original file
name: supabase-postgres-best-practicesdescription: "Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pg_cron, pgmq), vector/semantic search (pgvector), and restoring dumps (pg_restore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query."license: MIT
Show 2 other places
references/monitor-explain-analyze.md:8In the instructionsOpen original file
## Use EXPLAIN ANALYZE to Diagnose Slow QueriesEXPLAIN ANALYZE executes the query and shows actual timings, revealing the true performance bottlenecks.
references/monitor-explain-analyze.md:20In the instructionsOpen original file
**Correct (use EXPLAIN ANALYZE):**```sqlexplain (analyze, buffers, format text)select * from orders where customer_id = 123 and status = 'pending';
Could it expose your files or keys?Looks for uploads of files containing passwords or keys, and keys written directly in the code.Risks found: 2
High risk

A client-settable session variable is trusted as tenant identity, enabling cross-tenant access

Source references: 3
What we found

The recommended RLS policy trusts `app.current_user_id`, then demonstrates assigning it with an ordinary `SET`. PostgreSQL custom session parameters do not authenticate the caller. If an application role or user can execute arbitrary SQL or influence that setting, they may substitute another user's ID.

Why this matters

An attacker could read or modify another user's orders. `FORCE ROW LEVEL SECURITY` does not repair the untrusted identity source because the policy still accepts the forged value.

The “correct” example directly uses a custom session parameter, writable with `SET`, as the RLS identity. It shows no trusted binding to the authenticated user. If an application role can run arbitrary SQL, reuse a contaminated pooled connection, or influence this setting, it could select another user ID and read that user's orders. Users can ask for an unforgeable authentication claim and restrict who may set identity context, with pool reset verification.

references/security-rls-basics.md:28In the instructionsOpen original file
-- Create policy for users to see only their orderscreate policy orders_user_policy on orders  for all  using (user_id = current_setting('app.current_user_id')::bigint);-- Force RLS even for table ownersalter table orders force row level security;-- Set user context and queryset app.current_user_id = '123';select * from orders;  -- Only returns orders for user 123```
Show 2 other places
references/security-rls-basics.md:41In the instructionsOpen original file
Policy for authenticated role:```sqlcreate policy orders_user_policy on orders  for all  to authenticated  using (user_id = auth.uid());```
references/security-rls-basics.md:36In the instructionsOpen original file
-- Set user context and queryset app.current_user_id = '123';select * from orders;  -- Only returns orders for user 123```
High risk

The “correct” partitioning example directly drops a table and all data in that partition

Source references: 3
What we found

The guide recommends `drop table events_2023_01` as a fast way to remove old data. That operation deletes the partition and all its rows, without requiring confirmation of retention policy, detachment or archival, dependency checks, or a verified backup.

Why this matters

An agent adapting the example during a migration or optimization could permanently remove historical events that must be retained and disrupt dependent objects or audit records.

The partitioning guide recommends `DROP TABLE` as a fast way to remove old data; executing it deletes the partition and all its rows. Although the condition is described as old data, the source requires no retention confirmation, archive, backup, or dependency check, so an agent applying it could cause unrecoverable data loss. Users can require explicit approval, verified backups, and dependency checks, and prohibit unapproved DROP operations.

SKILL.md:3In the instructionsOpen original file
name: supabase-postgres-best-practicesdescription: "Postgres best practices maintained by Supabase, for Postgres running anywhere. Load this skill BEFORE writing or changing anything that lives in a Postgres database: creating or altering tables and columns (including choosing column types), schema design, migrations and declarative schema files, RLS policies and the tests that verify them, indexes, triggers, database functions, queues and scheduled jobs (pg_cron, pgmq), vector/semantic search (pgvector), and restoring dumps (pg_restore) or importing data. Also load it when diagnosing slow queries, high CPU, timeouts, EXPLAIN plans, connection exhaustion, locking, bloat, or rows visible to the wrong user or tenant. This is not just a performance guide — schema, migration, security, and SQL authoring tasks need these rules too, even for a one-column change or a single query."license: MIT
Show 2 other places
references/schema-partitioning.md:42In the instructionsOpen original file
-- Queries only scan relevant partitionsselect * from events where created_at > '2024-01-15';  -- Only scans events_2024_01+-- Drop old data instantlydrop table events_2023_01;  -- Instant vs DELETE taking hours```When to partition:- Tables > 100M rows- Time-series data with date-based queries- Need to efficiently drop old data
references/schema-partitioning.md:49In the instructionsOpen original file
When to partition:- Tables > 100M rows- Time-series data with date-based queries- Need to efficiently drop old data
Could it delete files or keep running?Looks for broad file deletion, disk overwrites, and programs set to start automatically.No risks found
Could it bypass safety checks?Looks for skipped website security checks, excessive file access, or actions that skip your approval.Risks found: 1
Medium risk

Some “idempotent” constraint checks match only by name and may silently skip required constraints

Source references: 3
What we found

The recommended “all constraint types” examples query only `conname`, without limiting the check to the target table or schema. If another PostgreSQL table already has a constraint with that name, the block treats it as present and does not add the CHECK or foreign key to the intended table.

Why this matters

A migration may appear successful while omitting data-integrity enforcement, allowing invalid ages, orphaned references, or other data that violates business rules.

Both the CHECK and foreign-key examples under “all constraint types” test only `conname`, without restricting `conrelid` or schema. If another table already has the same constraint name, `if not exists` becomes false and the migration silently skips the required target-table constraint, weakening data integrity. Users can ask the author to match the target table as in the earlier profiles example and verify the resulting definition after migration.

references/schema-constraints.md:37In the instructionsOpen original file
For all constraint types:```sql-- Check constraintsdo $$begin  if not exists (    select 1 from pg_constraint    where conname = 'check_age_positive'  ) then    alter table users add constraint check_age_positive check (age > 0);  end if;end $$;-- Foreign keysdo $$begin  if not exists (    select 1 from pg_constraint    where conname = 'profiles_birthchart_id_fkey'  ) then    alter table profiles    add constraint profiles_birthchart_id_fkey    foreign key (birthchart_id) references birthcharts(id);  end if;end $$;```
Show 2 other places
references/schema-constraints.md:40In the instructionsOpen original file
```sql-- Check constraintsdo $$begin  if not exists (    select 1 from pg_constraint    where conname = 'check_age_positive'  ) then    alter table users add constraint check_age_positive check (age > 0);  end if;end $$;
references/schema-constraints.md:51In the instructionsOpen original file
-- Foreign keysdo $$begin  if not exists (    select 1 from pg_constraint    where conname = 'profiles_birthchart_id_fkey'  ) then    alter table profiles    add constraint profiles_birthchart_id_fkey    foreign key (birthchart_id) references birthcharts(id);  end if;
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

4 instruction sections

This Skill is a reference set for agents writing SQL, designing schemas, managing connections, and working with RLS. Its entrypoint directs the agent to individual reference files rather than an installer, but agents may still copy the SQL examples into a real database.

View source
SKILL.md:19In the instructionsOpen original file
Reference these guidelines when:- Writing SQL queries or designing schemas- Implementing indexes or query optimization- Reviewing database performance issues- Configuring connection pooling or scaling- Optimizing for Postgres-specific features- Working with Row-Level Security (RLS)
SKILL.md:40In the instructionsOpen original file
## How to UseRead individual rule files for detailed explanations and SQL examples:```references/query-missing-indexes.mdreferences/query-partial-indexes.mdreferences/_sections.md```

The material intentionally presents problematic examples before corrected ones. An agent that ignores labels or copies an isolated fragment could adopt SQL meant as an anti-pattern; the anti-patterns themselves should not be treated as execution instructions.

View source
references/_contributing.md:15In the instructionsOpen original file
### 2. Error-First StructureAlways show the problematic pattern first, then the solution. This trains agentsto recognize anti-patterns.```markdown**Incorrect (sequential queries):** [bad example]**Correct (batched query):** [good example]```

The version information is inconsistent: the entrypoint metadata says 1.1.1, while the changelog starts at 1.6.0. These files alone do not clearly establish which release—and which security fixes—the user is receiving.

View source
SKILL.md:7In the instructionsOpen original file
  author: supabase  version: "1.1.1"  organization: Supabase  date: January 2026  abstract: Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.
CHANGELOG.md:3In the instructionsOpen original file
## [1.6.0](https://github.com/supabase/agent-skills/compare/supabase-postgres-best-practices-v1.5.0...supabase-postgres-best-practices-v1.6.0) (2026-07-30)
Start here · InstructionsSKILL.md
supabase-postgres-best-practices
Lines connect the instruction file to its sections, not an observed execution order. Select a section to read the source.

File reference map

References: 3
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 records36 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/_sections.mdFull text included
  • references/query-missing-indexes.mdFull text included
  • references/query-partial-indexes.mdFull text included
  • CHANGELOG.mdFull text included
  • references/_contributing.mdFull text included
  • references/_template.mdFull text included
  • references/advanced-full-text-search.mdFull text included
  • references/advanced-jsonb-indexing.mdFull text included
  • references/conn-idle-timeout.mdFull text included
  • references/conn-limits.mdFull text included
  • references/conn-pooling.mdFull text included
  • references/conn-prepared-statements.mdFull text included
  • references/data-batch-inserts.mdFull text included
  • references/data-n-plus-one.mdFull text included
  • references/data-pagination.mdFull text included
  • references/data-upsert.mdFull text included
  • references/lock-advisory.mdFull text included
  • references/lock-deadlock-prevention.mdFull text included
  • references/lock-short-transactions.mdFull text included
  • references/lock-skip-locked.mdFull text included
  • references/monitor-explain-analyze.mdFull text included
  • references/monitor-pg-stat-statements.mdFull text included
  • references/monitor-vacuum-analyze.mdFull text included
  • references/query-composite-indexes.mdFull text included
  • references/query-covering-indexes.mdFull text included
  • references/query-index-types.mdFull text included
  • references/schema-constraints.mdFull text included
  • references/schema-data-types.mdFull text included
  • references/schema-foreign-key-indexes.mdFull text included
  • references/schema-lowercase-identifiers.mdFull text included
  • references/schema-partitioning.mdFull text included
  • references/schema-primary-keys.mdFull text included
  • references/security-privileges.mdFull text included
  • references/security-rls-basics.mdFull text included
  • references/security-rls-performance.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.

  • CHANGELOG.mdSupporting file
  • SKILL.mdInstructions
  • references/_contributing.mdSupporting file
  • references/_sections.mdSupporting file
  • references/_template.mdSupporting file
  • references/advanced-full-text-search.mdSupporting file
  • references/advanced-jsonb-indexing.mdSupporting file
  • references/conn-idle-timeout.mdSupporting file
  • references/conn-limits.mdSupporting file
  • references/conn-pooling.mdSupporting file
  • references/conn-prepared-statements.mdSupporting file
  • references/data-batch-inserts.mdSupporting file
  • references/data-n-plus-one.mdSupporting file
  • references/data-pagination.mdSupporting file
  • references/data-upsert.mdSupporting file
  • references/lock-advisory.mdSupporting file
  • references/lock-deadlock-prevention.mdSupporting file
  • references/lock-short-transactions.mdSupporting file
  • references/lock-skip-locked.mdSupporting file
  • references/monitor-explain-analyze.mdSupporting file
  • references/monitor-pg-stat-statements.mdSupporting file
  • references/monitor-vacuum-analyze.mdSupporting file
  • references/query-composite-indexes.mdSupporting file
  • references/query-covering-indexes.mdSupporting file
  • references/query-index-types.mdSupporting file
  • references/query-missing-indexes.mdSupporting file
  • references/query-partial-indexes.mdSupporting file
  • references/schema-constraints.mdSupporting file
  • references/schema-data-types.mdSupporting file
  • references/schema-foreign-key-indexes.mdSupporting file
  • references/schema-lowercase-identifiers.mdSupporting file
  • references/schema-partitioning.mdSupporting file
  • references/schema-primary-keys.mdSupporting file
  • references/security-privileges.mdSupporting file
  • references/security-rls-basics.mdSupporting file
  • references/security-rls-performance.mdSupporting file

Operations mentioned in code and instructions

Connect to websites
CHANGELOG.md:3In the instructionsOpen original file
## [1.6.0](https://github.com/supabase/agent-skills/compare/supabase-postgres-best-practices-v1.5.0...supabase-postgres-best-practices-v1.6.0) (2026-07-30)
CHANGELOG.md:8In the instructionsOpen original file
* add schema-constraints reference for safe migration patterns ([#30](https://github.com/supabase/agent-skills/issues/30)) ([9b236f3](https://github.com/supabase/agent-skills/commit/9b236f3ebd65d76a2c570f19931353da9c858d5a))* using Supabase agent skills ([#12](https://github.com/supabase/agent-skills/issues/12)) ([7c2e389](https://github.com/supabase/agent-skills/commit/7c2e3894fddfde8eb6c77d2a8921904543b9be7a))
CHANGELOG.md:9In the instructionsOpen original file
* add schema-constraints reference for safe migration patterns ([#30](https://github.com/supabase/agent-skills/issues/30)) ([9b236f3](https://github.com/supabase/agent-skills/commit/9b236f3ebd65d76a2c570f19931353da9c858d5a))* using Supabase agent skills ([#12](https://github.com/supabase/agent-skills/issues/12)) ([7c2e389](https://github.com/supabase/agent-skills/commit/7c2e3894fddfde8eb6c77d2a8921904543b9be7a))
Lines read
2,030
File checksum (to compare versions)
8bf87a81fc598f58400cea081a2ff143a8faae7a08ce7afeae09cb41568cc6ef