IF Commons GitHub

Protected access

0014Active

0014-sql-protected-access · owner if · domain data

The layer and its settings

FR-001checkpassing

The settings MUST be a typed value, each with a default: the schema of the tables (data), the schema of the views (api), the enforcement (auto, the default: enforced where the dialect can, a convention where it cannot; enforced; or convention), the guard (on, the default, or off), the masks (on, the default, or off), the roles that see a masked value as it is (none by default) and the name of the setting a migration sets (lifecycle.migrating); and a project MUST be able to change each.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: the settings are typed, each has a default, and a project can change each

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/deps.ts, data/sql/extensions/protected-access/mod.ts, data/sql/extensions/protected-access/settings.ts

Tests: passed: suite/protected-access.test.ts the settings are typed, each has a default, and a project can change each

Cited by: Data, sql/extensions/protected-access/settings

FR-002checkpassing

The tables MUST be in the schema of the tables, and for each table of the logical schema there MUST be a view of the same name and columns in the schema of the views (written without the schema's words, where the dialect has no schemas and the words are in the table's name); a table of an extension (the documentation tables) MUST NOT have a view.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: the tables are in their schema and a view of each is in the other, read through on every engine, with the comment, and no view for a table of an extension

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/source.ts, data/sql/extensions/protected-access/view.ts

Tests: passed: suite/protected-access.test.ts the tables are in their schema and a view of each is in the other, read through on every e

Cited by: Data, sql/extensions/protected-access/view

FR-003checkpassing

