orbiter_postgres 0.1.5
orbiter_postgres: ^0.1.5 copied to clipboard
PostgreSQL kit for orbiter: the pieces for using it together with slingshot_postgres (a per-request row-level-security transaction middleware and a prefix tsquery builder).
orbiter_postgres #
PostgreSQL kit for orbiter,
for applications that use it together with
slingshot_postgres.
The framework has no database dependency; this package holds the
PostgreSQL-specific pieces for using the two together: a per-request RLS
transaction (rlsTransaction), the same transaction opened by a repository or
use case (withTenantTransaction), and a prefix search builder
(prefixTsQuery).
Experimental (pre-1.0). Install from pub.dev; the framework and the connector come with it:
dart pub add orbiter_postgres
The connector ships its native library pre-built and needs libpq at run time;
see its README for AOT and container builds.
rlsTransaction: one RLS transaction per request #
final handler = Pipeline()
.add(authenticate()) // yours: puts the principal in the context
.add(rlsTransaction(
pool: pool,
tenantResolver: (request) => request.context.getOrNull<Principal>()?.tenantId,
onUnresolved: (_) => Response.forbidden(),
))
.addHandler(router.handler);
// In a handler:
final rows = await request.rls.executor.execute('SELECT ...');
Per request the middleware borrows a connection through PostgresUnitOfWork,
runs SELECT set_config($name, $tenant, true) (both bound as parameters) as the
first statement of the transaction, hands the handler an executor pinned to that
transaction, and ends it deterministically. The connection always goes back to
the pool, from a cleanup hook if nothing else did.
What ends the transaction #
| Situation | Outcome |
|---|---|
| Buffered body, status below 400 | COMMIT before the response is written, so a failing commit becomes an error response, never a 200 already sent |
| Buffered body, status 400 or above | rollback (predicate shouldCommit, default commitOnSuccess) |
| Handler throws | rollback, then the error reaches the error boundary |
request.rls.setRollbackOnly() |
rollback, whatever the status |
Stream body |
decided after the handler, applied by the cleanup hook after the last byte, so the stream can keep reading through the executor |
No tenant (null or empty) |
onUnresolved; no connection is borrowed, no next() |
| Pool exhausted | 503 with Retry-After: 1 (onUnavailable) |
Errors and configuration #
Failures of the transaction itself are thrown as the sealed RlsException, so an errorHandler can switch
over it. Each carries the tenantId, and the original database error stays in cause / causeStackTrace;
toString does not repeat the server message, so mapping it to a response never leaks it by accident.
| Exception | When | What the client gets |
|---|---|---|
RlsContextException |
applying the tenant setting failed; already rolled back, connection returned | whatever the errorHandler answers |
RlsCommitException, afterResponse: false |
COMMIT failed before the response was written |
whatever the errorHandler answers |
RlsCommitException, afterResponse: true |
COMMIT of a streamed body failed after the last byte |
the response already sent; the failure only reaches onError |
The callbacks are typed: tenantResolver is a TenantResolver (FutureOr<String?> Function(Request), where
null or empty means no tenant) and shouldCommit a CommitPredicate (bool Function(Response),
commitOnSuccess by default). settingName (default app.tenant_id) must match the name the database policy
reads.
rlsTransactionOn is the same middleware over any UnitOfWork factory. It exists to exercise the policy in
tests without the native library or a database; production code uses rlsTransaction.
Rules this package cannot enforce #
- Streamed routes under RLS must be read-only. A
COMMITthat fails after the last byte reaches only the server-sideonError(RlsCommitExceptionwithafterResponse: true); the client already has its response. - Never capture the pool in a handler to run an autocommit statement on tenant data: the tenant setting does not exist there. Treat it as a review rule.
- The tenant comes from an authenticated source, never from the body, the query or an unauthenticated header.
- Routes without a tenant (health, login) go in another
Pipeline. - The database role must be neither superuser, nor table owner, nor
BYPASSRLS, and the table needsFORCE ROW LEVEL SECURITY; otherwise the policy does not apply. settingName(defaultapp.tenant_id) must match the name the policy reads withcurrent_setting(..., true). It is configuration on purpose.
Cost #
Each request adds a BEGIN, the set_config, a COMMIT and the pool's
session reset on return. The cost of this middleware has not been measured.
Tests #
test/rls_middleware_test.dart, test/tenant_transaction_test.dart and
test/prefix_ts_query_test.dart need no database. The integration tests (test/integration/postgres_live_test.dart,
rls_live_test.dart and prefix_ts_query_live_test.dart) need PostgreSQL at
localhost:5432 (dbname=teste user=postgres password=123).
prefix_ts_query_live_test.dart creates no schema or role.
rls_live_test.dart uses a superuser because it creates schema rls_mw and
roles rls_mw_owner/rls_mw_app, so do not run two instances at once:
dart test
The integration tests skip silently without the library; confirm the skipped count is zero.
Stress suite (integrity under load) #
test/stress/ is tagged stress (dart_test.yaml). It asserts integrity invariants only and never emits time, throughput or latency: client and server share one event loop and the handlers sleep on purpose.
dart test -x stress # fast suite, skips the stress files
dart test -t stress -r compact
Parameters (environment): STRESS_REQUESTS (400), STRESS_USERS (32), STRESS_SEED (random, always printed; the same seed rebuilds the same intents). It uses its own schema and roles (rls_stress*), so it can run next to rls_live_test.dart.
It covers four pool configurations (1, 4, 2 without session reset, 2 with a 100 ms acquire timeout), four tenants with one hot tenant, client aborts at four moments, handlers that fail or answer 409, and a clean shutdown (server.close() then pool.close()) with requests in flight, plus a stream the client abandons before the first byte. Two more cases run withTenantTransaction directly under the same parameters (pool of 2, with and without session reset), with bodies that throw or set rollback-only after inserting, and check that no caller sees another tenant's rows or a stale local setting.
The suite SKIPS silently when the native library or Postgres is missing: check the printed executed=N skipped=M line (skipped must be 0).
withTenantTransaction: one RLS transaction opened by the repository #
rlsTransaction keeps the transaction open for the whole handler. When a use
case has to call something slow or external (a KMS, another service) outside
the transaction, open it where it is needed instead:
final appointment = await withTenantTransaction(pool, principal.tenantId,
(tx) async {
final rs = await tx.executor.execute(
r'SELECT ... FROM appointment WHERE id = $1',
params: [Param.int8(id)],
);
try {
return Appointment.fromRow(rs.row(0));
} finally {
rs.release();
}
}, localSettings: {'app.correlation_id': correlationId});
The function receives the same RlsTransaction a handler gets from
request.rls (executor, tenantId, setRollbackOnly()), and its value is
returned. The two modes are independent: an application can use either, or
both, under the same database policy.
| Situation | What happens |
|---|---|
tenant null or empty |
TenantRequiredException, before the pool is touched |
| pool exhausted | PoolExhaustedException propagates; map it (for example to 503 with Retry-After) |
| applying the settings fails | rollback, then RlsContextException |
| the function throws | rollback, and the error is rethrown |
tx.setRollbackOnly() |
rollback, and the value is still returned |
COMMIT fails |
rollback, then RlsCommitException with afterResponse: false |
The connection goes back to the pool on every exit. TenantRequiredException
is not an RlsException: there is no tenant and no database error behind it.
localSettings are other transaction-local settings applied in the same
first statement as the tenant, one set_config(name, value, true) each, every
name and value bound as a parameter:
SELECT set_config($1, $2, true), set_config($3, $4, true), .... Names must be
prefixed custom settings (app.correlation_id); an empty name, a name without
a dot or the tenant's own settingName throws ArgumentError before the pool
is touched. Without localSettings the statement is the one rlsTransaction
sends.
withTenantTransactionOn is the same function over any UnitOfWork factory,
for tests without the native library or a database. The rules above about the
database role, FORCE ROW LEVEL SECURITY and an authenticated tenant apply
here too.
prefixTsQuery: prefix search from free text #
Turns what a user types into a prefix tsquery, safe to bind as a parameter:
prefixTsQuery('jo sil'); // "'jo':* & 'sil':*"
prefixTsQuery("d'avila & !"); // "'d':* & 'avila':*"
prefixTsQuery(' &|! '); // null
With a generated tsvector column and a GIN index:
CREATE TABLE person (
id serial PRIMARY KEY,
name text NOT NULL,
doc tsvector GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED
);
CREATE INDEX person_doc ON person USING gin (doc);
final q = prefixTsQuery(term);
if (q == null) return Response.badRequest(); // or run the query without the filter
final rows = await request.rls.executor.execute(
"SELECT id, name FROM person WHERE doc @@ to_tsquery('simple', \$1)",
params: [Param.text(q)],
);
Only runs of Unicode letters, combining marks and digits survive; every other
character (& | ! ( ) : * < - >, quotes, backslashes, spaces, punctuation) is
a separator. Each word becomes a quoted prefix lexeme, joined with &.
- Normalise the input the way the index was built. Case is folded by the
text search configuration, but accents are kept: if the
tsvectorcomes fromunaccent(...), apply the same removal to the term. nullmeans no word: skip the filter.to_tsqueryon an empty string matches nothing.- Always bind the result (
Param.text); never concatenate it into SQL.
Cost #
One regular expression pass over the input. Not measured.
Example: CRUD server #
example/postgres_crud_server.dart wires BackendServer, Pipeline and Router to the native pool. It
expects a users table:
CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE);
POSTGRES_HOST=localhost POSTGRES_DB=teste POSTGRES_USER=postgres POSTGRES_PASSWORD=123 \
dart run example/postgres_crud_server.dart
Endpoints: GET /health, GET /api/v1/users (first 50), GET /api/v1/users/:id, POST /api/v1/users
(inside a transaction). Error responses carry generic messages; exception details and PostgreSQL server
messages are logged on the server only. The server shuts down on SIGINT and SIGTERM.
test/integration/postgres_live_test.dart runs against the same database and skips when it, or the native
library, is unavailable.
License #
MIT, see LICENSE.