{ }
Resource profile / PostgreSQL Performance & Schema Review
About this skill

Workflow & requirements

BB Skills packaging and scoped native SQL evidence (2026-10-07)

The original Supabase PostgreSQL guidance is pinned to c9be0e931b7930f7d02126d04774d904c381e7d7. All 37 upstream files and the complete MIT notice are retained and checked against Git blob IDs and SHA-256. Original SKILL.md remains unchanged in upstream-original/. This marked preface, examples and evidence are independent BB Skills additions, not Supabase certification. Catalog package version c9be0e931b79.bb1 does not change upstream metadata version 1.1.1.

The disposable PostgreSQL 17.11 experiment executes four unchanged SQL blocks plus independent ordinary-role and error controls. Forty named observations passed with Python 3.13.16 and psycopg 3.2.10. Writer, reader, ordinary table owner and unprivileged role use separate real database connections with no superuser, database creation, role creation, replication or BYPASSRLS attributes. Bootstrap privileges are restricted to fixture setup and an explicit bypass control; they are not the application permission model.

Observations separate table GRANTs from row policies, matching writes from other-identity writes, SQLSTATE denials from zero-row updates, implicit WITH CHECK from USING, forced owner filtering from owner/superuser bypasses, permissive policy widening, and transaction-local context restoration. The fixture deliberately demonstrates that a caller can change app.current_user_id: this custom setting is not authentication. Missing or malformed values fail only under the recorded expression and environment.

The original index statement is first rejected for an ordinary owner lacking schema CREATE, then executed by the isolated setup role. Its definition is checked; no speedup or query-plan benchmark is measured. The first fixture attempt stopped at that permission error. Only the corrected runner and its resulting 40-observation evidence are packaged. The older missing-index experiment remains historical evidence for the old catalog version and checksum.

No hosted Supabase, auth.uid(), private SECURITY DEFINER helper, real identity, connection pool, concurrency, production authorization, paid API or complete AI-client skill execution is tested. The internal Docker network has no host ports or external access. Dependency installation happens separately. runtime_tested remains false. Read examples/README.md before reproducing: the script accepts only the named empty disposable database and does not provide production policy installation.

Supabase Postgres Best Practices

Comprehensive performance optimization guide for Postgres, maintained by Supabase. Contains rules across 8 categories, prioritized by impact to guide automated query optimization and schema design.

When to Apply

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)

Rule Categories by Priority

Priority Category Impact Prefix
1 Query Performance CRITICAL query-
2 Connection Management CRITICAL conn-
3 Security & RLS CRITICAL security-
4 Schema Design HIGH schema-
5 Concurrency & Locking MEDIUM-HIGH lock-
6 Data Access Patterns MEDIUM data-
7 Monitoring & Diagnostics LOW-MEDIUM monitor-
8 Advanced Features LOW advanced-

How to Use

Read individual rule files for detailed explanations and SQL examples:

references/query-missing-indexes.md
references/query-partial-indexes.md
references/_sections.md

Each rule file contains: - Brief explanation of why it matters - Incorrect SQL example with explanation - Correct SQL example with explanation - Optional EXPLAIN output or metrics - Additional context and references - Supabase-specific notes (when applicable)

References

  • https://www.postgresql.org/docs/current/
  • https://supabase.com/docs
  • https://wiki.postgresql.org/wiki/Performance_Optimization
  • https://supabase.com/docs/guides/database/overview
  • https://supabase.com/docs/guides/auth/row-level-security
PACKAGE TRANSPARENCY

Inspect before installing

Source: Supabase · MIT · SHA-256 shown alongside the download.

48 files56211 ZIP bytes1 script/code file

License file included. A license and checksum are not a security certification. Review package instructions and scripts before running them.

View files and uncompressed sizes
Machine-readable installation guide →
CATALOG REVIEW NOTES

Know what you need before installing

Source and packaging checks recorded on 2026-10-07. These notes are not safety certification or measured task performance.

Requirements

PostgreSQL 17 disposable Docker database; Linux Python with psycopg 3.2.10. Recorded Python 3.13.16/PG 17.11. Separate ordinary-role connections; explicit fresh fixture/input guards.

Costs, access & practical limits

Free MIT guidance and a synthetic internal-network experiment. No hosted Supabase, credential for a real service, paid API or model call. Dependency installation is separate. This is not authentication, production authorization or comprehensive security verification.

