DSQL

Serverless Postgres on Aurora DSQL. IAM auth over TLS, schemas and roles declared in code, and optional multi-region clusters.

Database · anvil:aws:DSQL

Database · Deep dive

DSQL Schema & Role Bootstrap

How the schemas you declare on DSQL become real Postgres DDL — without a database credential ever touching your deploy pipeline.

Schemas, roles, and tables in DSQL

Aurora DSQL is Postgres-compatible, but it isn't vanilla Postgres — a few of its own rules shape exactly how the schemas argument is structured and validated.

Schema

A schema is the same namespace concept as in Postgres — it groups tables as schema.table and is the unit DSQL uses for permissions: you grant a role USAGE on a schema, then privileges on the tables inside it. DSQL reserves the public schema for the built-in admin role and caps the rest at 10 schemas per database — both reasons Anvil's schemas array rejects a schema literally named "public" and refuses a deploy past 10 distinct schema names before it ever reaches AWS.

Role

A database role in DSQL is an ordinary Postgres role — created once with CREATE ROLE ... WITH LOGIN — that becomes usable only after it's explicitly mapped to an IAM identity via AWS IAM GRANT <role> TO 'arn:...'. IAM handles authentication (who can open a connection); the Postgres role handles authorization (what that connection can do in SQL). DSQL ships exactly one predefined, unmodifiable role — admin, authorized via dsql:DbConnectAdmin — which owns the public schema and can grant itself any other role. Every role under schemas[].roles[] is one you declare; each becomes a CREATE ROLE plus its GRANT statements, and each target you .grantConnect() connect access to becomes one more AWS IAM GRANT mapping on top of it.

Table

Tables are regular Postgres tables living inside one of your schemas, capped at 1,000 per database. One DSQL-specific wrinkle shapes the Lambda's cleanup logic: DSQL doesn't support DROP OWNED BY, which would normally matter because dropping a role that owns objects fails. Anvil sidesteps the problem entirely — the bootstrap Lambda always connects as admin to create tables, so the app roles declared in schemas[].roles[] never own anything, and a plain DROP ROLE is always sufficient. The other DSQL-specific rule worth knowing: index builds must use CREATE INDEX ASYNC rather than plain CREATE INDEX, which is why every index declared under schemas[].tables[].indexes compiles to the async form.

Why the indirection

Pulumi could, in principle, connect to the cluster and run SQL directly at deploy time. Anvil deliberately doesn't do that. Instead, declaring schemas on a DSQL component wires up:

1. A DynamoDB table with streams enabled — PAY_PER_REQUEST, deletion-protected, point-in-time recovery on.
2. A bootstrap Lambda, prebuilt and embedded in the provider binary — you never write or see its source as a user.
3. An event source mapping from that table's stream to the Lambda.
4. DynamoDB items — one per table you declared, one per role you declared — written directly by Pulumi.

Pulumi's job stops at writing an item to DynamoDB. The stream fires, and only the Lambda ever runs SQL:

The DynamoDB item shapes

One table, composite key roleName (PK) / sk (SK). Three item "kinds", distinguished by shape rather than a clean type field:

Table items carry the full table definition as a JSON string in a definition attribute. Role items carry schema, grants (a list), and optionally tableScoping. IAM mapping items carry nothing but the ARN — role name + ARN is all the Lambda needs.

Ordering is enforced through Pulumi dependencies: table items are created before role items, and any table-scoped role item depends on all table items — so the stream always delivers "create the table" before "grant on that table."

What the Lambda does with each event

The Lambda dispatches on the record shape, then on INSERT / MODIFY / REMOVE:

Table items

• INSERT / MODIFY, table missing — CREATE SCHEMA if needed, CREATE TABLE, then CREATE INDEX ASYNC for each declared index (DSQL builds it in the background), then grants to any role scoped to the table.
• INSERT / MODIFY, table exists — compares the definition with the real catalog, not with the item's previous version, and only adds what's missing (ALTER TABLE ... ADD COLUMN IF NOT EXISTS, new async indexes). A column added to an existing table can't have NOT NULL or DEFAULT, because DSQL's ADD COLUMN takes only a name and a type, so it's rejected with that reason. A column whose type or nullability differs from the code is left alone and reported as drift. Removed columns and indexes are ignored.
• REMOVE — no-op. Dropping Pulumi state never drops the actual table.

Role items

