@libreworks/db-provision-pgsql

Provision databases and schemas in PostgreSQL along with roles, logins, and grants.

Installation

npm install @libreworks/db-provision-pgsql

This library conforms to ECMAScript Modules (ESM). You can import this module using ESM or TypeScript syntax.

import { Catalog } from "@libreworks/db-provision-pgsql";

If you're using CommonJS, you must use dynamic imports instead.

Usage

You can use this library to perform initialization of a PostgreSQL database server (version 11 and later). For example, creating databases, schemas, roles, users, and grants.

Here is an example to provision several database objects.

import { Login, Role, Catalog } from "@libreworks/db-provision-pgsql";

const username = "example_user";
const password = "🙈";
const owner = new Login(username, password);

const admin = new Role("admin");
const readers = new Role("readers");

const grants = [admin.assignTo(owner)];
const catalog = new Catalog("my_database");
const schema = catalog.createSchema(username, owner);
grants.push(
  catalog.grant(owner, "CONNECT", "TEMP"),
  catalog.grant(readers, "CONNECT", "TEMP"),
  schema.grant(readers, "USAGE"),
  schema.allTables().grant(readers, "SELECT"),
  schema.allSequences().grant(readers, "SELECT"),
  schema.setDefaultTablePrivileges(readers, "SELECT").forCreator(owner),
  schema.setDefaultSequencePrivileges(readers, "SELECT").forCreator(owner)
);

// Display the SQL
const statements = [
  owner,
  admin,
  readers,
  catalog,
  schema,
  ...grants,
].map((v) => v.toSql());
console.log(statements.join(";\n") + ";\n");

The above example outputs the following SQL statements:

CREATE USER "example_user" WITH PASSWORD '🙈';
CREATE ROLE "admin";
CREATE ROLE "readers";
CREATE DATABASE "my_database" ENCODING 'UTF8';
CREATE SCHEMA IF NOT EXISTS "example_user" AUTHORIZATION "example_user";
GRANT "admin" TO "example_user";
GRANT CONNECT, TEMP ON DATABASE "my_database" TO "example_user";
GRANT CONNECT, TEMP ON DATABASE "my_database" TO "readers";
GRANT USAGE ON SCHEMA "example_user" TO "readers";
GRANT SELECT ON ALL TABLES IN SCHEMA "example_user" TO "readers";
GRANT SELECT ON ALL SEQUENCES IN SCHEMA "example_user" TO "readers";
ALTER DEFAULT PRIVILEGES FOR USER "example_user" IN SCHEMA "example_user" GRANT SELECT ON TABLES TO "readers";
ALTER DEFAULT PRIVILEGES FOR USER "example_user" IN SCHEMA "example_user" GRANT SELECT ON SEQUENCES TO "readers";

Because all identifiers are quoted, that means the objects will be created using the same character casing as provided. Without double quotes, PostgreSQL creates objects with lowercase identifiers.

Executing statements

ServerClient and DatabaseClient run these statements against a server. They don't require a superuser, which makes them suitable for managed services like Amazon RDS. Each runs as a different user:

• ServerClient connects to the postgres database as a user with CREATEROLE (and CREATEDB, to create databases). It creates logins, roles, and databases, and grants role memberships.
• DatabaseClient connects to the new database as the schema owner. It grants privileges on the schema.

import { DatabaseClient, ServerClient } from "@libreworks/db-provision-pgsql";

// As the CREATEROLE user, connected to the "postgres" database.
const server = new ServerClient(adminConnection);
await server.createMemberships(admin, [adminLogin]);
await server.createMemberships(readers, [readerLogin]);

// As the schema owner, connected to the new database.
const database = new DatabaseClient(ownerConnection);
await database.createAdminGrants(schema, admin);
await database.createReaderGrants(schema, readers);