> ## Documentation Index
> Fetch the complete documentation index at: https://docs.tuturuuu.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Definer guard and daily security scans

> Review migration execution policies and triage the read-only daily security inventory.

The PR-time guard checks `apps/database/supabase/migrations`. The `security-scan` workflow runs daily at **03:00 GMT+7** and supports manual dispatch from the default branch. It has only `contents: read` permission, uses no secrets, and never applies migrations, changes aliases, or writes database or repository state. Existing Security Checks and CodeQL coverage continue independently.

## Definer policy

New and changed `SECURITY DEFINER` functions must declare `SET search_path`, revoke default execution from `PUBLIC`, and revoke `anon` execution or provide an explicit restricted grant list. A `GRANT EXECUTE ... TO authenticated` alone leaves PostgreSQL default `PUBLIC` access intact. ACL statements must match the function overload. Standalone grants in later migrations are also checked against earlier definer definitions in migration filename order. Role memberships and EXECUTE grants are replayed across migrations; direct and transitive inheritance by `anon` or `PUBLIC` is also rejected, and membership revokes remove those paths. Grants to `anon` or `PUBLIC` are rejected; `authenticated` remains permitted by this guard. Grant exceptions bind the granting migration, earlier definition and ACL digest. The parser checks every target in multi-routine lists and all definers in each schema named by a bulk `ALL FUNCTIONS`, `ALL PROCEDURES` or `ALL ROUTINES` grant. Function, routine and procedure ACL forms share conservative matching. Built-in type aliases, `pg_catalog` qualification, argument modes/names and array spellings normalize for lookup without rewriting historical baseline identities. Unqualified routine names can resolve through `search_path`, so grants match possible same-name targets across schemas. Unknown types match by name and arity. Ambiguous or unqualified revokes cannot establish that a qualified unsafe grant was removed. Use qualified names and explicit, known overload types for verifiable revokes.

Executable privilege mutations inside `DO`, function or procedure bodies require review: dynamic `EXECUTE` whose SQL cannot be resolved to a single constant, or which contains `GRANT`, `REVOKE` or `ALTER DEFAULT PRIVILEGES` text, fails closed with a `manual review required` finding, as do inline body ACL statements. Unsafe default EXECUTE grants to `anon` or `PUBLIC` also fail, even without a current definer target. Exceptions for these review rules and unsupported ACL syntax must include a nonempty `reason` and match the migration path, rule, complete statement digest and full migration-file `sourceDigest`. Newly reviewed historical exceptions also record the full file digest, so deleting a protective statement invalidates the exception even if that statement was too ambiguous for static ACL matching. The guard never executes SQL or assumes arbitrary dynamic SQL is safe. Split strings, variable SQL and unresolved `format()` calls require review even when no privilege text is visible. Reviewed block exceptions in `scripts/ci/security-definer-reviewed-blocks.json` bind the exact migration path and SHA-256 of the raw block, with an explicit reason; changing its content or moving it invalidates the exception. Unreviewed historical blocks remain findings. Database permission review remains required.

Regression coverage lives in `scripts/ci/security-definer-guard.test.js` and `scripts/ci/security-definer-acl.test.js`, plus the two-file acceptance cases in `scripts/ci/security-definer-task.test.js`. Use schema-qualified references and prefer:

```sql theme={null}
security definer
set search_path = pg_catalog, pg_temp
-- ...
if auth.uid() is null or auth.uid() is distinct from target_user_id then
  raise exception 'Unauthorized' using errcode = '42501';
end if;
-- After the function:
revoke all on function public.example(uuid) from public, anon;
grant execute on function public.example(uuid) to authenticated, service_role;
```

Use a complete `CREATE OR REPLACE FUNCTION` definition when changing a function to definer mode; an opaque `ALTER FUNCTION ... SECURITY DEFINER` fails because its body cannot be inspected.

The guard rejects NULL-unsafe `auth.uid() <> value` and `!=` guards, including reversed operands. A NULL check elsewhere in the function cannot protect a comparison. Complex conditions should use `IS DISTINCT FROM`; the lint conservatively accepts a simple whole-condition `IS NULL OR` guard.

Historical exceptions in `scripts/ci/security-definer-baseline.json` are generated records keyed by migration file, qualified signature, definition/ACL digest and existing rule. They record debt, not security approval. Changing a definition or ACL invalidates its exception. Copying or moving it to a new file requires fresh checks. Never regenerate the baseline to make a new failure pass. Remove obsolete exceptions through review.

