Row-level security
Row-level security (RLS) controls which database rows a caller can read or change. Toolora’s browser queries need the relevant object privileges and applicable policies. The database evaluates those policies using the request’s identity, selected company and the helpers named by each policy. A hidden button, cached capability or company filter in JavaScript does not enforce this boundary.
Authentication owns identity verification; company context owns selection and switching. The record model distinguishes company-owned rows from shared identity and external projections. Those differences matter when reading a policy: not every table has a company column or the same access class.
Roles, privileges and execution paths
Section titled “Roles, privileges and execution paths”Application roles such as arbetare and backoffice are records that grant
capabilities. They are not separate PostgreSQL request roles. The Data API uses the
bearer token’s database role and verified claim context; a public application key
alone is not business authority. User-editable metadata is not Toolora’s source
for company membership or capabilities. Supabase’s RLS guidance
explains the database-role boundary; Toolora’s actual predicates are listed below.
| Path or role | Effective boundary | Practical meaning |
|---|---|---|
anon database request |
Object grants and applicable policies, without an authenticated person’s identity | Some lookup/shared surfaces are readable; a schema name does not grant access to all business rows. |
authenticated request |
Grants, command/role policies and their context helpers | The same database role serves different companies and application capabilities. |
| Browser query, caller view or invoker RPC | Caller privileges and underlying row policies | Every involved table/column/function can have its own additional requirement. |
| Definer RPC | Execute grant, function owner’s privileges and explicit body checks | Being callable does not establish company/resource permission; owner execution can bypass caller RLS. |
| FastAPI service-role business query | API identity/capability/resource checks, then privileged database access | Service-role bypass requires explicit scope in the API; browser RLS does not constrain that query. |
| Database owner or maintenance | Role privileges and the relation’s owner/force settings | An owner-run test is not proof that an authenticated browser is allowed. |
Table grants and RLS are separate. Enabling RLS without an applicable policy denies
ordinary row access. Owners normally bypass it unless forced; superusers and roles
with BYPASSRLS retain their bypass. FORCE ROW LEVEL SECURITY therefore does not
make the service-role path equivalent to the browser path. The configured local
stack uses PostgreSQL 17; these are PostgreSQL’s row-security rules,
not UI conventions.
Column grants matter too. The current persons boundary revokes browser table-level
SELECT and grants selected columns back to authenticated. The encrypted identifier
column is excluded. A request for allowed columns can work while SELECT * fails.
RLS choosing a row does not grant every column on it.
Company and person context
Section titled “Company and person context”current_tenant_id() reads only the accepted JWT’s
app_metadata.active_tenant_id and validates it against the live actor projection.
An absent, malformed or no-longer-eligible selection returns NULL.
current_person_id() resolves the current live person in that company. Neither
helper falls back to top-level claims or the account’s stored preference.
current_person_has_capability(), API actor RPCs and the token hook share the
same active people/company and current role projection. Platform authority
requires the selected operator-owned platform company, a live platform grant and
no impersonation. Ordinary company type is not that flag. The
permission reference owns
actor versus resource-policy comparisons. Shared actor rules do not make
service-role routes and direct browser policies interchangeable.
The company-context concept explains token issuance, explicit switching and the release’s named same-token observations.
Claims are snapshots. Changing the stored company preference does not rewrite an already issued token. Role-record changes can affect the next SQL lookup while an old token still carries its company claim. Helper subqueries cached within a statement do not provide a cross-request, immediate-revocation guarantee. Frontend query keys and query-client replacement keep company caches separate; they do not change the token or authorize an otherwise denied row.
Local SQL tests set synthetic claims and assume the authenticated database role.
That exercises policy execution. It does not test a real token signature or let a
browser set arbitrary trusted PostgreSQL request settings.
How a policy accepts or filters a row
Section titled “How a policy accepts or filters a row”USING filters existing rows. WITH CHECK validates proposed inserted/updated
rows. A failed visibility predicate commonly yields no matching rows; a failed
new-row check aborts the statement. For UPDATE/ALL policies, omitted WITH CHECK
uses USING as its default. Omission alone does not mean the new row is unchecked.
- SELECT applies visibility predicates.
- INSERT validates the proposed row.
- UPDATE needs its old-row and new-row permissions; ordinary filtered/returning requests also need the applicable SELECT permissions/policies.
- DELETE applies old-row permission. Setting
deleted_atis an UPDATE, not DELETE.
Applicable permissive policies combine with OR; restrictive policies narrow that
result with AND and do not grant access alone. A new permissive rule therefore
cannot be read as a restriction of an older allowing rule. RETURNING adds SELECT
requirements; it is not a general promise to silently omit an unacceptable new row.
These are the PostgreSQL policy semantics.
In Toolora, an ordinary projects_update checks the old row’s active-company
identity and projects:write; its new-row check retains that company identity.
Changing the query’s project ID cannot grant another company’s write. A matching
old row changed to an impermissible company can raise a check error; an invisible
foreign target can instead affect zero rows. The caller must inspect its actual
result. A successful transport response alone does not prove one record changed.
Policies do not replace constraints or triggers. Accepted-source integrity, issued invoice immutability and source-consumption locks continue to apply after access is established. Transaction boundaries explain that separate responsibility.
Representative current policies
Section titled “Representative current policies”| Object | Selected current boundary | Important limit |
|---|---|---|
projects |
Company capability read/write paths, published own-work and administrator read branches; former direct-contact/customer-company raw-row branches are removed | Read policies combine. A publication check in the own-work branch is not a universal filter on every branch. |
person_tenant_profiles |
Company plus parties:read for the read branch; write accepts parties:update or people:update, with the corresponding administrator exception |
Contact/profile editing is not Auth-user editing or portal access. |
invoices and their lines |
Header reads accept company invoices:read/invoices:write; ordinary writes require invoices:write. Lines retain their own read/write policies |
A header permission does not imply every child operation succeeds. |
persons |
party_visible_in_active_tenant() or administrator, plus selected-column grants |
Reachability through register/contact/person/profile is different from a universal people:read or activity check. |
parties and organizations |
Authenticated party-spine reads; organization read policy is true, subject to grants. Entered-organization update checks Auth identity and source class |
These shared rows do not use the same per-company predicate as private profiles. Do not infer uniform tenant isolation from the global UUID. |
| Payment-feed/state tables | Browser grants are revoked; service role owns the projection path | A direct browser query can fail at privileges before RLS. Authorized API/MCP reads apply their own company and source checks. |
Current capability codes include the reconciliation migration’s replacements;
older comments can still name deprecated codes. Read the final migrated policy,
not just the baseline or a function’s introductory comment. For example, the
current invoice line policies use invoices:write, even where an older comment
mentions invoices:update.
The shared identity model intentionally has different access classes. Global party/organization reads, natural-person reachability and company-private profiles must be evaluated separately. This review does not introduce a new shared-identity ownership policy or claim that every global write has the profile’s company check. Customers and contacts owns the business relationships.
The worker-specific projects_read_own branch now requires projects:read_own and
a published-project helper. It does not simply test project membership. Other
permissive read branches still have their own predicates. Customer admission now uses private SQL projections and service-only JSON RPCs;
legacy contact/customer raw project reads are removed.
Customer portal access owns the shared
discovery/detail rule and safe projection; worker and subcontractor access owns
publication/action checks and the existing UE file, closure and upload-order limits.
Signed-file access and previously released evidence retain their own
publication boundaries.
Views and RPCs keep their execution identity
Section titled “Views and RPCs keep their execution identity”v_party_tenant_display is explicitly security_invoker=true. Browser reads stay
in the active company and evaluate caller access to its underlying relations. Its
joins qualify private profiles and addresses by the selected company. The service
consumer must still filter fk_tenant: a privileged caller does not acquire
browser restrictions merely by using the same view.
For a normal view, underlying permission/RLS evaluation uses its owner unless the
invoker setting changes that behaviour. Functions inside the view retain their own
execution modes. Inspect the actual definition and grants; neither a view name nor
security_barrier establishes caller-context execution.
PostgreSQL documents this view distinction.
save_invoice_draft is an invoker RPC, callable by authenticated and service_role.
The browser caller remains under invoice/line RLS. Its row lock also needs the
applicable update access, so a read-only caller cannot use EXECUTE to save the
invoice. Revision and line-ownership checks are additional integrity rules.
The service-role caller retains its broader privileges even in an invoker function.
The settlement workflow owns the full draft contract.
ensure_person_contact is a different example: it runs as its definer and is
callable by authenticated/service roles. Before its internal write, the wrapper
checks the requested company/capability and the customer’s relationship in that
company. Its private guard has explicit service/maintenance branches; the browser
branch compares request context and current capability. The private schema/helper
is not a new browser entry point.
A definer function runs with its owner’s privileges; it is not inherently a public
permission or a guarantee of correct resource scope. Execute grants, owner, fixed
search_path and body checks must be reviewed together.
PostgreSQL’s function execution rules
explain the general mechanism. Toolora’s two RPC manifests classify actual signatures
and permitted roles; their contract tests inspect the database’s grants and modes.
A public-schema function can be authenticated-only, service-only or owner-only.
create_project_invoice_from_basis(uuid, uuid, jsonb, jsonb, jsonb, jsonb) is a
service-only definer RPC. EXECUTE is revoked from public/browser roles and granted
to service_role; the project-billing service calls it after API authorization.
Its resource/amount checks and atomic invoice/basis changes protect integrity
inside that privileged path. They do not make the RPC a browser entry point or
replace the API caller’s company/resource checks.
flowchart TD B[Browser with bearer token] --> D[Data API role and request claims] D --> G[Object and execute grants] G --> C[Table, caller view or invoker RPC] C --> R[Applicable row policies] R --> I[Constraints, triggers and transaction rules] G --> F[Definer RPC: owner privileges] F --> Q[Explicit company, capability and resource checks] Q --> I B --> A[FastAPI identity, capability and resource checks] A --> S[Service-role business query: RLS bypass] S --> I
Service-role API work has its own boundary
Section titled “Service-role API work has its own boundary”The API first verifies identity and capabilities, then resolves the requested resource in the authorized company or relationship before business work. Its identity/capability reconstruction already performs privileged reads; the guard’s ordering does not mean no service-role query exists before a handler starts. The API policy owner explains that boundary. A capability for projects in company A does not authorize completing company B’s project through a privileged query.
RLS evidence for the browser cannot certify that API’s scope. Conversely, an API’s 403 does not prove an identical direct-database resource predicate. Actor eligibility is shared; resource and execution scope remain path-specific. MCP and portals are also not generic browser table access; their transport/business authorization and audience checks remain separate. Provider writes, payroll and financial attestation have their own guarded contracts and are not part of this RLS explanation.
Examples and diagnosis
Section titled “Examples and diagnosis”With a valid company-A token and projects:write, an ordinary staff caller can
update an eligible visible company-A project. A foreign-company project filtered
through the same ordinary write policy can produce zero changed rows. A same-company
caller without the write capability can also get no matching update target. In
both cases, retaining the draft/error and checking the returned row/count is more
useful than treating an empty result as a saved edit.
If that person becomes inactive, the shared actor projection excludes the membership on both API and SQL paths. Changing the stored preference to B does not change an accepted A token’s selection on either path. A JavaScript B filter likewise does not change that token’s context. An ordinary worker using only the published-own-work read branch can lose project visibility after publication withdrawal; that does not prove every staff or contact branch is hidden. These are scoped examples, not universal revocation guarantees.
| Symptom | Inspect and recover |
|---|---|
| Empty read or zero-row update | Confirm record identity, token company, applicable read/write policies and returned count. Preserve the local draft; do not broaden the query to force a match. |
| RLS/check violation on write | Inspect the proposed new row, effective WITH CHECK, trigger changes and integrity errors. A visible old row alone is insufficient. |
| Permission denied on table/column/function | Inspect grants and execution identity first. A valid JWT or RLS predicate does not supply a missing object privilege. |
| Company changed but reads disagree | Finish the documented switch/token refresh and cache transition; identify which request carried which company context. |
| Inactive person or administrator behaves differently | Check the selected token and live actor eligibility first, then the actual route and SQL resource predicates. Shared actor rules do not imply identical resource scope. |
| Published/portal data differs from a staff query | Identify the exact branch, admission grant and audience projection. Retain the documented portal/worker/UE limits. |
Schema-catalogue output helps compare policy expressions and table grants, but its policy rows do not list every attribute, including role lists and permissive mode. Inspect the definitions and role-specific tests as well. Tests run as the owner, with substituted helpers or historical function bodies have different evidential scope from tests using the actual authenticated role and current functions. The delivery PR names the local SQL and synthetic backend results, substitutions and current candidate CI; those checks do not certify every live session, view, Storage path or concurrent company-switch outcome.