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"
}
);