Opaque historical blocks are frozen in `scripts/ci/security-definer-historical-blocks.json` as exact migration paths and raw block SHA-256 digests. Each entry is labeled `historical debt, not safe`. This reviewed inventory starts with 83 entries and may only shrink. The trusted-base guard rejects additions and digest changes before scanning; deleting an entry removes its suppression. New findings remain review blockers and must not be added to this inventory.

Mixed hardening exemptions in `security-definer-reviewed-blocks.json` bind the exact path, raw block digest, exact sorted grant recipients and review reason. The two approved blocks in `20261029030410` grant only `service_role` or `authenticated, service_role`; changing the block or recipients invalidates acceptance. Trusted-base CI rejects edits to review records. `security-definer-blocks.test.js` covers source changes, recipient changes, inventory growth, digest changes and deletion.

The ten findings independently ruled false positives (the finance migrations, the topic notice, tutoring notification, merge batch and merge helper pin migrations, and the revoke-only default-privilege block in `20261029030500`) are separate from historical debt, in `security-definer-verified-findings.json`. The four SR-843 invoice-total definers in `20261029040800` are not duplicated there: the definer guard defers to the SQL grant guard's source-bound exception in `scripts/ci/sql-grant-reviewed-exceptions.js`, so each finding has exactly one mechanism and a changed file voids both. Each review pins path, line, rule, complete file SHA-256, finding digest, effective role set and reason. Source or review changes invalidate it. The quoted `SET search_path TO 'pg_catalog', 'pg_temp'` form is recognized directly, including the valid empty path; a function body cannot supply an option. Regression coverage lives in `security-definer-verified-findings.test.js`. Effective-privilege parity on a fresh database remains a follow-up; static acceptance does not establish hosted catalog parity.

PR and push scans load the guard, parser and baseline from the trusted base commit. The initial independently reviewed introduction uses checkout files only when the base does not yet contain the guard. Migrations are read as data, never executed. Regression fixtures copy the historical session migration and the unmodified `20261027021000` fix from #6993.

### Admitting new verified records after bootstrap

Base records in `security-definer-verified-findings.json` are append-only: every record in the trusted base must still appear byte-identical (same keys, order and values), so the guard rejects an edited or removed record before scanning. Reviewed blocks and the historical inventory keep their existing rules. A new verified record is admitted only when the trusted base guard (`security-definer-admission.js`, loaded from the base commit) confirms all of the following:

1. **Source binding.** The record's `file` is a migration the change ADDED (git status `A` against the base, so modified, renamed and pre-existing paths are rejected). The guard recomputes the findings over that file with the base records only; the record's path, line, rule, signature, finding digest and full-file `sourceDigest` must match a finding that actually occurs. A record with no finding is rejected.
2. **Attestation.** The newest `security/definer-record-admission` commit status on the exact head SHA (`HEAD_SHA`, which must be the checked-out commit or the PR side of the merge checkout) has state `success`, was created by the pinned `tuturuuu-release-bot` login and numeric user id hard-coded in the base guard, and has the description `definer-records sha256:<digest>`. The digest is SHA-256 over the canonical JSON (recursively sorted keys, records sorted by their canonical form) of exactly the new records, so an extra, missing or edited addition fails.

