Skip to content

LQL: support table/setof-returning functions for SECURITY DEFINER row reads #63

@MelbourneDeveloper

Description

@MelbourneDeveloper

Need

NAP's `[AUTHZ-ONE-ROLE]` migration (docs/plans/kill-app-admin.md) drops the direct `app_user` SELECT grant on a handful of operator-owned tables (`cost_card_llm`, `cost_card_machine`, `wallet.markup`, `bundles` in the HMAC-only path). Reads from those tables move behind `SECURITY DEFINER` functions that re-verify the caller's intent server-side (HMAC, GUC, etc.) and return the row(s).

The existing LQL `functions:` surface in `schema.yaml` supports scalar returns (`boolean`, `uuid`). Example:

```yaml

  • name: is_member
    arguments: [...]
    returns: boolean
    securityDefiner: true
    bodyLql: |
    exists(tenant_members |> filter(fn(m) => m.user_id = u and m.tenant_id = t))
    executeRoles: [app_user, app_admin]
    ```

The pipeline syntax `

|> filter(...)` could naturally extend to row sets, but there is no example of a function returning a row (`returns: table(...)` / `returns: setof
`) and no obvious LQL operator to project a tuple.

Ask

  1. Document whether table/setof returns are supported today (and how — `returns: setof cost_card_llm` style?).
  2. If not supported, add it to the roadmap. NAP can work around by emitting JSON-encoded rows from a SECDEF returning `text` / `jsonb`, but that's a hack — proper typed support would be far cleaner.

Workarounds NAP is considering (in order of preference)

  1. `returns: jsonb` + decode in Python (works today if jsonb is supported by LQL).
  2. `returns: setof <table_name>` (needs DataProvider support).
  3. Multiple scalar SECDEF fns (one per column) — slow + many round-trips.
  4. Keep `admin_session` for these reads — defeats the whole `[AUTHZ-ONE-ROLE]` invariant.

Filed during the 2026-05-19 NAP coverage / kill-app-admin push. Cross-link: NimblesiteAgenticPlatform/docs/plans/kill-app-admin.md, NimblesiteAgenticPlatform/docs/plans/kill-app-admin-edit-list.md.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions