How do I set default privileges for a PostgreSQL object?

In this guide, we will learn how to set default privileges for a PostgreSQL object using Pulumi. Default privileges specify the default access permissions for newly created objects within a PostgreSQL schema. This is useful for ensuring that new objects automatically have the correct permissions without needing to manually set them each time.

Below is a TypeScript program using Pulumi to set default privileges for a PostgreSQL object.

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

// Define the PostgreSQL role
const role = new postgresql.Role("exampleRole", {
    name: "example_role",
    login: true,
    password: "examplepassword",
});

// Define the PostgreSQL database
const database = new postgresql.Database("exampleDatabase", {
    name: "example_db",
    owner: role.name,
    encoding: "UTF8",
    lcCollate: "en_US.UTF-8",
    lcCtype: "en_US.UTF-8",
    template: "template0",
});

// Set default privileges for the PostgreSQL role
const defaultPrivileges = new postgresql.DefaultPrivileges("exampleDefaultPrivileges", {
    role: role.name,
    owner: role.name,
    database: database.name,
    objectType: "table",
    privileges: ["INSERT", "SELECT", "UPDATE", "DELETE"],
    withGrantOption: false,
});

// Export the database and role names
export const dbName = database.name;
export const roleName = role.name;

Key Points

• We defined a PostgreSQL role named example_role with login privileges.
• We created a PostgreSQL database named example_db owned by the role.
• We set default privileges for the role on the database to automatically grant INSERT, SELECT, UPDATE, and DELETE permissions on new tables.

Summary

In this guide, we set up default privileges for a PostgreSQL role using Pulumi. We created a role and a database, and then specified default privileges for the role on the database. This ensures that new objects in the database automatically have the correct permissions.