# Identity

Three ways to say who is asking, all per transaction:

1. **A trusted backend** calls `SELECT authz.act_as('user', '42')`. Simple, but anything
   that runs as the app role can then claim to be anyone.
2. **API keys** that users create for themselves (`authz.create_api_key`). Only a
   SHA-256 hash of each is stored, with its first ten characters to tell keys apart.
   A key can carry **scopes**: `read` (built in) or the
   policy's own (`scope files = ...`) limit which commands and permissions the
   transaction gets.
   A read-only session can't share, approve, or mint broader keys.
   A session limited by scopes makes, and revokes, only a key no wider than itself: one that has scopes, each
   of them the session's (AZ704 otherwise).
   Pass the key as a parameter (`SELECT authz.login_key($1)`, as the generated clients do), not
   written into the statement, or it ends up in the server's statement log; the same for a JWT.
   `authz.login_key(key)` works in read-only transactions too (a replica), and requests with
   the same key never wait for each other: when a key was last used (`last_used_at`) is kept to
   the minute, and not by read-only transactions.
3. **JWTs** from your identity provider: `authz.login_jwt(token)` checks the token's
   signature (HS256 with a secret, or RS256 with the provider's keys, below), a required `exp` and optional `nbf`, and the issuer and the audience once the settings
   `jwt_issuer` and `jwt_audience` name them (a token that names none is then refused); a `scope` claim limits
   the transaction.
   The settings are rows of `authz.settings`, which the owner writes and the app role
   can't read:

   ```sql
   INSERT INTO authz.settings VALUES ('jwt_secret', '...'), ('jwt_issuer', 'https://id.example')
     ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;
   ```

   `jwt_secret` is the secret your identity provider signs HS256 tokens with, and HS256 asks for 32 random
   bytes or more (RFC 7518): a short one can be guessed from any token. With no secret and no stored keys,
   `login_jwt` signs nobody in.
   The other settings: `jwt_audience`, `jwt_user_claim` (the claim that holds
   the user's id, `sub` by default) and `jwt_type_claim` (below).

   **A provider that signs with a key pair** (RS256) serves its public keys as a key set (a JWKS, RFC 7517).
   The owner stores the set, and `login_jwt` checks an RS256 token against the key its `kid` names:

   ```sql
   SELECT * FROM authz.set_jwks('{"keys": [{"kty": "RSA", "kid": "2026-11", "n": "...", "e": "AQAB"}]}');
   ```

   The set replaces the stored one, and a row for each key says what became of it: `added`, `replaced` (its
   `kid` has another key now), `kept`, `removed` (it left the set), or `skipped` and why.
   Stored are RSA keys for signatures, of 2048 to 4096 bits, with the exponent 65537.
   Store the set again when the provider's keys change: from then on, a token signed by a key that left the
   set is refused.
   The database fetches nothing itself. `rowstile jwks https://id.example/.well-known/jwks.json` fetches the
   set from the provider and stores it, for a scheduled job or a deploy step, and with `--check` says whether
   the stored keys are still the provider's ([Tools](/reference/tools.md)).
   A set that leaves no key is refused, saying why each key was skipped, and the stored keys stay (AZ719): a
   fetch that failed signs nobody out.
   `authz.set_jwks` is the owner's, as the settings are: whoever stores a key signs in as anyone. The app
   role can't call it or read the keys, and the audit trail says when the keys were set.

   What a token says of itself (`alg`) chooses its check, and nothing else does. An HS256 token is checked
   against `jwt_secret` alone and an RS256 token against the stored keys alone, so a public key, which anyone
   has, is never taken for the secret.
   A token whose `kid` names no stored key is refused, and one that names no key is checked against the
   stored key when there is one only.
   The signature is checked in plain SQL, which takes a few milliseconds a sign-in, more with a longer key
   ([Limits](/reference/limits.md)). A connection checks a token's signature once: `login_jwt` leaves a note of the check
   in a setting of the connection (`authz.checked_tokens`), and the same token signing in there again costs
   what an HS256 token does.
   A note says what the check did, that this key signed this token, and no more: the token's time, issuer,
   audience and user are read at every sign-in.
   The app role may read and set that setting, as any, so a note is believed only as the database made it,
   with the key that signs sessions: for that token, that key and that connection.
   A connection keeps the notes of the 64 tokens it checked last, from the commit of the transaction that
   checked each (a pooler's connection serves many tokens).
   Tokens signed another way (ES256, EdDSA) aren't checked in the database: check
   them in your backend, and sign in with `authz.act_as`.

What a scope leaves a key able to do can be said in the policy's tests: a line with `with scope read`
is checked as a key limited to that scope would be
([the language's tests](/reference/language.md), [API keys with scopes](/cookbook/api-keys-with-scopes.md)).

**Services** (principal types) sign in the same three ways. The backend names the type
(`SELECT authz.act_as('service', '7')`);
`authz.create_api_key('deploy', '', NULL, 'service', '7')` makes service 7 a key, for someone
holding `manage_keys` on it (`can manage_keys = owner`); a JWT names the type in the claim the
setting `jwt_type_claim` says.
While a service is signed in, `authz.uid()` is NULL and
`authz.principal()` returns `('service', '7')`; the audit trail and `created_by` say `service:7`.
They name a user by their id (`42`), or as `user:<id>` when the id holds `:` or is `nobody` (`user:service:7`), so
no user reads as a service or as nobody; the SDKs write a principal the same way, and read these back.
`authz.who` lists users.
Some calls are for users only: the custom roles API (`create_role`, `set_role_permissions`, `delete_role`,
`roles_of`), access requests (`request_access`, `pending_requests`, `decide_request`, `cancel_request`),
access reviews (`start_review`, `review_items`, `review_decide`, `close_review`), `break_glass`, a key for
oneself (`create_api_key` without a principal) and `view_as`.
A service signed in is refused there, even one the policy gives the permission the call asks for, and the
error says so: "authz.create_role is for users, not services (service 8 is signed in)", AZ715.
A service shares and unshares as itself, and a user holding `manage_keys` on it makes its keys.

**Whoever signs in is a row of their type.** An id the type's table doesn't have (a user deleted since, an id
the app made up) is nobody: `authz.uid()` is NULL and `authz.principal()` returns no row, as for a row that
fails the type's `where`.
So a deleted user's leftover `owner_id` grants nothing, and an app inserts a new
user's row before it signs in as them.
A principal's API keys go with its row: deleting the row, giving it a new id (as its shares do) or emptying its
table deletes them, each with a line in the audit trail, so a row that later has that id signs in with none of
them.
A key made while its principal is deleted waits for the delete and is refused; a delete in REPEATABLE READ or
SERIALIZABLE whose snapshot is older than a key made since fails with a serialization error (40001), to retry.
Applying the policy deletes the keys of principals whose rows went while nothing watched them.

**Every sign-in is signed**. `act_as`, the logins and `view_as` put an HMAC in `authz.session`, over
who is signed in, the scopes, the backend and the transaction, with a key the app role can't read.
The
settings are believed only with it, so nothing but rowstile's own functions (`act_as`, the logins, `view_as`,
`sign_out`, and `who`, `explain` and the like for the length of their call) can say who is signed in, with which
scopes, viewing as whom, on any Postgres: setting `authz.user_id` is an error, and so is
a signature from another transaction or connection.
A signature is for its transaction: what an earlier sign-in of the same transaction signed, put back with that
signature, is believed again (the transaction as it was before a `view_as`, say). The backend, which may sign
out and in, gets nothing more from it.
A transaction in which nobody signed in gets an error
too, not an empty result (`act_as(NULL, NULL)` says "nobody" on purpose).
Sessions of the policy's owner
and superusers are believed without a signature (psql, migrations, tests).
[`core/tests/sessions.sh`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/core/tests/sessions.sh) checks all
of it, on the stock Postgres image.

**A transaction is one principal's**. Once someone is signed in, `act_as`, `login_key` and `login_jwt` refuse to
sign the transaction in as anyone else, nobody included (AZ716).
While the transaction views as someone or is limited by scopes, they refuse to sign it in again at all, even as
the same principal: that would end what limits it.
A first sign-in after `act_as(NULL, NULL)` is fine, and so is the same principal again (a key of theirs too,
whose scopes then limit the transaction). A refusal caught at a savepoint leaves the transaction as it was.
`view_as` isn't held to it (it is read-only, with a reason on record), nor are rowstile's own functions that ask
as others and put back the caller (`who`, `explain`, `who_among`, the invariants).
A test's transaction that acts for one person, then another (rolled back at the end), calls `authz.sign_out()`
between them: nobody is signed in then, as after `act_as(NULL, NULL)`, and the next sign-in may be anyone. The
SDKs' test modes do it.
Sessions of the policy's owner and superusers, which may say who is signed in by hand, aren't held to it.
The SDKs refuse such a sign-in earlier, in their own words, before anything is sent ([App code](/reference/app-code.md)).

**Is the session limited?** `authz.session_limited()` is true while the transaction is limited, by a key's or a
token's scopes or by viewing as someone, and false for a plain sign-in, a key without scopes and nobody.
An app asks it before it serves what scopes can't limit, such as a sync engine's shapes
([Electric's route](/reference/sync.md#the-route-electricshape) refuses such a session).
Like the rest, it believes the settings only when they are signed: settings set by hand are an error, as in any
other read.

**Signing in turns JIT off** until the transaction ends (`act_as`, the logins and `view_as`). The rules' lookups
are hashed once per read but estimated once per row, so a read of a few thousand rows through them passes
Postgres's `jit_above_cost`, and JIT spends most of a second compiling a read that runs in a few milliseconds
([Speed and limits](/reference/limits.md)). The next transaction has the session's own setting again, and the app may turn
JIT back on after signing in (`SET LOCAL jit = on`).

Who may call `act_as`: the app role and its members (the policy grants it), so a compromised app can still
choose users.
Keep the app role's credentials in the backend.

## Users and groups from an identity provider (SCIM)

An identity provider (Okta, Microsoft Entra ID) writes its directory's users and groups to an app by sending SCIM
requests (RFC 7644), and the database answers them: `authz.scim(method, path, query, body, token)` takes a
request's parts and answers a `status`, a `body` and a `location`. The app's part is a route that passes on the
request's method, its path below the route (`Groups/10`), its query string and its body as they were sent and its
bearer token, and answers what comes back. The Python SDK has that route for FastAPI
([FastAPI](/stacks/fastapi.md#users-and-groups-from-an-identity-provider-scim)) and the TypeScript SDK for
Next.js ([Next.js](/stacks/nextjs.md#users-and-groups-from-an-identity-provider-scim)); the other SDKs' routes
are not there yet. A body that is no JSON is answered 400, once the key says who asks.

**The owner says what the endpoint reads**, in one call that replaces the stored settings (`'{}'` turns the
endpoint off):

```sql
SELECT authz.set_scim('{
  "principal": "user:1",
  "group": "team.member", "group_name": "name",
  "user_name": "name"
}');
```

- `principal`: whose API key signs the provider in, as `type:id`: a user the app holds for the provider, or a row
  of a type that signs in (a service, above). The provider's bearer token is an API key of that principal, made as
  any of its keys is (`authz.create_api_key`).
- `group`: the groups, as `type.relation`: a relation `authz.sync_members` takes ([App code](/reference/app-code.md)).
  `group_name` and `group_external_id` name the columns of the groups' table that hold a group's `displayName` and
  its `externalId`.
- `user_name`, `user_external_id` and `user_active`: the columns of the users' table that hold a user's `userName`,
  its `externalId` and whether it is active (a boolean).
- `user_resource`: a jsonb column where the app's function puts the user as the provider wrote it. A GET then
  answers that, with what the three columns say now.
  Name one: without it a user is what the three columns say, and Microsoft Entra ID's validator checks that
  "Returned values from GET match the passed values from the POST request" (Microsoft's provisioning docs, read
  2026-11-16).
- `group_function` and `user_function`: the app's functions that make, change and delete a group and a user
  (below).
- `page`: the most a list answers.

Each setting is checked when set (AZ720), and `authz.lint()` says when one no longer holds (a column dropped, a
function given to another role): a request that meets it is an error (AZ722).
`authz.set_scim` is the owner's, as the principal it names writes users and groups: the app role can't call
it.

**The app's functions make the rows.** Users and groups are rows of the app's own tables, with columns rowstile
doesn't know, so the app gives a function for each, `(p_id text, p_resource jsonb) RETURNS text`: the row's key
and the user or the group as the request leaves it come in, and the key goes out. A NULL `p_id` is a row to make,
a NULL `p_resource` one to delete. The function runs as the owner, so it is the owner's own: one that another
role owns is refused when set, and is an error from then on if it is given away later.
The function says no by raising: a row whose key or name another row has (a unique constraint) is answered 409,
and what the function raises itself, or a constraint refuses, 400, each with the database's words.
With no function, a request that makes or deletes a user or a group, changes a user or renames a group is
answered 501. A group's members are written all the same, and the users and the groups the app has are read and
matched.
A user comes as a POST's or a PUT's body, or as a PATCH's operations applied to the user as it is, never with a
password (Okta's docs: "Okta sends the `password` parameter in a create user request, even if password sync isn't
enabled").
A request may say less of a user than the app has (a PUT without an email, a PATCH of a user the app made by
itself): write the function so that what `p_resource` says nothing of stays, `coalesce(p_resource->>'displayName',
name)`. The stack pages each show one.

**Groups** are read, listed, found by `displayName`, `externalId` or `id`, patched (members added, removed and
replaced, as Microsoft Entra ID and Okta write a PATCH), replaced, and made, renamed and deleted through the
app's function.
Members are written as `authz.sync_members` writes them, with its line in the audit trail, the provider's
principal as who did it.

**Users** are read, listed and found by `userName`, `externalId` or `id`, and made, changed and deleted through
the app's function.
A user the provider deactivates stays, answered as not active: what such a user may do is the policy's to say
(`type user = app.users where {active}` makes one nobody). A `userName` another user has is answered 409.

**A list answers a page**: `startIndex` and `count` as the provider sends them, the setting `page` at most, with
how many there are in all. `ServiceProviderConfig`, `ResourceTypes` and `Schemas` say what the endpoint serves:
PATCH and filters; no Bulk, no sorting, no ETags, no password change, no `/Me`, no search by POST, each answered
501.

**The key alone says who asks.** The app role may call `authz.scim`, and nothing in it trusts the caller: a
request whose token isn't a key of the settings' principal is answered 401, and a key limited to scopes that write
nothing only reads.
An answered request's transaction is signed in as that principal, as `authz.login_key` signs one in, so call
`authz.scim` in a transaction of its own; a refused one signs nobody in.
A refusal is an answer with SCIM's error body (AZ721) and changes nothing; a request is done whole or not at
all.
An error stays an error, for the app to see: settings that no longer hold (AZ722), the app's function failing.
The SDKs' routes answer those 500 in SCIM's words and log them.