View the recorded checks
  • 37 original files verified by Git blob ID and SHA-256; full MIT notices retained
  • Four unchanged upstream SQL blocks plus 40 named observations in disposable PostgreSQL 17.11
  • Ordinary writer, reader, owner and unprivileged connections checked separately
  • GRANT versus RLS, implicit write checks, zero-row updates and SQLSTATE errors distinguished
  • Caller-modifiable claim, owner/superuser bypass and permissive widening counterexamples retained
  • Native index definition checked by setup role; no performance claim or full AI-client execution

Upstream commit: c9be0e931b7930f7d02126d04774d904c381e7d7

Runtime status: not tested by this catalog. Configure your client and test the skill in your own environment.

LICENSE & ATTRIBUTION

License & attribution

The notices identify their files, attribution and changes. Retain the applicable original notices when adapting or redistributing the package.

MIT ↗

Original Supabase skill, changelog, reference rules and upstream notices

Attribution: Copyright (c) 2026 Supabase

Changes: Original bytes unchanged; primary preface is separately marked

Included notice: supabase-postgres-best-practices/LICENSE.upstream.txt

Declared source ↗

MIT ↗

Independent BB Skills preface, local runner, scoped evidence and packaging

Attribution: Copyright (c) 2026 BB Skills

Changes: Independent synthetic SQL controls and packaging; not production policies

Included notice: supabase-postgres-best-practices/LICENSE.bb-skills.txt

Declared source ↗
SCENARIOS

Inputs, criteria and recorded outcomes

Records are supplied by the site administrator and bound to a specific package. They are not third-party safety certification. This page does not execute skills.

Native PostgreSQL RLS snippets and ordinary-role controls

Reported passed · vc9be0e931b79.bb1

View input and acceptance criteria

Input

Forty named observations across four unchanged upstream SQL blocks and independent synthetic controls in disposable PostgreSQL 17.11. Separate ordinary writer, reader, table owner and unprivileged connections distinguish GRANTs, RLS filtering, implicit write checks, SQLSTATE errors, zero-row updates, owner/superuser bypass, permissive policy widening and transaction-local context. Caller-modifiable session claims are not authentication. Index setup is verified, not a performance benchmark. No hosted Supabase, auth.uid(), private SECURITY DEFINER helper, real identity, pool reuse, concurrency, production authorization, paid API or complete AI-client skill execution.

Acceptance criteria

Forty named observations pass in the recorded disposable fixture. Caller-modifiable identity and bypass counterexamples remain explicit; no full-skill, real identity or production security claim.

Recorded outcome

Forty named observations across four unchanged upstream SQL blocks and independent synthetic controls in disposable PostgreSQL 17.11. Separate ordinary writer, reader, table owner and unprivileged connections distinguish GRANTs, RLS filtering, implicit write checks, SQLSTATE errors, zero-row updates, owner/superuser bypass, permissive policy widening and transaction-local context. Caller-modifiable session claims are not authentication. Index setup is verified, not a performance benchmark. No hosted Supabase, auth.uid(), private SECURITY DEFINER helper, real identity, pool reuse, concurrency, production authorization, paid API or complete AI-client skill execution.

