Automating the annoying parts of DSQL

It’s been a year and a half since AWS released Aurora DSQL, a serverless relational database. It’s pay-as-you-go: no paying for idle compute when usage is zero.

DynamoDB and DSQL serve different purposes. I’ve seen it many times, and so have plenty of others: people modelling and using DynamoDB as a relational database. They then complain that they don’t like DynamoDB and that it’s not good, when in reality they just tried to eat soup with a fork and then blamed the fork for their own choices.

I was beyond excited to try out DSQL; I enjoyed it. But I never got that out-of-the-box feeling like I did with DynamoDB. I had to do the following manually:

• Connect to the DSQL cluster with an admin connection.
• Create your schema and tables.
• Create a database role.
• Grant the database role access to the schema and tables, such as SELECT, INSERT and UPDATE.
• Link the database role to an IAM role with AWS IAM GRANT.

I quickly noticed this is a bit of a nightmare for a fully serverless application with many Lambdas and multiple environments, think dev, UAT, prod. You have to remember what was deployed with what permissions, and if you forget one, you have to go back and redo it.

Before I explain Anvil’s unique take on DSQL, I need to explain a few concepts. When you deploy a cluster, you get one built-in database, postgres. Schemas separate different domains within your database, like folders. Tables are tables.

A DSQL cluster has one endpoint but two types of connections. Admin connections can do anything: create schemas, tables, and manage roles and permissions. Keep this in mind for later; if misused, they could delete schemas and tables.

Non-admin connections need an IAM role with permission to connect. That role’s ARN is linked to a database role, and an admin must explicitly grant the database role permission to run commands against the schema and tables. Put simply, IAM controls who can connect, and the database admin controls what they can touch.

I set out to create an out-of-the-box, true least-privilege experience for a serverless application that can scale across multiple environments. That means you configure it once in Anvil, and it handles the rest. Remember the admin connection and how it can do whatever? I needed a way to connect to the cluster without exposing access to the cluster or endpoint via the Anvil CLI or a CI/CD pipeline.

I landed on a bootstrapped DynamoDB table with a stream that triggers a bootstrapped Lambda. The table stream is its only trigger, and it acts only on what’s in the table, so calling it directly does nothing, and it’s the only thing that connects to the cluster as admin. When you grant access, Anvil puts an item on the table containing the database role name and your app Lambda’s IAM role ARN. The stream triggers the bootstrapped Lambda, which runs AWS IAM GRANT on the database, so your app Lambda can now log in as that database role.

That was the first iteration: configure once, deploy a million times, and I wouldn’t have to do anything from there. I am never happy. Could I go further? Could I do something truly unique? What if the DSQL component owned the schema itself, creating the tables and isolating each Lambda’s role with explicit, least-privilege grants?

Then came the schemas field on the component: name the schema, define roles for that schema and then allow that role to do certain CRUD actions against the database when connected. You can mix and match the actions with roles. You can have a strictly read-only role, a read/write role, or a role that can’t delete anything. Then you define a fully typed table schema as code.

const db = new DSQL('orders', {
  schemas: [
    {
      name: 'orders',
      roles: [
        { name: 'orders_readwrite', grants: ['SELECT', 'INSERT', 'UPDATE'] },
        { name: 'orders_full', grants: ['SELECT', 'INSERT', 'UPDATE', 'DELETE'] },
      ],
      tables: [
        {
          name: 'orders',
          columns: [
            { name: 'id', type: 'uuid', notNull: true, default: 'gen_random_uuid()' },
            { name: 'customer_id', type: 'uuid', notNull: true },
            { name: 'status', type: 'text', notNull: true, default: "'pending'" },
            { name: 'total', type: 'numeric', precision: 10, scale: 2, notNull: true },
            { name: 'created_at', type: 'timestamptz', notNull: true, default: 'now()' },
          ],
          primaryKey: ['id'],
          indexes: [{ name: 'orders_customer_id_idx', columns: ['customer_id'] }],
        },
      ],
    },
  ],
});

Deploys only write definitions to a table, and a single, narrowly scoped Lambda applies them. Schema changes are additive only. New tables, columns and indexes are applied automatically, but removing a column or table, or changing a column’s type, won’t touch your database. You handle those.

Roles and access are different: they follow your code in both directions. Change a role’s grants and the old privileges are revoked. Remove a role, and it’s dropped. Remove a grantConnect and that Lambda loses access on the next deploy. Your data only ever grows, but access always matches what’s in your code.

Giving a Lambda least-privilege access only requires the following:

db.grantConnect(createOrder, {
  schemas: [{ schema: 'orders', roleName: 'orders_readwrite' }],
});

By default, a role’s grants cover the whole schema, including tables you add later, so a new table doesn’t need a new grant. If you want tighter control, tableScoping limits a role to specific tables:

db.grantConnect(deleteOrder, {
  schemas: [{ schema: 'orders', roleName: 'orders_full', tableScoping: ['orders'] }],
});

Once the schema is defined, Anvil writes the Lambda’s IAM policy (connect to the deployed DSQL cluster only) and maps its IAM role to that database role. The Lambda can connect as that role and nothing more. It all goes through the same DynamoDB stream and bootstrapped Lambda as before; neither the Anvil CLI nor your CI/CD pipeline ever holds admin credentials for the database.

The DynamoDB table becomes the security boundary, and only your deploy writes to it. Even if your pipeline were compromised, an attacker could only write definitions. Every name is quoted, and grants, column types and defaults are checked against an allowlist before any SQL runs, so nothing in the table can make the bootstrapped Lambda run arbitrary SQL as admin.

  Anvil deploy (CLI / CI)
          │
          │  writes item { role: orders_readwrite, arn: …role/createOrder }
          ▼
  Bootstrap table (DynamoDB)
          │
          │  stream event
          ▼
  Bootstrap Lambda            ← only trigger is the stream.
          │
          │  admin connection  ← the only one that exists
          ▼
  Aurora DSQL
    AWS IAM GRANT orders_readwrite TO '…role/createOrder'
          ▲
          │  connects as orders_readwrite (dsql:DbConnect only)
          │
  createOrder Lambda

Another limitation of DSQL was that CDC didn’t come out of the box; it’s bring-your-own target. You need to create a Kinesis stream with the correct permissions and then wire the stream to the cluster using the AWS CLI. This process was a drag for me, the same problem as before. Multiple environments, and you’d need to remember to wire it up in all of them.

In Anvil

const changes = new KinesisStream('order-changes', {
  consumers: [
    {
      consumerName: 'order-events',
      lambda: { arn: orderEvents.arn, lambdaRoleArn: orderEvents.roleArn },
      grantRead: true,
      filter: { tables: ['orders.orders'] },
    },
  ],
});

const db = new DSQL('app', {
  stream: { kinesisArn: changes.arn },
  schemas: [/* ... */],
});

This was another great addition to the Anvil DSQL component: create a Kinesis stream, add it to the table, and it works out of the box. It handles all the permissions and wiring with least-privilege access so that you can build event-driven applications with a relational database after a single deployment.

What started out looking like it would have annoying operational overhead and wouldn't scale across a multi-stage software lifecycle with lots of Lambdas turned into a secure DSQL Anvil component that does the heavy lifting for you so that you can focus on building.

Thanks for taking the time to read this. DSQL is only the beginning; I believe we can bring the same approach to RDS. Governance and speed don’t have to be mutually exclusive. Anvil lets you move fast while keeping structure and safety.

If you have enjoyed this, please star the GitHub repo and start building with Anvil.

As always, it’s your infrastructure, not mine.

Enjoy your safe cloud!

Damien