Database Access Profiles

An access profile declares the PostgreSQL roles a product uses and which of them may read or write which rows. The database compiler lowers it to role creation, grants and role-targeted row policies. Without a profile the compiler emits the role-less security output it always has, byte for byte.

All model types are exported by package:vyuh_blueprint/vyuh_blueprint.dart.

Declare a profile

const app = DbRole.product('ops_app', login: true, runtime: true);
const worker = DbRole.product('ops_worker', login: true, worker: true);
const service = DbRole.external('service_role');
const anon = DbRole.external('anon', denied: true);

final blueprint = Blueprint(
  // ...
  db: const BlueprintDb(
    access: DbAccessProfile(
      roles: [app, worker, service, anon],
      defaults: [
        DbGrantRule(
          role: app,
          privileges: {DbPrivilege.select, DbPrivilege.insert, DbPrivilege.update},
        ), // rows default to DbRowRule.tenant()
      ],
    ),
  ),
);

// Per entity, replacing the defaults for that entity only.
final codes = EntityDb(access: DbEntityAccess.catalog(reader: app));

EntityDb.access replaces the profile defaults for that entity; it does not add to them.

Roles

DbRole is identified by name: two roles with the same name are equal.

Constructor	Meaning
DbRole.product(name, {login, runtime, worker})	Created by the product's generated SQL. login allows connecting.
DbRole.external(name, {denied})	Exists already (a platform's service_role, anon, authenticated). The product only grants to or revokes from it.

• runtime: true marks the role the Blueprint runtime serves requests as. At most one per profile.
• worker: true marks the role the outbox worker connects as. At most one per profile, and never on the runtime role. External roles are never runtime or worker roles.
• denied: true (external only) revokes the role from every owned object; it may hold no privilege.
• DbAccessProfile.runtimeRole and workerRole return the flagged roles.

Role names

A role name must be lower-case snake_case (^[a-z_][a-z0-9_]*$), at most 63 bytes, must not start with pg_, and must not be a reserved role name (public, current_user, current_role, session_user, user, none, all) or a PostgreSQL reserved word. A name that passes is written unquoted. DbIdentifierRules.roleNameProblem(name) returns the reason a name fails. The validator rejects a bad name, and the SQL lowering throws ArgumentError for one that reaches it.

Grants and row rules

DbGrantRule({role, privileges, rows}) grants privileges to role on the rows rows admits. rows defaults to DbRowRule.tenant().

DbPrivilege: select, insert, update, delete. The first three lower to a table grant and a role-targeted policy for that command. delete lowers to a table grant and a FOR DELETE policy; no rule grants it unless it asks.

DbRowRule says which rows a rule reaches:

Rule	Rows
DbRowRule.tenant()	Tenant field equals the session tenant (vyuh.tenant_id).
DbRowRule.all()	Every row.
DbRowRule.tenantOrGlobal()	The session tenant's rows, plus rows with no tenant.
DbRowRule.none()	Grants the privileges and creates no policy. Under enforced RLS the role then reaches no row, unless another policy (such as a hand-written one) admits it. With RLS off, the grant alone decides.
DbRowRule.predicate(Expr<bool>)	Rows for which the expression is true. It reads the row's own host columns only: comparisons (Comparison over FieldValue), null tests and And/Or/Not. A fact, an actor or a related row is not a column and is refused.

Owned child tables (facets, history) follow their host row, so no rule names a parent.

Entity presets

EntityDb.access takes a DbEntityAccess:

Form	Effect
DbEntityAccess.rules(List<DbGrantRule>)	Explicit rules.
.catalog(reader:)	reader selects every row.
.readOnly(role:)	role selects its tenant's rows.
.appendOnly(role:)	role selects and inserts its tenant's rows.
.serviceOnly(role:)	role selects, inserts and updates every row.
.tenantOrGlobal(role:, privileges:)	Tenant rows plus untenanted rows; default privileges select, insert, update.
.columns(role:, fields:)	External entities only: role reads the listed projection fields.

Every form except .columns is valid only on a Blueprint-owned entity; .columns is valid only on an external entity.

Entities the product does not author

Entities that a shared package's module declares, such as vyuh_iam's or vyuh_workflow_blueprint's, cannot take an EntityDb.access from the product. BlueprintDb.entityAccess sets their access by typed entity reference instead:

const action = EntityRef(module: ModuleRef('iam'), name: 'action');

final blueprint = Blueprint(
  // ...
  db: BlueprintDb(
    access: profile,
    entityAccess: [
      DbEntityAccessOverride(
        entity: action,
        access: DbEntityAccess.catalog(reader: app),
      ),
    ],
  ),
);

• An override applies after the entity's own EntityDb.access. When both exist, the product override wins. The lowering, the validator and the compiler diagnostics all see the entity with the overridden access.
• The same rules apply as for EntityDb.access: rules only on a Blueprint-owned entity, and columns only on an external one.
• Each override must name a declared entity, and each entity may appear only once.
• It is a list and not a map, so a const Blueprint can declare it. EntityRef has value equality, and a constant map key may not.
• The definition document encodes the overrides under db.entity_access, keyed by qualified entity type (such as iam.action) and sorted. The key is omitted when the list is empty.

What the compiler generates

Every policy is DROP POLICY IF EXISTS then CREATE POLICY, named <table>_<role>_<command> (shortened to PostgreSQL's 63-byte limit). Every role in the SQL passes through the same identifier check.

Object	Generated for a role with rules
Roles	One block per product role, sorted by name, opening 000_infrastructure.sql: CREATE ROLE ... NOSUPERUSER NOBYPASSRLS (LOGIN or NOLOGIN) and GRANT CONNECT on the current database.
Schemas	GRANT USAGE on each owned schema a role has a rule in (900_schema_access.sql), and on the runtime schema for the runtime role. The runtime role also gets USAGE on every outbox and versioned schema.
Host table	GRANT of the rule's privileges, plus one policy per privilege from the row rule.
Facet tables	Same grant; policies reach rows through EXISTS on the host row.
History (versions, audits, revisions)	Always append-only: SELECT and INSERT only, sequence USAGE for audits. UPDATE and DELETE are never granted. Only _revisions gets UPDATE, and only for the runtime role, to advance the draft head. A host rule with insert or update yields SELECT and INSERT on history. Tenant reads check the history table's own tenant_id. On a tenanted entity, every history write (INSERT, and the revisions UPDATE) requires the host row that the history row names, with the same tenant_id and under the host's row rule (see below). Predicate rules also read the host row.
_full views	GRANT SELECT to roles that select the host.
External projections	Default selecting roles get SELECT; DbEntityAccess.columns gives GRANT SELECT (col, ...) only.
entity_version_contents	Runtime role SELECT, INSERT. Insert is allowed on the tenant check or untenanted rows; select also sees contents referenced by a version row of an entity whose rules let the runtime role read history.
Outbox	See the worker role below.
Runtime ledger	The runtime role gets exactly the privileges the runtime stores use, each with an _insert (and where updated, _update) policy and the request-tenant predicate. Catalog tables (blueprint_*, entity_projections) are not granted.
Denied roles	REVOKE ALL on each owned schema, its tables, sequences and functions, and on every _full view and projection.

On a tenanted entity, a history write proves its host row. For the runtime role on tickets_versions the policy is:

CREATE POLICY tickets_versions_app_insert ON toy.tickets_versions
  FOR INSERT TO app WITH CHECK (
    tenant_id = NULLIF(current_setting('vyuh.tenant_id', true), '')::uuid
    AND EXISTS (SELECT 1 FROM toy.tickets h
      WHERE h.id = tickets_versions.entity_id
        AND h.tenant_id IS NOT DISTINCT FROM tickets_versions.tenant_id
        AND h.tenant_id = NULLIF(current_setting('vyuh.tenant_id', true), '')::uuid));

• The audit tables and the revisions INSERT have the same form.
• The revisions UPDATE uses it in both USING and WITH CHECK.
• A tenantOrGlobal rule uses its tenant-or-null form in both places.
• A predicate rule checks the predicate on h in place of the session tenant.
• An all rule keeps only the EXISTS, with the id and tenant match: the role reaches every tenant, but it still cannot file a row's history under another tenant.
• In one transaction, the runtime writes a created or changed host row before its history, and deletes a purged host row only after its version and audit rows are written, so the check always sees the row.
• The check reads the host row, so the rule needs select.

REVOKE ... FROM PUBLIC statements, immutable-history triggers and REVOKE DELETE are kept regardless of the profile.

Runtime role and worker role

The outbox and action_effects are settled by a worker that runs outside any request and so has no tenant. The two roles split the work:

• The runtime role enqueues inside a tenanted request. It gets INSERT on the outbox (policy tenant_id IS NOT DISTINCT FROM the session tenant) and the outbox sequence, nothing on outbox_subscriptions, and SELECT, INSERT on action_effects with the request-tenant check. It has no UPDATE on action_effects.
• The worker role claims and settles across tenants: SELECT, UPDATE on <schema>.outbox and <runtime>.action_effects, SELECT on outbox_subscriptions, all USING (true), plus USAGE on the outbox and runtime schemas. It gets no sequence and no other table. No access rule may grant the worker role.

With no worker role declared, the runtime role holds these cross-tenant policies itself and the compiler warns that it settles every tenant.

The worker connection

BlueprintPersistenceConfig(outboxWorkerConnection: DbAdapter?) (in vyuh_blueprint_server) is the connection that logs in as the worker role. BlueprintProtocolsConfig.createOutboxStore(db:, blueprint:) binds the outbox store to it, or to the runtime connection when none is set.

validateOutboxWorkerConnection() throws a StateError naming the worker role when the profile declares one and no connection is configured. BlueprintServerRuntime.bindConfigured calls it before building the runtime, so startup fails fast; BlueprintServerPlugin takes the same path. Opening the connection as the worker role is the product's job.

The convenience binders BlueprintServerRuntime.bindBlueprint and blueprintFeature take no persistence config. A profile with a worker role therefore needs BlueprintServerPlugin(persistence: ...) or a BlueprintProtocolsConfig with persistence:.

BlueprintServerPlugin and a multi-Blueprint BlueprintProtocolsConfig compose their sources' db:

• A source that declares none defers to the others.
• Sources that declare the same one agree.
• Sources that declare different ones fail: the plugin reports a blocking blueprint.binding.db_conflict finding, and the config throws a StateError. The plugin then leaves out the db_access findings, which would only follow from the conflict.

Row-level security mode

ModuleDb.rlsMode is DbRlsMode.off or DbRlsMode.enforced (the permissive value is removed). A schema enforces RLS unless every module placing a managed entity in it declares off; one enforcing module keeps it enforced. For a schema with RLS off the compiler emits no ENABLE ROW LEVEL SECURITY and no policy, including on entity_version_contents and outbox tables. Grants, USAGE, REVOKE ... FROM PUBLIC and the history triggers remain. A tenant, tenantOrGlobal or predicate rule there therefore reaches every row, and the compiler warns about each one.

Ownership limits

• Roles are created if missing and never altered or dropped. An existing role keeps its password, connection limit and attributes. If it already has SUPERUSER or BYPASSRLS, the block raises a warning that its policies do not apply to it.
• External roles are never created. The product only grants to or revokes from them.
• Grants into schemas the product does not own stay product SQL. The compiler writes grants and revokes only for owned schemas and the runtime schema; it never names shared, public, auth or other foreign schemas. An external entity's table is secured by its owning subsystem, so it accepts only a columns read of its projection.
• Contributions may not carry role statements.

Validation

Model errors use the code prefix blueprint.validation.. The paths are under <blueprint>.db.access or <entity>.db.access. For an override they are <blueprint>.db.entity_access[<index or entity type>].

Code	Reported when
db_access_profile_missing	An entity declares access, or has an override, but the Blueprint has no profile.
db_access_entity_unknown	An entityAccess override names an entity that no module declares.
db_access_entity_duplicate	An entity has more than one entityAccess override.
db_access_role_duplicate	A role is declared twice.
db_access_role_owner_clash	A role name is both product and external.
db_access_role_name_invalid	A role name fails the role-name rules.
db_access_runtime_role_multiple	More than one runtime role.
db_access_worker_role_multiple	More than one worker role.
db_access_role_runtime_worker	One role is both runtime and worker.
db_access_worker_role_granted	A default, rule or columns read grants the worker role.
db_access_role_unknown	A rule names a role not in the profile.
db_access_denied_role_granted	A denied role appears in a rule with a privilege, or in a columns read.
db_access_rule_role_duplicate	One role appears in two rules of one rule list.
db_access_external_rules	Rules on an external entity (only columns is allowed).
db_access_columns_owned	A columns read on a Blueprint-owned entity.
db_access_column_unknown	A columns field is not a column of the projection.

The compiler adds diagnostics without codes, reported at <schema>.<entity>.db.access. Errors reject the compilation:

• a columns read on an external entity in an owned schema;
• any rules override on an external entity;
• one role named in two rules of one rule list (colliding policy names);
• a row predicate on a field that is not a host column;
• a rule that writes without select, where facet or history policies read the host row. Any write on a tenanted entity under a rule other than none counts, because its audit rows prove the host row.

Warnings keep the lowering:

• the runtime role settles every tenant because no worker role is declared;
• a tenant, tenantOrGlobal or predicate rule in a schema with RLS off, which gets no policy and so reaches every row in every tenant (all and none rules are not reported);
• a tenant or tenantOrGlobal rule on an entity with no tenant field, which is granted with no policy and reaches no row;
• a role that selects a host whose _full view joins a related projection it cannot select.

A target's emit command prints each warning to stderr as warning: <path>: <message> Remedy: <remedy> and still writes the tree. blueprint db emit passes these lines through to its own stderr. Warnings do not change either exit code.