Security

Reporting a vulnerability

Please report vulnerabilities privately through GitHub private vulnerability reporting, not as public issues. Include steps to reproduce and the affected version or commit. You will get an acknowledgement within a few working days, and a fix and advisory are coordinated with you.

Supported versions: the latest release and the main branch.

Security model and review

This document describes how pgkiln authenticates users, authorizes access and protects data. It also records the findings of the security review of the first version (MVP). Every finding below has a regression test in test/security.test.ts.

Layers of defence

Layer	Mechanism
Database role	The runtime connects as pgkiln_runtime (NOINHERIT). It can read metadata and manage sessions, but cannot read meta.developer, password hashes or meta.instance_setting. It can reach an application's data only through SET LOCAL ROLE <app db_role>.
Application role ("parsing schema")	Every request runs in a transaction as the app's db_role. Postgres grants decide what the app can touch at all.
Row level security	meta.app_user() and meta.has_role() expose the signed-in application user to SQL, so RLS policies (and triggers, audit logs) know who is acting, not just which database role.
Authentication	Local accounts: bcrypt (pgcrypto) behind the meta.authenticate() SECURITY DEFINER function, constant work for unknown users, throttling per user and per IP. Single sign-on: OpenID Connect with PKCE, browser-bound one-time state, nonce, JWKS signature, issuer/audience/expiry checks and linking by subject (an existing account of the same name only when the provider allows it, and with the e-mail claim only when verified); SAML 2.0 with signed assertions only, issuer, audience, recipient, validity and one-time InResponseTo, the same browser binding (a same-site re-post, since the IdP's cross-site POST carries no Lax cookie). LDAP: search, then bind as the user (empty passwords refused, RFC 4515 filter escaping), write-only service password. "Keep me signed in": a rotating one-time token (SHA-256 stored, owner-only), fixed expiry, access re-checked on each use, ended by sign-out, new password, deactivation or removed access. Session rotation on every sign-in.
Authorization schemes	On pages, regions, items, buttons, processes, dynamic actions and navigation entries. They are role based or SQL based and fail closed when unknown.
REST API (PostgREST)	Tokens are HS256 JWTs (shared API_JWT_SECRET) or identity-provider tokens. PostgREST switches to the app's API role (never one that bypasses RLS). meta.api_check() runs before every request and rejects tokens of inactive accounts or accounts without access; roles are read live. Only the api schema is exposed; the anonymous role has no privileges.
Passwords	Password policy (length, letters and digits, no username), expiry and change on first use enforced before a session exists; changing a password needs the current one and ends other sessions.
Approvals	Task tables are closed to application roles; meta.tasks and the meta.*_task functions check, for every call, who may see, claim, decide, delegate or cancel (one rights function for both). Initiators can't decide their own requests unless the definition allows it. The completion SQL runs as the application's role in the same transaction, so RLS applies and an error undoes the decision.
Workflows	Steps run as the application's role (grants and RLS apply), variables are binds (escaped literals), the instance tables are closed to application roles; only initiators and administrators see a workflow, terminate it, and only administrators retry.
REST modules	Bearer tokens only (HS256 with API_JWT_SECRET, the app claim must match; no cookies, so no CSRF), the account (active, access) or OAuth client (not revoked) checked on every request, handler roles via meta.has_role(), SQL as the application's role (RLS) with every value a bind; :APP_USER, :APP_ID and the other built-in binds come from the server, never from the request.
Progressive Web Apps	Off by default, per app. Kept pages (opt-in) are removed at every sign-in and sign-out; forms kept offline are sent only for the user who filled them in, with a fresh CSRF token, and processed at most once (submission ids); the record a form was opened for is signed with the form. Permissions-Policy allows camera and geolocation for the application itself only (camera=(self), microphone=(), geolocation=(self)).
Outgoing web requests (REST data sources)	Off until PGKILN_REST_ALLOWED_HOSTS lists hosts. Only http/https without user:password; the resolved address is checked at connect time and is the one connected to (no DNS rebinding); private, loopback, link-local (cloud metadata), CGNAT, multicast and documentation ranges are refused, also as IPv4-mapped, NAT64 and 6to4 addresses, unless the host is in PGKILN_REST_PRIVATE_HOSTS; at most 3 redirects, each re-checked, credential headers dropped when a redirect leaves the origin; time and size limits (also after decompression). Parameter values are URL-encoded after a fixed host (./.. refused), header values are one line, and Authorization, Cookie, Host and transport headers can't be set on a source. Response values reach SQL as one escaped literal through jsonb_to_recordset with checked column names, as the app's role.
Web credential secrets	Encrypted with AES-256-GCM using PGKILN_SECRET_KEY, which lives outside the database (a dump alone reveals nothing); write-only in the builder, never exported, logged or shown in errors; the runtime role's column grant leaves out secret_enc. Changing the key means entering the secrets again.
Page logic	Computations, branch conditions and badge SQL run as the app's role in savepoints with binds as literals; a PL/pgSQL function body becomes a pg_temp function behind a random dollar-quote tag (a body containing it is refused). URL branches are checked in the database (no scheme, //, \, .. or control characters) and item values in them are URL-encoded, so a branch can't leave the app. A menu entry's request counts as a button only when the menu button is visible and the entry's authorization passes; a real button of that name keeps its own checks. Components of excluded build options are not rendered, run or accepted (an excluded page is a 404).
Calendar drag and drop	The move route checks the CSRF token first, then a signed-in user, page and region authorization and conditions and the region's who may drag scheme; the event must be in the region's query for this user (app role, RLS). The browser sends only an event key (≤ 200 characters) and a date-checked slot; new start and end reach the developer's move SQL as literals. Refusals and moves are logged.
Facets and smart filters	Search terms, facet values and range bounds are query parameters, never SQL text; bounds are checked against the column type, only a facet's own ranges are accepted unless it allows a custom range, column names are checked against the report and quoted, NUL bytes are dropped, and the number and size of filters is limited. Facets of regions the user can't see don't filter, and such regions get no display-selector tab.
Lazy regions and the region cache	The lazy region endpoint needs the app's session, re-checks page access and the region's condition and authorization, serves only lazy regions of that page (403 otherwise), never writes session state and answers no-store. Cached regions are keyed by a SHA-256 of the app, page, region definition, language, roles, visible regions, the query string and the values of every bind and substitution the region uses; the scope adds the session or user, and "all" is shared only between users with the same roles and sign-in state. CSRF tokens are filled in per request, regions with per-user checksum links aren't shared, and regions with errors aren't cached. Page numbers, sizes, row limits and cache durations are clamped on the server.
Popup LOV search	POST …/lov/:item/search needs the CSRF token and the app's session, re-checks page access and that the item is a popup LOV on that page the user may see and edit; the term is a bind parameter (wildcards escaped, at most 200 characters), page size 1–100, run as the app's role; a posted value must be one the list returns.
Header authentication	Trusted only when the socket's own peer address is in PGKILN_AUTH_HEADER_PROXIES (X-Forwarded-For and TRUST_PROXY play no part); untrusted peers get 403 and a login_failed entry. Values are 1–100 printable ASCII characters without spaces, colons or commas, a repeated header is refused, a changed header ends the session and a missing one signs out. Deactivated accounts and users without access are refused; automatic accounts get access to that app only. Password, SSO and "keep me signed in" are off for header apps.
Database-account authentication	The password is checked only by PostgreSQL itself: one short-lived connection as that role to pgkiln's own database (5 s timeout, so pg_hba.conf, VALID UNTIL, NOLOGIN and connection limits apply); pgkiln never reads pg_authid and never stores or logs the password. Only roles listed for the app or members of its membership role; nothing listed means nobody; superusers and pgkiln's own connection roles are always refused. Sign-in throttling and the activity log as for other types.
Keyset paging	The position in r<id>_k is signed (HMAC) per region and bound as query parameters; unsigned, foreign, oversized or ill-typed positions fall back to offset paging.
Interactive grid (0.23.0)	Master row selection links carry an HMAC bound to app, page, user, region and value; unsigned or foreign selections are ignored, and the item can't be set by a submit or an unsigned URL. GET …/region/:id serves only lazy regions or details of a visible master after page access and region checks. Layout, reset and apply routes check the CSRF token, page access and that the region is a visible grid with its Actions menu on; layouts are cleaned on the server (identifiers, widths 40–1000, at most 5 frozen columns, 6,000 characters) and only reorder columns the query returns. A detail grid's master column is never editable and comes from the session; inserts without a selection are refused.
Page logic (0.23.0)	Download queries run as the app's role (logged as download); files are sent with nosniff, a sandbox CSP and no-store, names and types cleaned, at most 1,000 files and 100 MB. Function branches run as the app's role with binds as literals; a path outside the app is refused and logged forbidden. Branches to another app go only to an existing page whose build option is on, with items signed for that app, page and user; its own authentication applies. meta.enqueue_process_job queues only chains of the current app, under the current user and session roles; passwords are left out of a job's binds and request-bound process types are refused in the background; meta.process_jobs (security barrier) shows users only their own jobs. Workflow terminate and retry are checked in the database. dialog_closed accepts only same-origin messages with a numeric page.
Globalization (0.23.0)	Time zones from the browser, the sign-in form, My account and the builder are accepted only as an exact pg_timezone_names name (64 characters at most) and reach SQL as a bound set_config(…, true) (transaction-local, so pooled connections are unaffected); check constraints back this up, also for the currency (^[A-Z]{3}$). POST /a/:alias/tz needs the CSRF token and changes only that session. Mask literals and currency symbols are escaped; invalid or over-long masks fall back to the plain value; number input is capped (200 characters, 4-digit exponents, no NaN/Infinity), and number items refuse text that isn't a number.
SQL and Data Workshop (0.23.0)	SQL Scripts, Quick SQL and the query builder need a builder session and the CSRF token and run on the owner connection like SQL Commands, on a separate connection closed afterwards. The query builder only uses catalog names, a fixed operator list and literals. The XML reader refuses <!DOCTYPE> and entity declarations (no XXE or entity expansion), limits nesting and element counts, and fetches nothing. Data load definitions: table names checked by a pattern and a constraint, resolved with regclass and quoted; masks become literals and values parameters; processes see only their own app's definitions and load as the app's role.
Custom authentication (0.23.0)	The check runs as the app's role (pgkiln's tables out of reach) in a temporary function with a random dollar-quote tag; the password is only a bind parameter and never logged; failures are logged by SQLSTATE only, and an error, false or null all give the same "invalid" answer and count towards throttling. Post-authentication code can refuse a sign-in. No remember-me, SSO, LDAP or password change for these apps; with nothing configured nobody can sign in.
Lists (0.23.0)	Entry URLs are limited to a path in the app or http(s) (a check constraint, and again after substitution and for query rows); external links get rel="noopener noreferrer"; links with item values carry the checksum; entries are hidden by authorization, condition, build option and access to the target page; query lists run as the app's role.
Builder locks and supporting objects (0.23.0)	Page and application locks are enforced on the server for every builder POST (423 for JSON); only the owner or an administrator unlocks, an administrator breaking a lock is logged (lock_broken), and only administrators manage developers (nobody demotes themselves). Supporting objects never run on import; running them needs a developer session and the CSRF token, runs as the app's role in one transaction (600 s per statement) and is logged (supporting_objects). Comments are escaped and length-limited; locks and comments are not exported.
Automations (0.24.0)	The action table is closed to application roles; meta.automation_begin/automation_end are security definer functions limited to the current app (meta.app_id()). meta.run_automation() runs synchronously in the caller's transaction as the caller's role, only for the current app, with the automation's roles and the user automation:<name> while it runs (treat those roles like a security definer function's); row values become escaped literals; per-row errors are kept in the run log.
Workflow invoke API (0.24.0)	Only the app's own REST data sources and web credentials; the host is fixed by the developer (no substitutions in it, values URL-encoded after it) and goes through the same allow-list and SSRF checks as the invoke-API process; step fields are checked again before each call; no transaction stays open during the call (a lease guards the result); a response can't overwrite DETAIL_PK, WORKFLOW_ID or INITIATOR; secrets never reach variables, events or errors.
Unload Data (0.24.0)	Developer session and CSRF like SQL Commands; its own owner connection in a read-only transaction with UNLOAD_STATEMENT_TIMEOUT, closed afterwards; exactly one SELECT/WITH/VALUES/TABLE statement (a guard against mistakes, not a boundary: developers can already run any SQL); rows capped by DOWNLOAD_MAX_ROWS; logged as sql_unload.
Create page wizards (0.24.0)	Option column names must be columns of the table and reach SQL only as quoted identifiers; table names are schema-qualified; types, functions and icons come from fixed lists; meta, pg_* and information_schema are refused. The functions run as the caller and are revoked from PUBLIC; builder routes need a developer session, CSRF and respect the app lock. Generated calendar drag and drop is off by default and runs as the app's role (RLS); set move_authz to limit who may move events.
Create application from a file (0.24.0)	Developer session and CSRF on both posts (also the upload); the temporary file belongs to the uploading builder session and is deleted after use; table and column names are lower-case identifiers, quoted, always in the new app's schema; types from a fixed list; values are escaped when shown. The new role gets privileges on its own schema and table only. meta, information_schema and pg_* are refused as a parsing schema (also for blank apps).
Charts (0.25.0)	Gantt, pyramid and polar queries run as the app's role (RLS); labels and values are escaped in the SVG and its data table; the chart kind comes from a fixed list; Gantt dependency ids are only used as lookup keys; drill-down links carry the checksum.
Map layers (0.25.0)	Each layer's query runs as the app's role (RLS); links only go to pages the user may open; names and texts travel as escaped JSON or HTML and popups are built with DOM methods. r<id>_near and r<id>_bb are parsed into range-checked numbers before they reach SQL and only narrow the rows a user can already see. With PostGIS, the app role needs USAGE on the PostGIS schema.
REST write-back and synchronisation (0.25.0)	Operation paths are checked (no host, .., : or //), values are URL-encoded and the host stays the source's; allow-list, SSRF and credential URL checks on every call. OAuth2 passwords and refresh tokens are encrypted, write-only, out of the runtime role's reach, never exported or logged. Grids only send writable columns. Synchronisation writes as the app's role (grants, RLS); meta.request_rest_sync() acts on the current app only. There is no transaction across web-service calls: a grid save that fails halfway leaves earlier rows sent.
Debug messages (0.25.0)	Application roles and the runtime role have no rights on the debug tables (security definer functions for the runtime only). The viewer needs a developer session and CSRF and shows a request only under its own app; any developer can read any app's debug messages. Password items and URL query values are never recorded; item values only at level 9, cut at 200 characters; database error messages may quote values. The Installation page is for administrators only.
meta.web_request() and meta.parse_data() (0.25.0)	Requests from SQL go through the allow-list, SSRF, redirect, size and credential URL checks of the other outgoing calls; headers are filtered when queued and again before sending; app authentication headers are dropped on cross-origin redirects; secrets never reach the log or SQL. The log is closed to application and runtime roles; the security definer functions act on meta.app_id() only and the server only takes requests queued in the caller's own transaction. A response belongs to the app, not to a user: any of the app's code with the id can read it. parse_data runs as the caller, at most 1,000,000 rows; XML is refused.
Theme Roller (0.25.0)	Only checked #rrggbb colours and fixed constants become CSS, checked again on every render (a hand-edited bad style is skipped); style names are escaped; lookups use own keys only. Switching a style needs CSRF and a safe return URL and only accepts the app's own styles; the runtime role can only touch meta.account_style. The Theme Roller needs a developer session and CSRF and respects app locks. Template options are shape-checked in the database and only classes from the fixed lists are rendered; no inline styles. (0.31) A style's condition runs as the application's role with bind variables (an error counts as false); a colour from an item (&ITEM.) is written only when its value is #rrggbb.
Working copies (0.26.0)	Developer session and CSRF on every change; copy names are checked and all output is escaped (differences too). A merge is refused while another developer has locked the target application or a page it changes, and when either side changed after the comparison was shown (a fingerprint of the comparison). Merges go through the same in-place import as pgkiln import --replace, in one transaction. A copy runs against the main application's schema, role and data, with the same users (by design) and without automations or synchronisations; meta.working_copy is closed to the runtime role. Any developer can merge any copy (no per-application developer rights, as elsewhere).
Application types and subscriptions (0.26.0)	Developer session and CSRF on every change; only components that a theme or library application offers can be subscribed to (the kind comes from a fixed list, so no table name reaches SQL from the form); the subscriber's application lock is respected and publishing skips subscribers locked by another developer; the redirect back is only to the subscriber's own Shared Components page. Starting from a boilerplate copies its definition only (no access, secrets or data) and keeps the new application's role and authentication. meta.subscription is closed to the runtime role.
Data Reporter (0.27.0)	Reports run as the application's role (grants and RLS apply, so a shared report shows each user their own rows). Every definition, from the URL or stored, is checked again before it runs or is saved: only offered columns the role can read, whitelisted operators and functions, sum and average on numbers only, bounded counts and lengths; identifiers are only the developer's and quoted, values are escaped literals. Sources in meta, pg_* or information_schema are refused in the builder and ignored at runtime. Saving and deleting need the CSRF token, a signed-in user, page access and a visible region; security definer functions only change the user's own reports; the runtime role has no direct rights on meta.data_report.
Sample data (0.27.0)	Developer session and CSRF; runs on the owner connection like SQL Commands (triggers run, RLS does not apply), in one transaction with SAMPLE_DATA_STATEMENT_TIMEOUT and at most SAMPLE_DATA_MAX_ROWS rows and 50 tables per run; meta, information_schema and pg_* refused (also by a check constraint on saved definitions). Table and column names are looked up in the catalog and quoted, values are bound (the SQL download escapes them); output is escaped; each insert is logged (sample_data); generated e-mail addresses use reserved example domains only. Saved definitions are closed to the runtime role.
Create application from several sheets, pasted data or existing tables (0.27.0)	Developer session and CSRF; pasted data is a temporary file owned by the builder session; names are validated and quoted, keys are chosen column indexes, foreign keys are recomputed on the server and only proposed ones applied; meta, information_schema and pg_* are refused as parsing and source schema, and posted table names are only matched against a fresh catalog lookup; one transaction. An application made from existing tables gets the blank application's grants on every table of that schema.
AI services (0.28.0)	Administrators only configure services (anonymous → sign-in, other developers 403, CSRF); API keys are encrypted with PGKILN_SECRET_KEY, write-only, never exported, logged or shown; all AI tables are revoked from public and the runtime role. Model, base URL and limits are checked; models are never changed silently. Item values go into prompts as escaped <data> text marked as data, never instructions; session ids and password items are never sent. Answers only become item values and are escaped when shown. Usage is logged without prompt or answer text (except at debug level 9). Data in prompts leaves the instance for the chosen provider. AI calls don't use PGKILN_REST_ALLOWED_HOSTS: only administrators set the base URL.
AI assistant, NL2IR (0.28.0)	CSRF and region visibility are checked before any AI call; assistants are for signed-in users unless marked public. Conversations belong to one session and region, are deleted with the session and are closed to the runtime and application roles. Context queries and tools run as the app's role (grants, RLS): arguments are bound literals checked against the declared parameters (unknown arguments refused), each tool is one subquery with a row limit and a 15 s timeout and is rolled back unless the developer marks it as writing; tools can be limited by an authorization scheme; REST tool arguments are never &ITEM.-substituted. Natural-language report filters only produce filters a user could set by hand, on visible columns. Answers are escaped (paragraphs, lists, bold, code only). A writing tool lets the model change data on the user's behalf within the role's rights: design those with care.
App Builder AI and blueprints (0.28.0)	Developer session and CSRF; only administrators choose the builder's AI service. Generated SQL is shown, never run automatically; page proposals are checked (page types, tables the app role can read, free page numbers) and created only after the developer confirms. The model sees names, types and keys, never rows. A blueprint is validated (identifiers, references, rows) and turned into quoted SQL; Create only accepts the exact text the review showed, signed with the developer's session, in one transaction. meta.builder_ai, meta.ai_table_note and meta.blueprint are closed to the runtime role.
Workspaces (0.29.0)	Every builder request under /apps/:id or /pages/:pid (GET and POST), a working copy's main application, a boilerplate and the code editor's application are checked against the developer's workspaces in developer() and answer 404 outside them; lists are filtered by the current workspace. Switching needs CSRF and membership; managing workspaces needs an administrator and CSRF; names are escaped; subscriptions only reach theme and library applications of the same workspace. Workspace tables are closed to the runtime role. Not a tenant boundary: the SQL Workshop runs as the owner and application code on the runtime connection, so developers who write SQL can reach other workspaces; accounts, identity providers, LDAP directories and AI services are installation-wide.
Drawers, built-in template components, Theme Roller additions (0.29.0)	Dialog positions and sizes, base styles, dark-mode colours, item and report-column template options only reach the page from fixed lists (checked when saved and again when rendered: a hand-edited value is ignored); the browser gets dialog shapes as JSON values from those lists. Built-in template components pass the same template checks as plug-ins; Copy into this application takes a static id from the fixed list, needs CSRF and the application in the developer's workspace, and never overwrites the application's own component.
Report row selection across pages (0.29.0)	POST …/report/<id>/select needs the CSRF token, page access and a visible report region whose selection names the item (and the item visible); it only changes that item's session state; values are bounded (5000, 400 characters, no :) and treated as user input, like a submit; output is escaped.
Instance settings (0.29.0)	Administrators only, with CSRF; only whole numbers within each setting's range are stored (a bad stored value falls back to the environment variable or default); the configuration overview never shows secrets (connection strings, keys: only whether they are set); changes are logged.
Session sharing (0.30.0)	Only for the user directory; the group cookie is HttpOnly, SameSite=Lax, Secure behind HTTPS, path /, a random token whose SHA-256 alone is stored (closed to the runtime role); a token only opens applications of its own group, after each application's own access check (roles per application, groups re-mapped); idle and maximum times, deactivated accounts and expired passwords end or refuse it; signing out of one application ends the shared sign-in and the sessions it started.
Static application files, Execute JavaScript (0.31.0)	Only developers (session, CSRF, workspace guard, app locks) change files; the runtime role can only read them. Names are checked in the database and the server (letters, digits, _, -, ., a fixed list of extensions, never HTML or paths) and served only for the application named in the URL, with their own type and nosniff; an SVG or PDF gets default-src 'none' (SVG sandboxed). Files are public, like APEX's: no secrets in them. Pages load them with <script src>/<link> from the same origin, so the CSP stays script-src 'self'. Execute JavaScript sends the page only a checked function name, never code; code typed into the action is ignored.
Plug-ins (0.31.0)	Only developers (session, CSRF, workspace guard, app locks) import, remove and install plug-ins; meta.import_plugin is not executable by the runtime role, which can only read meta.plugin. Plug-in files are checked before anything is stored: type, names, static file names and types, base64 contents, attributes, a region's template against the template allow-list, a process function as a schema.function identifier (checked again by the database). Install SQL never runs on import: a developer reads and runs it as the application's role, in one transaction. Pages get only a plug-in's name and attribute values (escaped JSON in data-plugin-attrs, or in the page's JSON), and only for a plug-in of the matching type; its JavaScript comes from same-origin static files. A plug-in's code runs with the application's rights: install only what you trust.
Zips and parsing in SQL (0.31.0)	meta.unpacked_file/meta.unpacked_entry are closed to applications; the functions that read them are security definer and only return entries of content the caller already holds (looked up by its SHA-256). The server unpacks only zip/xlsx it receives, at most 2,000 files and 200 MB (declared sizes checked before inflating, actual sizes after), kept 24 hours. Zip entry names are relative paths (no .., no leading /). XML with a DTD or entity declarations is refused; a row selector is an element name or path, never XPath.
Object storage (0.31.0)	Bucket requests go through the web client: allow-list, private-address checks at connect time, no secrets on cross-origin redirects; the credential (type aws_sigv4, secret encrypted) must be valid for the bucket URL and is used only for object storage. Keys are generated by the server (<prefix><uuid>/<cleaned name>); prefixes can't contain ... Downloads keep the checksum, page access and RLS checks (the row is read as the app's role before the object is fetched) and the sandboxing headers; users never see the bucket or credential.
Session state protection	Item values in URLs, and the row keys of interactive grids, carry an HMAC checksum bound to app, page and user. Hidden, display, read-only and unauthorized items cannot be set by a form post.
Request integrity	A CSRF token on every POST (pages, AJAX, login, logout). Buttons are re-validated on submit.
Output	Auto-escaping HTML templates, strict CSP (no inline scripts, no inline styles: the page's one <style> carries a per-response nonce), X-Frame-Options, nosniff, no-store. Images may also come from the map tile server (MAP_TILE_URL, an http(s) origin only); map data travels as JSON in a <script type="application/json"> element with < escaped. A map area in a report URL (r<id>_bb) is parsed into four range-checked numbers before it reaches SQL; anything else is ignored. The browser fetches tiles from that server directly, so it sees users' IP addresses, map areas and the site's origin (sent as Referer, which OpenStreetMap requires; never the page's path or query): self-host tiles where that matters.
Files	Uploads are size- and type-checked and stored per session (meta.temp_files shows only the session's own files). A posted text value can't point a file item at another file, and a "remove file" value only removes files of the form's own record. Downloads need a checksum bound to user, page, item and record, check page access and read as the app's role (RLS applies). They are sent as attachments with nosniff and a sandboxing CSP; only PNG, JPEG, GIF, WebP and PDF are shown inline.
Data loading and PDFs	The data_load process loads as the app's role (grants, RLS, triggers apply) and shows only data errors and RAISE messages per row. Report PDFs run the report's own query with the same page, region and row checks as the screen.

Findings of the MVP review and their fixes

Severity is rated for an internet-facing deployment.

#	Finding (MVP)	Severity	Fix
1	No authorization layer: every signed-in user could open every page and press every button.	Critical	Authorization schemes on all component types. Links, menu entries and buttons to pages the user cannot open are hidden.
2	Forged button requests: the submit handler only checked that a button with that name existed. Its condition was never re-checked, so a hidden DELETE could be "pressed".	High	Visibility (authorization + conditions) is computed before posted values are applied. Requests for buttons that were not rendered get a 403 and are logged.
3	IDOR through URL items: ?P3_EMPNO=<any id> opened any record.	High	Pages default to protection = 'checksum'. URL item values need an HMAC checksum (urlChecksum / meta.page_url()) bound to app, page and user.
4	Runtime connected as a superuser; apps without db_role ran as superuser.	High	Separate least-privilege pgkiln_runtime login. The builder creates a dedicated role per app. Builder and migrations use the owner connection.
5	No brute-force protection.	High	Throttling in activity_log: 5 failures per user (since their last successful login) or 50 per IP within 15 minutes. The same applies to builder logins.
6	Username enumeration by timing: bcrypt only ran for existing users (~10× slower responses).	Medium	meta.authenticate() runs bcrypt against a random salt when the user is unknown. Error messages are identical.
7	Session tokens stored in plain text, with only an 8-hour idle timeout.	Medium	The cookie holds a 256-bit random token and the database stores only its SHA-256. Idle timeout is 60 minutes and the absolute limit is 8 hours (APEX defaults). Expired rows are purged. Password changes and deactivation end sessions.
8	Logout via GET (CSRF-able).	Low	POST with CSRF token only.
9	No security headers, and inline onchange handlers.	Medium	CSP script-src 'self', frame-ancestors 'self', form-action 'self', base-uri 'none', nosniff, SAMEORIGIN, Cache-Control: no-store, HSTS when COOKIE_SECURE=true. All behaviour lives in /static/app.js via event delegation.
10	Database error details shown to end users (table names, SQL fragments).	Medium	Only intentional messages (RAISE EXCEPTION, SQLSTATE P0001) and friendly constraint messages are shown. Everything else becomes "reference #id" in the activity log. The per-app debug flag shows details during development.
11	Password hashes readable by the runtime connection.	Medium	Column-level grants. Verification happens only inside meta.authenticate().
12	Default admin/admin builder account with no way to change it in the UI.	Medium	A Developers page (change password, manage accounts). A banner appears while a default or trivial password is in use. Minimum password length is 8 for new accounts.
13	CSV export formula injection (new feature).	Low	Text cells starting with = + - @ get a leading '.
14	Read-only conditions failed open (found during this review): an erroring condition made an item editable.	Medium	Read-only conditions fail closed. Region and button conditions already did.

Findings of the review of 2026-10-08 and their fixes

A second full review, mainly of what end users and API callers can reach. Regression tests are in test/security.test.ts ("security review 2026-10-08"), test/sso.test.ts, test/grid.test.ts, test/custom-auth.test.ts and test/binds.test.ts.

#	Finding	Severity	Fix
1	Hidden report columns readable through the URL: computed columns, filters, highlights, the search, sorting, control breaks, aggregates and the group by, pivot and chart views took any column of the query, including those in hidden.	Medium–High	Only the shown columns can be used; the search looks only at them.
2	REST modules: :APP_USER set by the caller (?app_user=KING); query, body and path values filled every bind.	Medium	APP_USER, APP_ID, APP_ALIAS (and APP_SESSION, APP_PAGE_ID, REQUEST, APP_LANGUAGE) come from the server; such parameters are dropped, and refused as path parameters. AI assistant tool arguments can't replace them either.
3	Single sign-on linked an existing account by the username claim on first use, and a missing email_verified counted as verified: where users choose that claim at the provider, they could take over an account.	Medium–High	Per-provider Link existing accounts (link_existing, off for new providers; migration 075 keeps it on for existing ones), only verified e-mail addresses link; SAML usernames are checked like OIDC ones.
4	List values not checked: select lists, radio groups, checkbox groups, shuttles and grid select columns accepted any posted value.	Medium	Posted values must be returned by the item's list of values (config.any_value: true opts out); comboboxes stay free text.
5	URL checksum ambiguity: names and values were joined as k=v&k=v, so a value containing &OTHER= signed two items.	Medium	Each name and value carries its byte length, with a version prefix (TypeScript and meta.url_checksum, migration 075). Links signed before the upgrade need a new checksum.
6	My account password change not throttled: the current password could be guessed from an open session.	Low	The sign-in throttle applies; database, custom and header apps don't offer the form at all.
7	Any developer could change identity providers, LDAP directories and the password policy, and grant access to applications outside their workspace.	Medium	Administrators only; access grants are checked against the developer's workspaces.
8	meta.page_url() trusts settable settings, and application SQL can RESET ROLE to pgkiln_runtime.	Info	Documented below: the app role is not a sandbox for developer SQL.
9	Validations for DELETE never ran (a DELETE skipped validation altogether).	Low	A DELETE runs the validations made for it (when_button = 'DELETE') and skips the item checks.
10	TRUST_PROXY=true trusted every X-Forwarded-For entry, so behind an appending proxy a client could choose its IP and dodge the per-IP throttle.	Low	true trusts one proxy; a number that many; addresses or subnets name them.
11	Document templates via ?doc= on public pages: anyone could download every template without an authorization scheme.	Low	Users who aren't signed in only get templates the page offers with a visible document button.
12	Bind scanner and PostgreSQL could disagree on non-ASCII dollar-quote tags, $ inside identifiers, \r ending a comment and WHERE'…'.	Low	The scanner follows PostgreSQL's lexer for these cases.

Things that remain the developer's responsibility

• Developer SQL is trusted, as in APEX. It runs as the app role, so the role's grants define its reach. End-user input reaches SQL only as escaped literals, whitelisted operators, integers, or identifiers checked against the result's column list.
• Static region HTML is trusted. &ITEM. substitutions in it are escaped, and inline scripts, event handler attributes, style attributes and <style> blocks are blocked by the CSP (style-src 'self' 'nonce-…'): even HTML that slips through can't restyle the page to fake a form or hide a warning. Style developer HTML with the classes in /static/app.css.
• Template components and plug-ins are not trusted like developer HTML, because a plug-in file may come from someone else. A template is checked against an allow-list when it is saved, imported (builder or SQL trigger) and rendered: no scripts, styles, event handlers, data-*, forms, frames, unquoted attribute values or raw placeholders. Every placeholder is HTML-escaped, and a value that would make a javascript: (or similar) link is dropped. Plug-ins carry no code of their own.
• SQL Commands and SQL Scripts are logged. Since 0.23.0 the statement text goes into the activity log (sql_command, sql_script; up to 2,000 characters), so a password typed into a statement is stored there. Developers have full database rights in the SQL Workshop, as in APEX. A SELECT in a script is read completely even though only 10 rows are kept.
• Supporting objects and custom authentication code are developer SQL: review imported scripts before running them, and give the app's role only what an install script needs.
• The builder's code editor suggests tables and columns as the application's own database role sees them (the runtime role for apps without one), so a developer of one application can't list another application's schema through it; it never returns password hashes.
• Server-side checks belong in the database for anything that matters: RLS policies, or checks inside PL/pgSQL functions (see hr.decide_leave). UI authorization hides things; the database enforces them.
• protection = 'unrestricted' pages accept any URL item values. Use it only for items that are harmless to set, such as search filters.
• REST data sources: a cached response is shared by every user of the application, so don't cache sources whose answer depends on the user. With * on PGKILN_REST_ALLOWED_HOSTS developers can call any public host. OAuth tokens and the response cache are kept per server process.
• Calendar move SQL is developer SQL: pgkiln checks that the user sees the event, but who may move what (e.g. only the organiser) belongs in the SQL or RLS.
• Streamed downloads keep a read-only transaction open while a slow client reads; STATEMENT_TIMEOUT applies to each fetch.
• The app role is not a sandbox for developer SQL. pgkiln switches to the application's role with SET LOCAL ROLE, and SQL can always RESET ROLE back to the session's login role, pgkiln_runtime. So application SQL (processes, regions, REST handlers, custom authentication, supporting objects) can do what the runtime role can: read every application's metadata and sessions, set pgkiln.app_id / pgkiln.app_user and call meta.page_url() for any application and user (valid checksums), or change sessions. Treat every developer as trusted with all applications of the instance, as the workspace notes say; end users never write SQL. A separate login role per application would close this.
• Administrators and developers: SQL Commands, SQL Scripts and the other SQL Workshop tools run on the owner connection for every developer, so a developer can change anything, including meta.developer.is_admin. The administrator-only pages (instance settings, AI services, identity providers, LDAP directories, the password policy, developers) prevent mistakes; they are not a boundary against a developer who means harm.
• App id from SQL (known gap, to fix): meta.app_id() reads the pgkiln.app_id setting, which an application's own SQL can change. Requests queued from SQL (meta.web_request, meta.ai_generate) could therefore be made under another application's id, using its web credentials or AI services and quota. Page processes are not affected (they use the server's app id). Until this is fixed, treat applications on one instance as mutually trusted for these features.
• Lockout trade-off: per-user throttling lets an attacker lock a known account for 15 minutes. The per-IP limit and the activity monitor help you spot this.

Deployment checklist

1. Change the builder password (the Developers page), or delete admin after creating your own account.
2. alter role pgkiln_runtime password '...' and set RUNTIME_DATABASE_URL.
3. Run behind HTTPS with COOKIE_SECURE=true, and set TRUST_PROXY=true behind one reverse proxy (a number for several, or their addresses; client IPs are needed for throttling).
4. Keep debug off for production apps (see the Settings → Security checklist in the builder).
5. Give each app its own db_role with the minimum grants; add RLS where rows are per user or per team.
6. Restrict network access to /builder (reverse proxy or firewall) if developers are a small group.
7. With a REST API: set a long random API_JWT_SECRET (the same in PostgREST), change the pgkiln_authenticator password, configure db-pre-request = meta.api_check, expose only the api schema, and run PostgREST behind HTTPS.
8. With REST data sources: list only the hosts you need in PGKILN_REST_ALLOWED_HOSTS (avoid *), set a long random PGKILN_SECRET_KEY (32+ characters) kept outside the database and its backups, and use PGKILN_REST_PRIVATE_HOSTS only for internal services you mean to reach.
9. With header authentication: list only your proxy's addresses in PGKILN_AUTH_HEADER_PROXIES, make sure users can't reach pgkiln directly, and have the proxy remove any incoming copy of the header.
10. With database-account authentication: list exact roles or use a dedicated membership role, and limit what those roles may connect to in pg_hba.conf (they can reach the database with their password outside pgkiln too).