Reilly Wood

I'm a big fan of Pulumi, and I've previously used it for cloud infrastructure. That's their main selling point; Pulumi's slogan is literally “Program the cloud." Interestingly, Pulumi can also be used to manage on-premise infrastructure, including PostgreSQL databases. Let's dive into the details.

Pulumi basics

Pulumi takes a declarative infrastructure definition and handles provisioning said infrastructure, just like Terraform, AWS CloudFormation, and Azure Resource Manager. The main difference is that in Pulumi you're writing real code (Node, Python, or Go) to build that infrastructure definition instead of a YAML or JSON file, it's great.

Pulumi/Terraform PostgreSQL Provider

After Pulumi has executed your code to build up an infrastructure definition, it needs to interact with external infrastructure resources (cloud providers, databases, etc.) to turn that definition into a reality. Some of Pulumi's functionality for working with external resources is derived from Terraform providers. For example, Pulumi has a PostgreSQL provider which is derived from the Terraform PostgreSQL provider.

At a high level, the PostgreSQL provider lets you define databases, roles, schemas, permissions, and PostgreSQL extensions. It stops short of letting you manage DDL objects and data within the database (which is probably for the best given the complexities of schema+data migrations).

Defining a Postgres database with TypeScript

Pulumi can be used to instantiate entire Postgres clusters, but for this tutorial let's assume you already have an existing cluster managed outside of Pulumi. It can be running anywhere: in the cloud, in a container, on your local machine, whatever.

First, install Pulumi then instantiate a new TypeScript project with pulumi new typescript. This scaffolds a bare-bones Node+TypeScript Pulumi project with the following files:

index.ts                package-lock.json       tsconfig.json
Pulumi.yaml             node_modules            package.json

Install the Pulumi PostgreSQL provider with npm i @pulumi/postgresql (or just edit your package.json directly).

Next, we need to tell Pulumi which Postgres cluster to connect to and how. The PostgreSQL provider's configuration points are documented here. My Postgres cluster is running on a local VM with the hostname fedora-vm, so I set the postgresql:host variable like so:

pulumi config set postgresql:host fedora-vm

This creates a YAML configuration file for the current Pulumi stack, or modifies one if it already exists. For my dev stack, this creates a file named Pulumi.dev.yaml with the following contents:

config:
  postgresql:host: fedora-vm

You'll want to do the same for postgresql:username and postgresql:password, to tell Pulumi which credentials to use. Sensitive configuration values like passwords can be encrypted using the --secret flag.

Finally, we're ready to work with the fun stuff: configuring a database in real TypeScript code. Let's say we want to instantiate a Pulumi-managed database, create a role with login permissions, and grant the role SELECT permission on tables in the public schema in the new database. This can all be done in the index.ts file like so:

import * as pulumi from "@pulumi/pulumi";
import * as postgresql from "@pulumi/postgresql";

const config = new pulumi.Config();

const managedDatabase = new postgresql.Database("managedDatabase", {
  name: "pulumi"
});

const publicReaderRole = new postgresql.Role("publicReaderRole", {
  login: true,
  name: "public_reader",
  password: config.require("publicReaderPassword")
});

const publicReaderSelectTablesGrant = new postgresql.Grant(
  "publicReaderSelectTablesGrant",
  {
    database: managedDatabase.name,
    objectType: "table",
    privileges: ["SELECT"],
    role: publicReaderRole.name,
    schema: "public"
  }
);

It's fairly self-explanatory, but some points of interest:

1. We can retrieve arbitrary configuration values, even encrypted ones, using config.require().
   1. Use config.get() for optional configuration that might not be present in every stack.
2. It's a bit weird that we write names like managedDatabase twice, right?
   1. The names in the constructors are Pulumi resource names, which Pulumi uses to track resources across multiple deployments.
   2. The TypeScript variable names could be anything, Pulumi doesn't use them outside of when our code is run.
   3. It's a bit awkward that we need to repeat ourselves – in most cases I don't have any reason to name Pulumi resources differently from the variables that represent them. Perhaps Pulumi should use reflection to find default resource names, or perhaps I should come up with a better naming convention.

Deployment

Once we have something like the above, deployment is trivial with the pulumi up command. It runs our TypeScript program then provides a nice visual preview of the resources that will be created:

Selecting yes will create the database, role, and grant in just a few seconds. With just a little bit of work, we've defined our core database infrastructure as real code.