A view MUST be made by dropping it and making it again, together with its comment where the dialect has COMMENT ON (the table's description), as one unit that the lifecycle applies again when its text changes or a step changes the table; on PostgreSQL it MUST be a security barrier.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: the tables are in their schema and a view of each is in the other, read through on every engine, with the comment, and no view for a table of an extension

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/source.ts, data/sql/extensions/protected-access/view.ts

Tests: passed: suite/protected-access.test.ts the tables are in their schema and a view of each is in the other, read through on every e

Writing through the views

FR-004checkpassing

A row MUST be able to be inserted, updated and deleted through the view on PostgreSQL (the view is updatable) and on SQLite (instead-of triggers that the layer makes), and on DuckDB, whose views are read-only, the layer MUST say so and make no trigger.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: a row is inserted, updated and deleted through the view on PostgreSQL and SQLite, and DuckDB's views are read-only and say so

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/view.ts

Tests: passed: suite/protected-access.test.ts a row is inserted, updated and deleted through the view on PostgreSQL and SQLite, and Duck

Enforcement

FR-005checkpassing

Where the layer is enforced, the roles security.access names MUST be granted on the views only (SELECT for those that read, INSERT, UPDATE and DELETE for those that write) and USAGE on the schema of the views; every right on the schema of the tables and on its tables MUST be taken from PUBLIC; and a table that has a scope MUST have row-level security forced, with one policy that does not name a role, so that the rows a role sees through a view are those of its scope.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: on PostgreSQL a role that owns nothing sees through the view only the rows of its scope and cannot read the table, a writer writes only in its scope, and the guard refuses a change of a table outside a migration

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/guard.ts, data/sql/extensions/protected-access/source.ts, data/sql/extensions/protected-access/view.ts

Tests: passed: suite/protected-access.test.ts on PostgreSQL a role that owns nothing sees through the view only the rows of its scope an

Cited by: Data, sql/extensions/protected-access/guard, sql/extensions/protected-access/view

FR-006checkpassing

Where the layer is enforced and the guard is on, the layer MUST make a guard that refuses to alter or drop a table or a column of the schema of the tables unless the session has said that a migration is running, and the lifecycle's run MUST say so for the transactions of its steps (before); a table of another schema MUST be free to change.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: on PostgreSQL a role that owns nothing sees through the view only the rows of its scope and cannot read the table, a writer writes only in its scope, and the guard refuses a change of a table outside a migration

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/guard.ts, data/sql/extensions/protected-access/source.ts

Tests: passed: suite/protected-access.test.ts on PostgreSQL a role that owns nothing sees through the view only the rows of its scope an

FR-007checkpassing

The layer asked for enforcement on a dialect that cannot enforce it (enforced on SQLite or DuckDB) MUST be returned as an error value naming what the dialect lacks; convention MUST make the schemas and the views and none of the grants, the policies or the guard on any dialect.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: enforcement asked for where the dialect cannot is an error value, and convention makes the schemas and views and nothing else

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/guard.ts, data/sql/extensions/protected-access/source.ts

Tests: passed: suite/protected-access.test.ts enforcement asked for where the dialect cannot is an error value, and convention makes the

Open: issue data 00008: The migration flag of the protected layer is a setting any session can set

Cited by: Data, sql/extensions/protected-access/guard

Consumers while tables change

FR-008checkpassing

An attribute that carries lifecycle.renamedFrom MUST stay in its view under the old name as well as the new, for as long as the annotation is there, and MUST leave the view when it is taken out.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: a renamed attribute stays in its view under its old name for as long as governance says so, and leaves it when the annotation is taken out

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/transform.ts, data/sql/extensions/protected-access/view.ts

Tests: passed: suite/protected-access.test.ts a renamed attribute stays in its view under its old name for as long as governance says so

Cited by: Data, sql/extensions/protected-access/view

FR-009checkpassing

The layer's transform of the lifecycle MUST, for a step that renames, alters or drops a table or a column, drop the views of the table before the step's statements and make them again after them, in the same transaction, so that a step is never blocked by the view it changes; the views made again MUST be those of the state after the step.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: a step that changes a table drops its views before and makes them again after, in the same transaction, so a view never blocks the change; the lifecycle gives the transform the states and runs the setting first

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/transform.ts

Tests: passed: suite/protected-access.test.ts a step that changes a table drops its views before and makes them again after, in the same

Cited by: Data, sql/extensions/protected-access/transform, sql/lifecycle/run

FR-010checkpassing

The lifecycle MUST give a transform the states before and after a step, MUST apply as one unit the statements of a group that are made from the same elements, and MUST apply again a unit made from an element a step changed.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: a step that changes a table drops its views before and makes them again after, in the same transaction, so a view never blocks the change; the lifecycle gives the transform the states and runs the setting first

Proved by: data/suite/protected-access.test.ts

Tests: passed: suite/protected-access.test.ts a step that changes a table drops its views before and makes them again after, in the same

Cited by: sql/lifecycle/plan

Masks

FR-011checkpassing

Where the masks are on, a column that carries security.mask MUST be hidden in its view: hash as the hash of its text (md5, on PostgreSQL and DuckDB), redact as a constant for text and null for any other type, partial as its first two characters and ***; on PostgreSQL a role that the settings name MUST see the value as it is; and a mask the dialect cannot write (hash on SQLite) MUST be an error value naming the column.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: what governance says must be hidden is hidden in the view, a role the settings name sees it as it is, and a mask the dialect cannot write is an error value

Proved by: data/suite/protected-access.test.ts

Written in: data/sql/extensions/protected-access/view.ts

Tests: passed: suite/protected-access.test.ts what governance says must be hidden is hidden in the view, a role the settings name sees i

Cited by: Data, sql/extensions/protected-access/view, sql/physical/dialect

What the database holds

FR-012checkpassing

On PostgreSQL with roles that own nothing, a role that governance lets read MUST see through the view only the rows of its scope and MUST NOT be able to read the table; a role that governance lets write MUST be able to insert through the view in its scope and MUST NOT be able to outside it; and the guard MUST refuse ALTER TABLE and DROP TABLE in the schema of the tables outside a migration and allow them in one. Where a project gives a secret that seals the flag (migratingSecret), the guard MUST hold only its hash, a session that says on or guesses MUST NOT be taken for a migration, and one that holds the secret MUST be.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: on PostgreSQL a role that owns nothing sees through the view only the rows of its scope and cannot read the table, a writer writes only in its scope, and the guard refuses a change of a table outside a migration; suite/protected-access.test.ts: with a secret that seals the flag, a session that sets the flag to on is not a migration, and one that holds the secret is

Proved by: data/suite/protected-access.test.ts

Tests: passed: suite/protected-access.test.ts on PostgreSQL a role that owns nothing sees through the view only the rows of its scope an; passed: suite/protected-access.test.ts with a secret that seals the flag, a session that sets the flag to on is not a migration,

FR-013checkpassing

On SQLite, DuckDB and PostgreSQL, the plans from empty and from each version of a domain to the next, run with the layer, MUST leave the tables in their schema, a view of each with the columns of the last version, the reference rows and no table of the views' schema.

Enforced (check): .github: cd commons/data && bun run verify — suite/protected-access.test.ts: the tables are in their schema and a view of each is in the other, read through on every engine, with the comment, and no view for a table of an extension; suite/protected-access.test.ts: the plans of a domain's versions run with the layer leave the tables in their schema, a view of each with the last version's columns, the reference rows, and every step safe to run twice

Proved by: data/suite/protected-access.test.ts

Tests: passed: suite/protected-access.test.ts the tables are in their schema and a view of each is in the other, read through on every e; passed: suite/protected-access.test.ts the plans of a domain's versions run with the layer leave the tables in their schema, a vi

Out of scope

  • Views that leave out the rows marked as removed (Arca's deleted_at): a later extension, on the roadmap.
  • A schema for each version of an API (parallel versions): the layer's views in a schema of their own are what they are made of, and the feature is its own specification.
  • A column that is dropped staying in its view: the safety features (quarantine) make the drop a rename away first.
  • Creating the roles themselves: the layer names roles and never creates them.

Edge cases

  • A concept with security.access and no scope: grants on the view and no policy, per FR-005.
  • A role in security.access of a concept and the schema of the views not granted to it: it is granted USAGE, per FR-005.
  • A renamed attribute whose annotation is then taken out: the next plan makes its view again without the old name, per FR-008.
  • A step that drops a column the view shows: the view is dropped, the column dropped, the view made again without it, per FR-009.
  • SQLite with enforced: an error value, per FR-007.
  • A masked column that is part of the key: shown masked; the key columns of a write are matched on the table's own, per FR-004.
  • No lifecycle.migrating set by a session that alters a table: refused, per FR-006.

Assumptions

  • The guard stops accidents of a privileged session; what stops a role is what it has been granted.
  • A view in PostgreSQL runs with the rights of its owner, and current_user in it is the role that reads.
  • The roles exist.

Open questions

OQ-1open question

Whether the guard may be told, by the lifecycle's run, which role may migrate, so that the flag alone is not enough.

Key entities

  • Protection - the schema of the tables, the schema of the views, the views and, where enforced, the grants, the policies and the guard.
  • View unit - a view with its comment and its triggers, applied together.
  • Shim - the old name of a renamed column, kept in its view.
  • Migration flag - the setting that tells the guard a migration is running.

Success criteria

SC-001success criterion

An application that uses only the views keeps working when tables are renamed, widened or reorganised, and cannot change them.

SC-002success criterion

On an engine that cannot enforce, the layer says so and is a convention, never a promise.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/extensions/access/access, sql/extensions/protected-access/guard, sql/extensions/protected-access/mod, sql/extensions/protected-access/settings, sql/extensions/protected-access/source, sql/extensions/protected-access/transform, sql/extensions/protected-access/view, sql/lifecycle/plan, sql/lifecycle/run, sql/physical/dialect, project cli

Titles, headings, exports, tables, columns and requirements. Esc closes.