Skip to content

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). 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). 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, API keys with scopes).

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 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).

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 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). 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) and the TypeScript SDK for Next.js (Next.js); 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). 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.