A missing token, missing or foreign status, wrong SHA, wrong digest or any status API error fails the job. There is no label or approval path. Only matched findings are suppressed; SQL scanning, baseline, historical, reviewed-block and grant rules stay fully enforced in the same run. The job keeps `contents: read` plus `statuses: read`, uses the existing `pull_request` and `push` triggers only, and executes base code alone, reading PR SQL and ledgers as data. After the attestation is posted, rerun the **Definer execution policy** job; see [how to attest](https://docs.tuturuuu.com/build/devops/security-cadence.md#attest-new-definer-verified-records). A main push that merges new records needs the same attestation on the merge commit, because that run evaluates the pushed SHA.

Coverage lives in `security-definer-admission.test.js` (append-only base records and source binding) and `security-definer-attestation.test.js` (attestation, digest, API failures and the guard CLI).

The admission module was introduced by a PR that the previous base guard evaluated, so it could not admit records for itself and added none. Its workflow step copies `security-definer-admission.js` from the base only once the base contains it; base guards from before then neither have nor require it. This bootstrap is documentation only: no code path skips a check.

The SQL grant end-state guard (`sql-grant-end-state-guard.js`) and the definer guard import the frozen six-entry inherited-ACL policy from `scripts/ci/sql-grant-frozen-definers.js`. An inherited exception requires the earlier revokes to survive ACL replay; the list and cutoff never authorize a new definer or a later grant.

## Daily reports

Each job uploads a separate location-only JSON report for seven days and fails on new findings:

* **Secret diff:** checksum-pinned Gitleaks scans the last 36 hours with `--redact`. Inline suppressions and checkout ignore/config files are disabled. Only the public demo JWTs at `apps/web/e2e/helpers/environment.ts:10` and `scripts/e2e-local-environment.js:13` are accepted. Matching requires the JWT rule, exact path and line range, and the exact line digest from the finding commit. Another value at either location fails. Uploaded reports contain no secret values or raw match text.
* **Definers:** the same guard and historical exception list used for PRs.
* **Route permissions:** active Next app API and legacy route modules must call `getPermissions`, `withSessionAuth`, `withWorkspaceAuth` or `requireWorkspacePermission`, or match a reviewed historical inventory. Inventory exceptions bind path and file digest, so changed unguarded routes reappear. Paused Rust and TanStack runtimes are excluded.

Route inventory is a heuristic: a helper call does not prove every export or branch is authorized. Delegated, re-exported, public, webhook and service-authenticated routes may need reviewed exceptions after callers and delegated implementations are inspected. Comments, strings and bare imports do not satisfy the call-site check. The definer lint is a static safeguard, not a PostgreSQL parser or proof of runtime privileges.

Security Desk reads the latest `security-scan` run after 03:30 GMT+7, confirms or refutes new entries from the redacted reports, and records custody in its private register. Do not publish sensitive scan contents in PR text.

The staging catalog job remains gated on provisioning a read-only staging role and `SEC_CATALOG_RO_DSN`. Until then the workflow performs no database query. Catalog acceptance must report only names and counts for anon-executable public/private definers and public tables without RLS.

## Reproduce without a build

```sh theme={null}
ttr resources run -- node --test scripts/ci/security-definer-guard.test.js scripts/ci/security-route-permissions.test.js scripts/ci/security-daily-secrets.test.js scripts/ci/security-daily-workflow.test.js
ttr resources run -- node scripts/ci/security-definer-guard.js /tmp/definers.json
ttr resources run -- node scripts/ci/security-route-permissions.js /tmp/routes.json
```

No local build or database is required. Exact-commit CI must still qualify hosted scheduling, Gitleaks execution and artifact uploads. CI and security changes require independent review before merge.

## PR credential boundaries

Unit-test proxy keys and Codecov tokens are empty on same-repository PRs, fork
PRs and PR-target events. Test sends an empty token through its reusable-workflow
secret map; the Codecov callee independently checks the originating event.
Admitted PRs still generate coverage, retain artifacts and run tokenless uploads.

Credentialed paths retain push behavior for the existing main, production and
generated Release Please branch families. Feature pushes, unknown reusable
contexts and workflow-run contexts receive empty values. Deployment and
package-release workflows retain their existing credentials and routing.

| Origin/caller | Proxy keys | Codecov caller map | Codecov callee and Discord upload |
| - | - | - | - |
| Same-repository or fork PR, including main/production ref fixtures | Empty | Empty | Empty |
| PR target, unknown workflow-call or workflow-run context | Empty | Empty | Empty |
| Feature-branch push | Empty | Empty | Empty |
| Existing main, production or generated release push | Existing credential | Existing credential | Existing credential |

Run `node --test scripts/ci/pr-credential-boundaries.test.js` through the resource
queue. It evaluates actual expressions with synthetic values, discovers local
Codecov callers and verifies both forwarding and callee boundaries. The former
unconditional expression remains a positive control. Assertions retain hosted
PR test admission, coverage generation and artifact publication.
[GitHub documents explicit reusable secret forwarding](https://docs.github.com/en/actions/how-tos/reuse-automations/reuse-workflows);
the fixture uses the originating caller event. No live credential is accessed.

Safe rollback touches `.github/workflows/turbo-unit-tests.yaml`, `codecov.yaml`
and `discord-python-ci.yml`: use empty values or synthetic placeholders for the
affected credentials while retaining PR tests and artifacts. Do not restore
unconditional PR secret expressions by reverting this protection.
Hosted routing is preserved. Ephemeral lifecycle, runner-group restrictions and
physical host isolation remain unproved; no self-hosted PR job is admitted.

## Disclosed historical audit debt

The legacy `20230428053924_add_supa_audit_v0.3.1.sql` import grants broad routine execution and default execution in `audit` to anonymous and authenticated database roles. Its five affected definers were already historical baseline debt. The newer public-schema hardening does not remove audit-schema grants. The additional default-ACL baseline entry records this unchanged exposure as `7034-LEGACY-AUDIT-ACL`; it does not classify it as safe, remediate it or accept release risk. The repository's Data API configuration does not expose `audit`; hosted schema exposure and effective database ACLs still require verification by the security owner. Keep that debt visible during security review.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.