Recorded fixture evidence:
{
  "executed_at": "2026-10-07T10:28:40.654988+00:00",
  "python": "3.13.16",
  "psycopg": "3.2.10",
  "postgresql_version": "17.11",
  "source_commit": "c9be0e931b7930f7d02126d04774d904c381e7d7",
  "original_files_verified": 37,
  "observations": 40,
  "cases": [
    {
      "name": "writer_has_no_elevated_role_attributes",
      "status": "passed",
      "detail": null
    },
    {
      "name": "reader_has_no_elevated_role_attributes",
      "status": "passed",
      "detail": null
    },
    {
      "name": "owner_has_no_elevated_role_attributes",
      "status": "passed",
      "detail": null
    },
    {
      "name": "unprivileged_has_no_elevated_role_attributes",
      "status": "passed",
      "detail": null
    },
    {
      "name": "native_writer_inherits_only_declared_order_privileges",
      "status": "passed",
      "detail": [
        true,
        true,
        true,
        false,
        false
      ]
    },
    {
      "name": "native_reader_can_select_declared_product_table",
      "status": "passed",
      "detail": null
    },
    {
      "name": "native_reader_cannot_select_ungranted_orders",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "ordinary_writer_sees_all_rows_before_RLS",
      "status": "passed",
      "detail": null
    },
    {
      "name": "native_RLS_block_filters_ordinary_owner",
      "status": "passed",
      "detail": null
    },
    {
      "name": "native_policy_enabled_forced_all_and_implicit_check",
      "status": "passed",
      "detail": [
        true,
        true,
        "*",
        true,
        true,
        true
      ]
    },
    {
      "name": "ordinary_writer_sees_own_rows",
      "status": "passed",
      "detail": null
    },
    {
      "name": "caller_can_change_session_claim_to_other_identity",
      "status": "passed",
      "detail": null
    },
    {
      "name": "superuser_bypasses_even_forced_RLS",
      "status": "passed",
      "detail": null
    },
    {
      "name": "missing_session_context_reports_error",
      "status": "passed",
      "detail": {
        "sqlstate": "42704"
      }
    },
    {
      "name": "invalid_session_context_reports_cast_error",
      "status": "passed",
      "detail": {
        "sqlstate": "22P02"
      }
    },
    {
      "name": "granted_reader_is_filtered_by_RLS",
      "status": "passed",
      "detail": null
    },
    {
      "name": "reader_insert_denied_by_table_privilege",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "policy_does_not_grant_table_access",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "native_all_policy_allows_matching_insert",
      "status": "passed",
      "detail": null
    },
    {
      "name": "implicit_with_check_rejects_other_identity_insert",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "implicit_with_check_rejects_ownership_change",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "rejected_ownership_change_leaves_row_unchanged",
      "status": "passed",
      "detail": null
    },
    {
      "name": "other_identity_update_affects_zero_rows",
      "status": "passed",
      "detail": null
    },
    {
      "name": "other_identity_row_remains_unchanged",
      "status": "passed",
      "detail": null
    },
    {
      "name": "matching_update_succeeds",
      "status": "passed",
      "detail": null
    },
    {
      "name": "writer_delete_denied_by_table_privilege",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "writer_truncate_denied_by_table_privilege",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "table_owner_without_schema_create_cannot_create_index",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "native_RLS_column_index_created",
      "status": "passed",
      "detail": "CREATE INDEX orders_user_id_idx ON public.orders USING btree (user_id)"
    },
    {
      "name": "ordinary_table_owner_bypasses_without_force",
      "status": "passed",
      "detail": null
    },
    {
      "name": "force_restores_owner_filtering",
      "status": "passed",
      "detail": null
    },
    {
      "name": "permissive_extra_policy_widens_select",
      "status": "passed",
      "detail": null
    },
    {
      "name": "removing_extra_policy_restores_select_filter",
      "status": "passed",
      "detail": null
    },
    {
      "name": "transaction_local_context_selects_other_identity",
      "status": "passed",
      "detail": null
    },
    {
      "name": "transaction_local_context_restores_session_value",
      "status": "passed",
      "detail": null
    },
    {
      "name": "enabled_RLS_without_policy_hides_rows",
      "status": "passed",
      "detail": null
    },
    {
      "name": "enabled_RLS_without_policy_rejects_insert",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "row_security_off_does_not_bypass_policy",
      "status": "passed",
      "detail": {
        "sqlstate": "42501"
      }
    },
    {
      "name": "restored_row_security_keeps_matching_rows",
      "status": "passed",
      "detail": null
    },
    {
      "name": "fixture_retains_other_identity_and_no_failed_inserts",
      "status": "passed",
      "detail": null
    }
  ],
  "native_SQL_blocks_executed": 4,
  "executed_blocks": [
    {
      "source": "references/security-privileges.md",
      "block_index": 1,
      "block_sha256": "96ac0ee0f45b65d60ba4516f7071190df95124bc3661c3fc682a80aaa52225c3",
      "source_rewritten": false
    },
    {
      "source": "references/security-privileges.md",
      "block_index": 2,
      "block_sha256": "199ad4c20d41db0ff8be9799da80fa6bfda6ace4f96d0b98e9a83178a72f109b",
      "source_rewritten": false
    },
    {
      "source": "references/security-rls-basics.md",
      "block_index": 1,
      "block_sha256": "7ace28d7ee4101a5888b5ff0086edc733032f20f940237d097fb6b519c3c113e",
      "source_rewritten": false
    },
    {
      "source": "references/security-rls-performance.md",
      "block_index": 3,
      "block_sha256": "487dc32b09c68e24f7d184fc763b1f73a085a2ff0009a6e20f27675d4b7d9629",
      "source_rewritten": false
    }
  ],
  "role_attributes": {
    "writer": [
      false,
      false,
      false,
      false,
      false
    ],
    "reader": [
      false,
      false,
      false,
      false,
      false
    ],
    "owner": [
      false,
      false,
      false,
      false,
      false
    ],
    "unprivileged": [
      false,
      false,
      false,
      false,
      false
    ]
  },
  "source_hashes": {
    "references/security-privileges.md": "095909274a4fb43df785d7144cf674eeb40284d2d45628f14af4108737b93663",
    "references/security-rls-basics.md": "e04dd1a3a382aafdaaa73fe9f7976352acdde6e90c2a2470d281b94d4f44b096",
    "references/security-rls-performance.md": "2e1faeca7d5746e246b1c78f9492fe2a9cfa41c4d7fa1e23c402d58fa18ea014"
  },
  "runner_sha256": "5787a86e5c1f10dcf19cfa54c91541df9e8c7e44ad20625d8484ac7a3b450c41",
  "server_image_id": "sha256:b0f9560a2de083e2cc7382e75f808c7381a32852a7ec49117deedb300e552b24",
  "client_image_id": "sha256:5291689d3752b989c53242046453362888b9e371c1dac4491eb00729f6bad6f9",
  "runtime_tested": false,
  "ai_client_executed": false,
  "production_tested": false,
  "hosted_supabase_used": false,
  "real_identity_verified": false,
  "paid_API_used": false,
  "performance_speedup_measured": false,
  "external_network_access": false,
  "synthetic_data_only": true,
  "scope": "Four unchanged upstream SQL blocks plus independent role/claim/error controls in an empty PostgreSQL 17 fixture. Ordinary roles connect separately; bootstrap superuser is only setup and explicit bypass control. Session variables are deliberately caller-modifiable and are not authentication. No auth.uid(), hosted Supabase, private SECURITY DEFINER helper, concurrency, production authorization or full AI-client execution."
}

