Table ownership boundaries find a cross-boundary query in the code. They cannot see SQL that is built at run time, or a query in a helper. The database sees every query. Give each boundary its own Postgres role, and the database refuses a query on a table that the boundary does not own.
$ psql "postgres://marketing@db/app" -c 'SELECT * FROM deals'
ERROR: permission denied for table deals
When a boundary works with its own role, a later move to its own database is
a URL change. Each read that it still needs from another boundary is an API
call or a may_read grant, because the database refused all other SQL.
This is opt-in. An app with no role and no [database.roles] keeps one role
and one schema.
1. Give the boundary a schema and a role
Add schema and role to the boundary in boundaries.toml:
# boundaries.toml
[boundaries.core]
owns = ["contacts", "deals"]
crates = ["crm-core"]
role = "core"
[boundaries.marketing]
owns = ["campaigns", "attribution_*"]
crates = ["crm-marketing"]
schema = "marketing"
role = "marketing"
may_read = ["contacts"]
| Key | What it does |
|---|---|
schema | The Postgres schema of the owned tables. When unset, public. |
role | The Postgres role of the boundary. Only a boundary with a role gets grants. |
- An
ownspattern matches a table in the schema of its boundary. A pattern with a.(marketing.campaigns) matches one schema and table. - A table (not a view) in a schema other than
public, that no pattern matches, belongs to the one boundary with that schema. - A view belongs to a boundary only through an
ownspattern. A view runs with the rights of its owner, so it can read tables outside the grant. Give such a viewsecurity_invoker = true(Postgres 15 and later). - An exception pattern (
may_read,may_write) with no.matches the table name in every schema, also in the schema of another boundary. Writepublic.contactsto match one table. - Schema names match without case.
autumn graph check-boundarieshas no schemas. It reads the table part of a pattern with a., sopublic.contactsthere meanscontacts.
2. Put the tables in the schema
A migration that names the schema works as it is:
CREATE SCHEMA IF NOT EXISTS marketing;
CREATE TABLE marketing.campaigns (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL);
An embedded set can go into a schema at boot. Each migration of the set runs
with the schema first on the search_path. The schema is made when it does
not exist. So CREATE TABLE campaigns makes marketing.campaigns:
const MARKETING: EmbeddedMigrations = embed_migrations!("migrations-marketing");
autumn_web::app()
.migrations(MIGRATIONS)
.migrations_in_schema("marketing", MARKETING)
.plugin_migrations_in_schema("my-plugin", "admin", my_plugin::MIGRATIONS)
.run()
.await;
plugin_migrations_in_schema puts the tables of a plugin into the schema of
the boundary that owns them. The plugin SQL does not change.
-
Use it for a plugin that does not register its own migrations. When a plugin registers the same set with
plugin_migrations, the boot stops: one migration is then registered with a schema and without one. -
The plugin code must find its tables. Run it as the boundary role, or put the schema on the
search_pathof the owner role too. -
The role that runs the migrations needs
CREATEon the database. Postgres checks it beforeIF NOT EXISTS, also when the schema exists. -
autumn migraterunsmigrations/only. It does not see an embedded set. Run the embedded sets withAUTUMN_MIGRATE=1, or at boot. -
SQLite has no schemas. A schema set on SQLite stops the boot with an error.
-
autumn_web::migrate::SchemaMigrationsruns a set in a schema withrun_pending, for a test or a tool.
3. Declare the table in its schema
Put the schema on the model. The table! block keeps the module name, so the
model code does not change:
#[model(managed, table = "campaigns", schema = "marketing")]
pub struct Campaign {
#[id]
pub id: i64,
pub name: String,
}
- A schema name is lower case:
[a-z_][a-z0-9_]*. The macro refuses other names. schema = "managed"is the old spelling ofmanaged. A schema cannot have that name.autumn schema diff --write-migrationwritesCREATE SCHEMA IF NOT EXISTS marketing;andCREATE TABLE marketing.campaigns. LaterALTER,CREATE INDEXandDROP INDEXstatements use the same schema.- The sync writes a new model as
marketing.campaigns (id) { ... }insrc/schema.rs. A block that has a schema keeps it when the model names none. - A foreign key to a table of the same schema is written with the schema.
- A foreign key to a table in another schema, such as
auth.users, does not match a managed table with the same name. autumn schema pull --schema marketingreads the schema. Repeat the flag for more schemas.publicis always read. A pull keeps the schemas of the existing snapshot.schema doctorandschema diff --dev-urlread the schemas of the snapshot and the models.
A diesel::table! block can also name the schema without the model argument:
diesel::table! {
marketing.campaigns (id) {
id -> Int8,
name -> Text,
}
}
The width gate (autumn schema width) reads a qualified block too.
Some framework features write raw SQL with the bare table name. The grants
put the schema first on the search_path of the boundary role, so these
features find the table when they run as that role. On the primary pool (a
repository, for example) the owner search_path applies, and a bare
campaigns in marketing is not found. Set it on the owner role too:
ALTER ROLE app_owner SET search_path = "$user", public, marketing;
4. Connect as the role
Each boundary connects with [database.roles.<boundary>] in autumn.toml:
[database]
url = "postgres://app_owner@db/app"
[database.roles.marketing]
user = "marketing" # the host and database of database.url
# password: AUTUMN_DATABASE__ROLES__MARKETING__PASSWORD
[database.roles.core]
url = "postgres://core:secret@db/app"
pool_size = 4
| Key | What it does |
|---|---|
url | A full Postgres URL for the role. |
user | The role name, on the host and database of database.url. |
password | The password for user. The password of database.url is not used. |
pool_size | The pool size. When unset, database.pool_size. |
Set url or user, not both. The name after roles. is the boundary name.
It matches [a-z0-9_-]. With user, a user, password or passfile in
the query of database.url is removed, so the role does not connect as the
owner. user needs database.url: a URL that a pool provider finds at boot
is not used. Set url then.
Each key has an env var: AUTUMN_DATABASE__ROLES__<NAME>__URL, __USER,
__PASSWORD and __POOL_SIZE. <NAME> is the name in upper case, with -
as _. AUTUMN_DATABASE__ROLES=marketing,core declares roles that
autumn.toml does not have.
Code in the boundary gets the pool of its role:
use autumn_web::db_roles::Boundary;
pub struct Marketing;
impl Boundary for Marketing {
const NAME: &'static str = "marketing";
}
// A pool:
let pool = state.pool_for::<Marketing>().expect("marketing pool");
// Or a connection in a handler. `db` is a `Db` that connects as `marketing`.
#[get("/campaigns")]
async fn campaigns(mut db: autumn_web::db::BoundaryDb<Marketing>) -> AutumnResult<String> {
let conn: &mut Db = &mut db;
Ok("ok".to_owned())
}
| Config | pool_for::<Marketing>() |
|---|---|
No [database.roles] | The primary pool. The app has one role. |
[database.roles.marketing] | The pool of marketing. |
Other roles, no marketing | None, and one warning. The boundary does not fall back to the owner role. BoundaryDb answers 503. |
A repository as the role
Bind a repository to a boundary with boundary = Type. The repository then
runs every query as the role:
#[repository(Campaign, boundary = Marketing)]
pub trait CampaignRepository {}
- The extractor uses
pool_for::<Marketing>(). Without[database.roles], that is the owner pool. A wrong key in[database.roles]gives the same result. - With roles, but none for
marketing, the extractor answers 503. It does not use the owner role. - Reads stay on the role pool. A read replica connects as the owner. So a replica read would run with the wrong rights.
boundaryandshardedcannot work together.- The retention sweep of a repository uses the primary pool.
- A
dependent(...)cascade runs the child repository on the pool of the parent. Give the parent and the child the sameboundary.
Each role pool opens its own connections. The boot log adds them to
total_max_connections, and database.max_connections_warn_threshold
counts them. TestApp builds the role pools of its config too.
5. Generate the grants
autumn db grants reads boundaries.toml and the live catalog, and prints
one transaction:
$ autumn db grants
-- Generated by `autumn db grants` from boundaries.toml. Do not edit.
BEGIN;
-- Boundary `marketing`: role "marketing", schema "marketing".
REVOKE ALL ON ALL TABLES IN SCHEMA "marketing", "public" FROM "marketing";
REVOKE ALL ON ALL SEQUENCES IN SCHEMA "marketing", "public" FROM "marketing";
REVOKE ALL ON SCHEMA "marketing", "public" FROM "marketing";
GRANT USAGE ON SCHEMA "marketing", "public" TO "marketing";
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE "marketing"."campaigns" TO "marketing";
GRANT SELECT ON TABLE "public"."contacts" TO "marketing";
GRANT USAGE, SELECT ON SEQUENCE "marketing"."campaigns_id_seq" TO "marketing";
ALTER ROLE "marketing" SET search_path = "marketing", "public";
COMMIT;
- Each role first loses every privilege on every table, sequence and schema.
Then it gets its own tables, its
may_writetables (read and write), and itsmay_readtables (read only). So a grant that the config does not have goes away. - A sequence of an own or
may_writetable getsUSAGE, SELECT, so an insert can use it. This includes a sequence that a column default calls withnextval. - A role in a named schema gets that schema first on its
search_path. A role inpublicgetsRESET search_path, so an old schema goes away. - A schema with no table gets no statement. A
REVOKEon a missing schema would stop the transaction. - The
ownsglobs resolve against the live tables, with the same rules as the graph check. Plugin tables come fromcargo metadata.
Run it after each migration, with the URL that runs the migrations:
autumn migrate
autumn db grants --apply
--apply stops when a role does not exist, and prints the CREATE ROLE
statement. The command does not make roles, because a role has a password.
It also stops when a role is a superuser, is the role that runs the command,
or owns tables. A REVOKE on such a role takes the rights of the owner, or
has no effect.
--json prints the plan per role. --config names another
boundaries.toml. --profile selects the database URL.
A new table that a migration makes has no grant until the next
autumn db grants --apply. So the safe state is the default: the boundary
cannot read it.
6. Check it with doctor
autumn doctor --strict (or --online) connects as each role. It tries
SELECT 1 FROM <table> LIMIT 0 on each table, and reads
has_table_privilege for the other rights:
- Each table of the grant must open.
- Each other table must give
permission denied, and the role must have no other right on it (INSERT,UPDATE, ...). This is the negative check. It finds a grant thatREVOKEcannot remove, for example a grant toPUBLICor through role membership. - A
may_readtable must haveSELECTonly. Amay_writetable must not haveTRUNCATE,REFERENCESorTRIGGER. A trigger on another boundary's table can change that boundary's writes. - The role should not create objects in a schema, and should not be a member of another role.
Each check connection has statement_timeout = 5s and lock_timeout = 2s,
so a migration that locks a table does not stop doctor.
✓ database_roles
marketing: 2 of 2 granted table(s) readable; 3 of 3 other table(s) denied
core: 2 of 2 granted table(s) readable; 2 of 2 other table(s) denied
| Result | Why |
|---|---|
| Fail | A role reads or changes a table outside its grant, has a right on a may_read or may_write table that the grant does not give, or is a member of pg_read_all_data / pg_write_all_data. Doctor names the table or role. |
| Warn | A role cannot connect, connects as another role, cannot read a table of its grant, can create objects in a schema, is a member of another role, or a table cannot be checked. |
| Pass | Every role reads its grant and nothing else. |
Without --strict or --online, doctor does not connect, and does not run
this check.
Connection poolers
Each role is a separate login. A pooler keeps one server pool for each pair of database and user. So each boundary gets its own server pool.
- PgBouncer. Add each role to
auth_file, or useauth_query. Sizedefault_pool_size(or a per-userpool_sizein[users]) for each role.max_db_connectionslimits all roles of one database together. Keepmax_client_connabove the sum of all app pools. - Transaction mode. A
SET search_pathat connect does not hold. The grants useALTER ROLE ... SET search_path. Postgres applies it when the server connection starts, so it holds in every pooler mode. - Open connections.
ALTER ROLE ... SETchanges new sessions only. Apply the grants before the app starts, or recycle the pools (PgBouncerRECONNECT). - RDS Proxy, Supavisor, Neon. Each role needs its own credentials in the pooler. RDS Proxy reads each one from Secrets Manager.
- Migrations and grants. Run
autumn migrateandautumn db grants --applywith a direct URL, as the owner of the tables. See Running behind PgBouncer / RDS Proxy.
Limits
- Postgres only. SQLite has no schemas or roles.
ALTER ROLE ... SET search_pathneeds a superuser orCREATEROLE. From Postgres 16, the role also needsADMIN OPTIONon each boundary role. The role that made the boundary roles has it.REVOKEremoves only grants of the owner. A grant from another role, or toPUBLIC, stays. The doctor check finds it.- Before Postgres 15,
PUBLICcan create objects in schemapublic. Doctor warns. Remove it once:REVOKE CREATE ON SCHEMA public FROM PUBLIC;. - The retention sweep of a repository uses the primary pool. A job runs
its SQL through
BoundaryDborpool_for. - A table name can be in only one schema. The schema tools identify a table by its name only. They refuse a second table with the same name. See ADR 0047.
schema diffdoes not move a table to another schema. It refuses the change. WriteALTER TABLE ... SET SCHEMAby hand.- A model with
schemastopsschema diffon SQLite. autumn migratedoes not run an embeddedmigrations_in_schemaset. Run it at boot, or setAUTUMN_MIGRATE=1.- A role pool uses
database.urlwithuser. The role pool ignores a URL that a pool provider sets at boot. Seturlon[database.roles.<boundary>]. - Framework raw SQL with a bare table name finds the table by the
search_path. On the primary pool, the owner role needs the schema on its path:ALTER ROLE app_owner SET search_path = "$user", public, marketing; - Doctor does not flag a granted view that reads tables outside the grant. A
view belongs to a boundary only through an
ownspattern. Give such a viewsecurity_invoker = true(Postgres 15 and later).
Related
- Table Ownership Boundaries — the static check.
- Running behind PgBouncer / RDS Proxy.
- Migrations.