• INSERT — CREATE SCHEMA if needed, then a conditional CREATE ROLE (DSQL has no CREATE ROLE IF NOT EXISTS, so it checks pg_roles first), tagged with the comment managed-by:anvil, then the GRANT statements for that role. A scoped table that doesn't exist yet is skipped; its own table item grants it once created, so the order the stream delivers items in doesn't matter.
• MODIFY — revoke-all-then-re-grant: best-effort revoke of everything the role had on the old schema (failures here are swallowed — the old schema may already be gone), then apply the new grant spec. No per-privilege diffing.
• REMOVE — AWS IAM REVOKE for every IAM mapping the role still has (DSQL won't drop a role with one), revokes in every schema, then DROP ROLE IF EXISTS. This never returns an error: a failed REMOVE would poison the stream and block every record queued behind it.

After any role change the Lambda also sweeps the database's roles. A role tagged managed-by:anvil that's no longer in code is dropped the same way. A role without the tag, made by hand or by another tool, is reported when you deploy, or dropped too when the component sets pruneUnmanagedRoles: true. admin and dbowner are never touched.

Grant scope depends on whether the role declared tableScoping:

-- schema-wide (no tableScoping): covers current + future tables
GRANT USAGE ON SCHEMA app TO app_role;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA app TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT ON TABLES TO app_role;

-- table-scoped: only the named tables, no default-privilege coverage
GRANT USAGE ON SCHEMA app TO reporting_role;
GRANT SELECT ON app.orders TO reporting_role;

IAM mapping items

Literally AWS IAM GRANT "<role>" TO 'arn:...' on insert and AWS IAM REVOKE ... FROM ... on remove — DSQL's own non-standard SQL extension for mapping an IAM principal to a Postgres role.

Status items

After acting on an item, the Lambda writes a status next to it in the same table: the same roleName, sk set to STATUS# plus the item's sort key, and applied, drift or failed with a message. Each item carries a version (a hash of its contents) that the status copies, so anvil deploy can wait until every item has a status for its current version, print the results, and fail when one failed. The Lambda ignores status records on the stream, so writing them never triggers more work. The sweep's result is the status of the component's settings item.

A conflict DSQL reports as retryable (SQLSTATE 40001) is retried inside the Lambda with backoff. A change DSQL rejects outright is recorded as failed and the stream moves on; connection and other passing errors are retried by the stream.

Every statement is built with identifiers quoted to prevent injection, and the Lambda connects using a fresh IAM dsql:DbConnectAdmin auth token generated per invocation — never a stored password.

The runtime connection path

This is what .grantConnect() sets up for you, and what a Lambda granted connect access actually does at request time:

1. Generate a dsql:DbConnect auth token (not the admin one).
2. Connect with user=<db role>, password=<token>, port 5432, TLS.
3. DSQL checks: does this IAM identity have dsql:DbConnect? (the IAM policy)
4. DSQL checks: is this IAM identity mapped to that role? (the AWS IAM GRANT mapping)
5. Connection is established with that role's privileges — nothing more.

If the DSQL component was built with vpc set and no NAT, granting connect access also wires the egress rule from the target's security group to each regional VPC endpoint's security group on port 5432 — automatically, as part of the same grant call.

DSQL limits Anvil follows

Anvil sticks to what Aurora DSQL supports and doesn't build workarounds for what it doesn't. A workaround would hide a real difference from PostgreSQL, so when DSQL can't do something the deploy fails with the reason instead. When DSQL adds support, Anvil removes the check.

• Adding a column to an existing table takes only a name and a type. DSQL's ALTER TABLE ADD COLUMN rejects NOT NULL and DEFAULT, and has no SET NOT NULL, so a column added later can't have notNull or default. Declare them when the table is first created, or add the column as nullable. Anvil doesn't backfill or emulate them with check constraints.
• Changing an existing column (type, nullability, default) isn't done by Anvil. A difference between your code and the database is reported as drift when you deploy.
• Indexes are built with CREATE INDEX ASYNC, so a new index isn't usable straight after the deploy. Its progress is in sys.jobs.
• One DDL statement per transaction, so each schema, table, role and grant change is applied on its own, and DSQL may ask for a retry when two land at once.
• Column types are limited to the ones DSQL supports; arrays, enums, serial, money and xml are rejected at deploy.
• The public schema belongs to admin and can't be declared, and role names are database-wide, so each must be unique across schemas.

Other constraints worth knowing

• Max 10 schemas per DSQL database, validated before any of this fires.
• One roles table and one bootstrap Lambda per DSQL component, not shared — two DSQL components in the same stack are fully isolated from each other's bootstrap.
• Pulumi's own diffing means unchanged roles/tables are skipped entirely — no API calls, no stream events, no Lambda invocations.

See also

• DSQL — the component that owns the cluster, the roles table, and the bootstrap Lambda.
• DSQLConnect — the grant component that maps an IAM identity to a database role.
• Grants — the general .grant() pattern this builds on.