What are PostgreSQL Templates?

Summary

PostgreSQL bases every new database creation on an existing template database within the cluster, defaulting to template1. While administrators can directly modify template1 with tables, data, extensions, or procedural languages, altering it risks breaking future database creation commands if mistakes happen. Setting an existing database as a custom template using the ALTER DATABASE command allows users with the CREATEDB privilege to instantiate customized databases without polluting system templates. Any database creation from a template requires zero active connections on that template at execution time, making pg_dump the preferred tool for replicating active production environments. The immutable template0 system database serves as a fallback to recreate corrupted templates, restore clean dumps, or initialize databases with alternative character encodings.

Context

Directly modifying the default template1 database to pre-configure new databases risks breaking the CREATE DATABASE command if errors occur during manual modifications or database recreation.

Approach / What changed

Configuring custom template databases with ALTER DATABASE template_db_name WITH is_template TRUE isolates custom schemas across multiple use cases, while keeping template0 as an immutable fallback for recovery, clean dumps, and custom encodings.

Takeaways

• Setting is_template to TRUE on an existing database allows any role with the CREATEDB privilege to use it as a template, whereas unflagged databases restrict template usage to superusers or owners.
• The CREATE DATABASE command fails immediately if any active connections exist on the target template database at the start of execution, making pg_dump preferable for live database replication.
• The pristine template0 database must remain unmodified after cluster initialization to enable recovery of corrupted template1 databases and allow database creation with alternative encodings such as SQL_ASCII.

Related reading

• Supabase · Jul 2, 2021

  Protecting reserved roles with PostgreSQL Hooks

  Supabase required granting database customers the CREATEROLE privilege to manage custom roles while preventing them from dropping or altering backend service roles such as supabase_storage_admin. Because stock PostgreSQL lacks a native mechanism to define custom reserved roles, the team created the SupaUtils extension using PostgreSQL hooks. Loaded via shared_preload_libraries, SupaUtils intercepts utility statements by overriding the global ProcessUtility_hook function pointer. The extension inspects incoming statements like ALTER ROLE and DROP ROLE against a configurable list defined in postgresql.conf using DefineCustomStringVariable. If a targeted role matches the configured reserved roles, the hook raises an error, preserving backend infrastructure roles while permitting broader role administration.

  Steve Chavez

• Supabase · Nov 27, 2021

  PostgREST 9

  PostgREST 9 introduces several core updates to the tool that turns PostgreSQL databases into RESTful APIs. Developers can now perform inner joins when embedding tables using the !inner keyword, allowing top-level table rows to be filtered directly by embedded table attributes across multiple operators. The release also adds support for POST requests to functions containing a single unnamed parameter, which simplifies handling raw JSON payloads sent by external webhooks. To ensure compatibility with PostgreSQL 14, custom authentication functions accessing HTTP context headers and JWT claims must be updated to parse JSON properties from consolidated settings. Additional enhancements included in this version provide support for partitioned tables alongside documentation improvements and bug fixes.