1. Answers
  2. Managing PostgreSQL schema permissions using Pulumi

How do I set schema ownership and permissions in PostgreSQL using Pulumi?

In this guide, we will demonstrate how to set schema ownership and permissions in a PostgreSQL database using Pulumi. We will create a PostgreSQL database, a schema within that database, and then assign ownership and permissions to a specific role.

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

// Create a PostgreSQL database
const myDatabase = new postgresql.Database("myDatabase", {
    name: "mydatabase",
    owner: "dbadmin",
    encoding: "UTF8",
    lcCollate: "en_US.UTF-8",
    lcCtype: "en_US.UTF-8",
    template: "template0",
});

// Create a PostgreSQL role
const myRole = new postgresql.Role("myRole", {
    name: "myrole",
    login: true,
    password: "password123",
    superuser: false,
    createDatabase: false,
    createRole: false,
    inherit: true,
    replication: false,
    bypassRowLevelSecurity: false,
});

// Create a schema and assign ownership to the role
const mySchema = new postgresql.Schema("mySchema", {
    name: "myschema",
    owner: myRole.name,
    database: myDatabase.name,
    ifNotExists: true,
});

// Grant permissions to the role on the schema
const schemaPermissions = new postgresql.Grant("schemaPermissions", {
    role: myRole.name,
    database: myDatabase.name,
    schema: mySchema.name,
    objectType: "schema",
    privileges: ["USAGE", "CREATE"],
    withGrantOption: false,
});

Key Points

  • We created a PostgreSQL database using the postgresql.Database resource.
  • We defined a PostgreSQL role with the postgresql.Role resource.
  • We created a schema within the database and assigned ownership to the role using the postgresql.Schema resource.
  • We granted specific permissions to the role on the schema using the postgresql.Grant resource.

Summary

In this guide, we demonstrated how to manage PostgreSQL schema ownership and permissions using Pulumi. We created a database, a role, a schema, and assigned ownership and permissions to the role. This allows for fine-grained control over database access and operations.

Deploy this code

Want to deploy this code? Sign up for a free Pulumi account to deploy in a few clicks.

Sign up

New to Pulumi?

Want to deploy this code? Sign up with Pulumi to deploy in a few clicks.

Sign up