Define a PostgreSQL execution profile for AI reporting

Choose a fixed report instead of a generated SQL sandbox

This original profile targets PostgreSQL 18 and the currently documented node-postgres pg.Pool API. It is a proposed design, not an installed or tested stack; pin and verify the actual driver dependency before implementation.

Invent a Cedar reporting endpoint that accepts a report identifier and a bounded date value. Trusted application code maps the identifier to one fixed statement selecting report IDs and summaries from report.cedar_reports, ordered by report ID and limited to fifty rows. The model supplies no SQL text, identifiers, joins, function names, or connection options.

Node-postgres query documentation supports binding values separately and notes that parameters do not represent SQL identifiers. Choose table and column names in the trusted template. Reject a model’s extra sql field rather than passing it to a keyword filter.

Bind the login, row policy, and columns together

Authenticate the application user and authorize this report before selecting a connection profile. This small fixture uses a dedicated report_cedar login for Cedar, with no object ownership, superuser status, BYPASSRLS attribute, or memberships permitting broader authority. It does not switch a shared privileged login between tenants.

Enable RLS on the fictional base table data.reports; its SELECT policy for this role permits only rows whose tenant is Cedar. Audit every applicable policy. PostgreSQL 18 RLS documentation explains owner and privileged-role exceptions, and that permissive policies combine with OR. A restrictive-looking policy cannot compensate for an overlooked permissive one.

Grant only the needed report ID, tenant, creation-time, and summary columns, plus the required view privileges. Exclude private_notes; do not also grant table-wide SELECT through another path. Privilege documentation covers column-level SELECT and PUBLIC grants. Review effective inherited and PUBLIC privileges, including unwanted CREATE, TEMPORARY, and custom-function EXECUTE access; revoking a direct grant alone may be insufficient.

Review the view and callable code as authority boundaries

Make the reviewed projection view security_invoker=true. PostgreSQL’s CREATE VIEW documentation says this uses the invoker’s underlying relation privileges and RLS policies; the caller needs relevant permissions on both view and base relations. Default owner-based view behavior must not be silently substituted.

A function referenced by a view retains its own invoker or definer rights. This profile uses plain built-in column types and no custom functions, extension calls, or SECURITY DEFINER helpers in its template, view, or row policy. A proposed new helper requires a separate owner, privileges, body, and effect review.

PostgreSQL function security guidance recommends trusted object owners and excluding writable, untrusted schemas from search_path. Use schema-qualified application relations and a reviewed path such as pg_catalog, report, pg_temp, with no untrusted CREATE rights and no effective TEMPORARY grant. These choices do not certify arbitrary SQL or every installed function.

Keep one transaction on one checked-out client

The proposed executor checks out one client, verifies the expected session identity and profile, then starts its read-only transaction before the report. All setup, report, and completion statements use that client. Node-postgres transaction guidance explicitly requires the same client and warns against using separate pool.query calls for transaction statements.

PostgreSQL’s transaction documentation lists commands restricted by READ ONLY, including qualifications for temporary tables, and states that this is not a prohibition on all disk writes. Do not turn it into a universal no-effect promise for callable server code, extensions, or external systems.

Choose separate resource bounds. For this fictional workload, propose a two-second statement timeout, fifty-row result limit, and sixteen-KiB response ceiling. These are untested operating choices. The server setting documentation defines statement_timeout and search_path behavior. A row limit alone does not bound query work, and cancellation does not retroactively undo every possible external effect.

Specify disposal rather than assuming a pool reset

The Pool API documents that checkout may return an idle client; ordinary release returns it to the pool, while client.release(true) disconnects and destroys it. Pool return is not evidence that role, transaction, or session settings match the next request.

This deliberately small profile starts with an empty, dedicated pool and destroys each checked-out client on every exit, including setup failure, success, timeout, and rollback failure. Complete or attempt rollback as appropriate, then dispose of the connection. No reporting client is returned for reuse. If performance requirements later introduce reuse or another pooling intermediary, define and verify a new reset contract first.

Review the complete profile with inert fixtures

A future recorder could compare fabricated profile records with these expected decisions. That would test only local admission logic, not database enforcement. Separately scoped database checks would still be necessary before real use.

All checks are PROPOSED_UNRUN: no database connection, SQL, role, grant, schema, function, pool operation, model, or customer-data access occurred. Boundary is a prelaunch commercial model router with planned request-path policies, not a shipped SQL sandbox or PostgreSQL authorization service. Existing eight mock-policy tests do not validate this profile.