Environment

Linux Docker internal network, no host ports/external access; PostgreSQL 17.11, Python 3.13.16, psycopg 3.2.10; disposable tmpfs database and synthetic roles/orders. Server 1 CPU/512 MiB, client 1 CPU/256 MiB; no production volumes or credentials.

Package SHA-256: 09e7272645b0adb69e18d05b2ff72ec9f4bb0ed69e06a3512c3393522fc9c0f8

Outcome recorded: 2026-10-07 10:28 UTC

Open evidence ↗

Missing-index SQL recipe (isolated synthetic workload)

Reported passed · vc9be0e931b79 · Package changed — rerun needed

View input and acceptance criteria

Input

Create an isolated PostgreSQL 17 database with 200,000 synthetic orders and 10,000 customer IDs. Filter customer_id = 123. Compare EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) before and after CREATE INDEX orders_customer_id_idx ON orders (customer_id). Take one warmup and five measured runs per condition. Do not connect to a production database.

Acceptance criteria

Both result sets contain the same 20 rows. The baseline uses Seq Scan; the indexed plan uses orders_customer_id_idx. Record actual timings and limitations; no speedup threshold is required. Scope: one SQL recipe, not an agent benchmark.

Recorded outcome

Scope: direct SQL recipe validation, not an AI-agent end-to-end evaluation.
200,000 synthetic orders; customer_id = 123 matches 20 rows.
Before: Seq Scan. After: Bitmap Heap Scan with orders_customer_id_idx.
Result rows and SHA-256 match before and after.
Five warmed measurements: median 12.676 ms before; 0.152 ms after.
Timing is specific to this workload, cache, and hardware. Runs are sequential, not randomized.
No production queries, third-party scripts, or paid AI calls were used.
Only the missing-index recipe was checked; no full-skill effectiveness or safety certification is implied.
Result SHA-256: f72cd8761469a9f1c53d4d7fd478d05857f685a5395e7300ae98ad696cdac8fb
Probe script SHA-256: 0a429cdfed4779f645767cd945d08cda58df801be271037ae362cb2f191337b7

Environment

PostgreSQL 17.11 on x86_64-pc-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit; Docker isolated database; 1 CPU and 512 MiB database limit; parallel query disabled.

Package SHA-256: 2e93a64c76cbde8d4dd1b7d8c6896f21492d0425b8ff25fcc69d97769b4f0e3d

Outcome recorded: 2026-10-02 17:17 UTC

Community reviews

★ New

Be the first to share your experience.

Sign in to leave a review →

Guides using this resource

All guides →
Practical guide

PostgreSQL RLS tests: roles, writes and session context

PostgreSQL row-level security needs tests that use the same ordinary database roles as the application. A successful query from a superuser cannot establish that another user's…

By GX AI · Read guide →
View all ↗

Links use published guide associations and catalog labels. Inspect each resource’s requirements and license.