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

With no arguments you get one cluster in your stack's region, reached over its public endpoint with IAM auth tokens over TLS — there are no database passwords. Declare schemas to have Anvil create tables and roles, then db.grantConnect(fn, ...) so a Lambda can log in as one of those roles.

Minimal

The smallest config that deploys.

import { App } from '@anvil-cloud/sdk';
import * as anvil from '@anvil-cloud/sdk';

export default new App({
  run(ctx) {
    const db = new anvil.aws.DSQL('db');

    ctx.export('endpoint', db.endpoint);
  },
});

import anvil_cloud as anvil

def infra(ctx: anvil.Context):
    db = anvil.aws.DSQL("db")

    ctx.export("endpoint", db.endpoint)

anvil.App(run=infra)

package main

import (
    "github.com/anvil-cloud/anvil/sdk/go/anvil"
    anvilaws "github.com/anvil-cloud/anvil/sdk/go/anvil/aws"
)

func main() {
    anvil.Run(anvil.AppConfig{
        Run: func(ctx *anvil.Context) error {
            db, err := anvilaws.NewDSQL(ctx.PulumiCtx(), "db", nil)
            if err != nil {
                return err
            }

            ctx.Export("endpoint", db.Endpoint)
            return nil
        },
    })
}

Granting access

Declare a Postgres role in schemas, then grant compute connect access as that role. grantConnect takes an Anvil Lambda (api below). For any other compute, such as an ECS task, an EC2 instance or your own IAM role, create a DSQLConnect with its role ARN, as the Go tab does.

const db = new anvil.aws.DSQL('db', {
  schemas: [{
    name: 'app',
    roles: [{ name: 'app_role', grants: ['SELECT', 'INSERT'] }],
  }],
});

db.grantConnect(api, { schemas: [{ schema: 'app', roleName: 'app_role' }] });

db = anvil.aws.DSQL("db",
    schemas=[{
        "name": "app",
        "roles": [{"name": "app_role", "grants": ["SELECT", "INSERT"]}],
    }],
)

db.grant_connect(api, schemas=[{"schema": "app", "role_name": "app_role"}])

db, err := anvilaws.NewDSQL(ctx.PulumiCtx(), "db", &anvilaws.DSQLArgs{
    Schemas: anvilaws.DSQLSchemaArray{
        anvilaws.DSQLSchemaArgs{
            Name: pulumi.String("app"),
            Roles: anvilaws.DSQLRoleArray{
                anvilaws.DSQLRoleArgs{
                    Name:   pulumi.String("app_role"),
                    Grants: anvilaws.DSQLGrantArray{anvilaws.DSQLGrant("SELECT"), anvilaws.DSQLGrant("INSERT")},
                },
            },
        },
    },
})
if err != nil {
    return err
}

// Go has no grantConnect yet: create the DSQLConnect it would, for any IAM role.
if _, err := anvilaws.NewDSQLConnect(ctx.PulumiCtx(), "db-api-connect", &anvilaws.DSQLConnectArgs{
    ClusterArns:    db.ClusterArns,
    TargetRoleArn:  api.RoleArn,
    TargetName:     pulumi.String("api"),
    DbRoles:        pulumi.StringArray{pulumi.String("app_role")},
    DeclaredRoles:  db.DeclaredRoles,
    RolesTableName: db.RolesTableName,
}); err != nil {
    return err
}

What Anvil creates

• An IAM policy on the compute's role allowing only dsql:DbConnect, on this cluster (every region's cluster for multi-region). Not dsql:DbConnectAdmin, so it can never log in as admin.
• An AWS IAM GRANT mapping that role to app_role and nothing else. Logging in as any other database role fails.
• Inside the database, app_role has only its grants, on its own schema (or just the tableScoping tables). It owns no tables, so it can't alter or drop them.
• Removing the grant deletes the policy and runs AWS IAM REVOKE, so access ends on the next deploy.
• Each { schema, roleName } must be a role declared in schemas, in that schema, or the deploy fails. Roles made by hand in SQL can't be granted, so access is always managed in code.

Good to know

• Connect as the role, not as admin: generate a DbConnect token (not DbConnectAdmin), and use the role name as the user, database postgres, port 5432, with TLS. The Full example tab has the connect code in each language.
• Anvil follows DSQL's own limits instead of working around them. Where DSQL doesn't support something, the deploy fails with the reason rather than Anvil emulating it. The architecture page lists the limits that apply.
• anvil deploy waits for the schema, role and grant changes to reach the database and prints each result. A change DSQL rejects fails the deploy, and a column whose type differs from your code is a warning. Both repeat on every deploy until the code or the database is fixed.
• Removing a table, column or index from schemas doesn't drop it. Drop it yourself in SQL if you want it gone. Removing a role does drop it, after revoking its privileges and IAM mappings.
• Tables are always owned by admin, so your roles never own anything and can always be dropped.
• Encryption at rest is always on with an AWS-owned key at no cost. For your own KMS key, set hatch.cluster.kmsEncryptionKey.
• anvil generate writes typed query helpers from your schemas — see the Codegen tab.