October 2, 20265 min read

Ptah 0.12.0: Conditional PostgreSQL Roles in Desired SQL

Ptah 0.12.0 reads conditional PostgreSQL role declarations. Catch up on SQL preservation, migration loading, and container updates since 0.11.0.

Nathan BrooksTechnical Writer

Ptah helps you plan, review, and apply database migrations. Try in your browser

Ptah 0.12.0 is available. A PostgreSQL schema can now declare an application role inside a supported conditional DO block. Ptah reads the declaration into its schema model and generates role DDL before dependent grants. Native Ptah and Ptah Compat use the same reader.

This post also brings together the patch releases since our 0.11.0 announcement. Those releases improved PostgreSQL schema handling, kept application prompt text intact, and made every shipped CLI available in the container image. If you are upgrading from 0.11.0, 0.12.0 includes all of them.

Keep conditional role declarations in the desired schema

Application schemas often create a role only if it does not exist. The role then appears in a grant or a row-level security policy. Treating the role’s DO block as opaque SQL leaves the planner without the declaration that the rest of the schema depends on.

In 0.12.0, this file is a desired schema:

DO $bootstrap$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = 'app_reader'
  ) THEN
    CREATE ROLE app_reader NOLOGIN;
  END IF;
END;
$bootstrap$;

CREATE TABLE documents (id bigint PRIMARY KEY, body text NOT NULL);
GRANT SELECT ON TABLE documents TO app_reader;

Save it as roles.sql and render it with the native CLI:

ptah schema render --schema-file roles.sql --dialect postgres

The output contains a role, a table, and a grant. These are the role and grant statements, excerpted from the result:

CREATE ROLE "app_reader" WITH NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION;
GRANT SELECT ON TABLE "documents" TO "app_reader";

Ptah has turned the conditional declaration into ordinary role DDL. It does this without connecting to PostgreSQL or executing the procedural body. Migration generation uses that same model, so a newly declared role can join its dependent grants in a generated migration.

The condition is evaluated against declarations in the desired document. Earlier declarations in the file or an ordered source list establish the roles that later conditions see. Roles already present on a development or production server do not change that interpretation. This keeps the same desired SQL consistent across environments.

The supported language is deliberately bounded: role declarations, BEGIN/END, nested IF/ELSE, and supported existence or Boolean conditions. Dynamic SQL, loops, and unknown statements are refused, including in a branch that would not run. This is not arbitrary PL/pgSQL execution.

Reading desired SQL and replaying migration history are separate operations. Replaying procedural or role effects still requires a disposable server; resetting one database cannot reset cluster-wide roles. Applying role DDL also requires permission to create those roles. The role-bootstrap reference describes the supported forms and execution boundaries.

The erun article demonstrated this capability on an unreleased build. It is now part of the published 0.12.0 release. The example files contain this post’s input and recorded output from the official binary.

SQL formatting must preserve SQL contents

The compatibility sql template helper formats generated SQL for migration files and reports. Before 0.12.0, adding indentation could also add spaces inside a quoted function body, multiline string, or quoted identifier.

That matters even when the function still executes correctly. PostgreSQL stores the body, so a change introduced by formatting can make the next diff propose the same function again. A multiline string can also hold application data whose spaces are significant.

The formatter now indents the surrounding SQL while preserving quoted contents. We verified the change with Scooter’s notification function and triggers: a function-body and trigger-condition change generates a migration, the migration replays, and the next diff is empty. This fixes formatting; it does not weaken routine comparison or require a custom output template.

What arrived between 0.11.0 and 0.12.0

The intervening patches did not get separate release posts. These are the changes most likely to affect an existing project:

Release	What changed	What it means for your project
0.11.1	PostgreSQL foreign-key compatibility, current-time defaults, SQL NULL defaults, and routine type modifiers; MySQL and MariaDB primary-key comments in SQL inspection.	Keep valid PostgreSQL declarations as written and avoid repeated routine replacement caused by type modifiers PostgreSQL discards. SQL export retains primary-key comments.
0.11.2	Container images gained `ptah-compat` and `ptah-ls` alongside `ptah`.	A migration image can copy the compatibility binary from the published image instead of downloading a separate archive.
0.11.3	Migration loading preserves literal prompt placeholders such as `{{DATE}}` and `{{ ... }}`.	Application prompt text no longer triggers accidental Go-template parsing. Actual migration template actions continue to render.
0.11.4	Ordered SQL source lists, PostgreSQL GRANT lists and column privileges, identity-column nullability, and the MIT license in container images.	Split desired SQL can retain statement order, privileges keep their object-role pairs, and identity columns no longer produce an incorrect `DROP NOT NULL`.

The PostgreSQL fixes in 0.11.1 removed the SQL rewrites needed for our HCI handover. In 0.11.4, we generated migrations from erun’s existing SQL layout, including triggers, row-level security policies, and grants. The current release adds conditional role declarations to that workflow.

The primary-key comment fix also resolves the MySQL inspection issue noted in the 0.11.0 release post. The 0.11.1 documentation update retains older published versions, with links to the current documentation.

Upgrade to 0.12.0

Use the installation guide or the images ghcr.io/stokaro/ptah:0.12.0 and stokaro/ptah:0.12.0. Both images ship ptah, ptah-compat, and ptah-ls; the default entry point is ptah. The MIT license is at /usr/share/licenses/ptah/LICENSE.

Review a generated plan before applying it. In particular, if you previously widened a column to work around the old foreign-key check, restoring its original type can narrow the allowed range. Existing values must fit.

The release notes and full change list since 0.11.0 cover the remaining dependency and maintenance updates. To try schema commands without installing Ptah, open the playground.

Put Ptah to work

Example files

← All articles