October 2, 20265 min read

Generating erun's PostgreSQL Migrations with Ptah Compat

Keep erun's Atlas commands and migration history while generating PostgreSQL triggers, RLS policies, grants, and conditional role declarations.

Simon HartSolutions Engineer

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

erun declares its PostgreSQL schema in SQL, including the functions, triggers, policies, and grants its application needs. Its migration workflow still required manual additions for objects that the pinned Atlas 1.2.0 binary would not generate without login. Those additions had to stay aligned with a separate desired schema.

In our proposed change, Ptah Compat replaces that binary under the existing atlas command name. The 80 SQL source files, 39 migrations, atlas.hcl, and original atlas.sum stay unchanged. The proposal is open; this is a tested integration, not an announcement that erun has adopted it.

We reproduced the migration generation with Ptah Compat 0.11.4 and PostgreSQL 18.6. We also checked the current edge implementation of conditional role creation. That newer capability removes the role-bootstrap limitation still described in the PR. The supporting files contain the source snapshot, generated SQL, and recorded results for both versions.

Keep the command and the history

The proposed database image copies our compatibility binary from the pinned 0.11.4 image into /usr/local/bin/atlas. The API-test and devops images use the same release. The migration command and environment name remain:

atlas migrate apply --env default --url "$DATABASE_URL"

We applied all 39 existing migrations to an empty database. Separately, we loaded the declared SQL in the order listed by atlas.hcl. erun’s catalog comparison found matching columns, constraints, indexes, functions, triggers, RLS settings, policies, and grants.

Before changing the desired schema, we ran:

atlas migrate diff unchanged --env default

The result was an empty diff. No migration file or checksum changed. Replacing the binary did not require a new baseline or a rewritten migration history.

Generate the objects the application depends on

The regression case in the PR adds a temporary ptah_probe table to a copy of the desired schema. Its timestamp trigger calls erun’s existing function. RLS is enabled and forced. A tenant policy scopes rows to the current tenant; an operations policy permits cross-tenant access. Table grants permit reads and inserts, while the tenant’s update grant covers only the name column.

After adding the probe declaration to atlas.hcl’s source list, we generated and applied its migration:

atlas migrate diff generated_objects --env default
atlas migrate apply --env default --url "$DATABASE_URL"

The generated migration contains the table, timestamp trigger, both RLS settings, both policies, table grants, and the column grant. No SQL was added to that migration by hand.

We compared the resulting catalog with the declared schema, then exercised the table through PostgreSQL roles:

Check	Observed result
Insert rows for two tenants	The timestamp trigger populated both timestamps
Read as one tenant	Only that tenant’s row was visible
Insert a row for another tenant	PostgreSQL rejected the write
Update `name` as the tenant	The update succeeded
Update `tenant_id` as the tenant	PostgreSQL rejected the column access
Read as operations	Both rows were visible

A repeated generation then reported:

atlas migrate diff settled --env default

The migration directory is synced with the desired state, no changes to be made

The migration directory and checksum stayed unchanged on that second diff. The catalog comparison remains part of the proposed integration: it can catch an omission in a generator as well as a mistake in a migration.

Edge also reads conditional role declarations

The PR’s role-creation caveat describes 0.11.4. Its existing bootstrap statements remain in migration history, and that version needs an explicit migration when adding a role declared inside the schema’s procedural DO blocks.

Current edge supports that conditional role-bootstrap pattern. Ptah reads the selected CREATE ROLE declarations into the desired schema and generates role DDL before dependent grants. The existing bootstrap migrations still remain unchanged.

We tested an addition to erun’s schema/roles.sql:

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

GRANT USAGE ON SCHEMA public TO erun_auditor;
GRANT SELECT ON audit_events TO erun_auditor;

With the edge binary installed as atlas, the same workflow generated the change:

atlas migrate diff add_auditor --env default
atlas migrate apply --env default --url "$DATABASE_URL"

The generated SQL creates erun_auditor before its grants. PostgreSQL confirmed the result (catalog excerpt):

   rolname    | rolcanlogin | schema_usage | audit_select | audit_insert
--------------+-------------+--------------+--------------+--------------
 erun_auditor | f           | t            | t            | f

The role cannot log in. It has schema usage and table-level SELECT, with no INSERT privilege on audit_events. These grants do not bypass the table’s existing RLS policies; choosing which rows this role may read is a separate policy decision.

All 39 earlier SQL migrations remained byte-identical. A second diff was empty. There was no need to replace the old DO blocks or handwrite the new role’s migration.

This support covers a defined role-bootstrap subset, not arbitrary PL/pgSQL. Ptah interprets supported conditions without executing the desired DO body. Dynamic SQL, loops, and procedural ALTER ROLE or DROP ROLE are refused. Migration replay still executes historical SQL on a disposable server; roles are cluster-wide, so resetting a database is not enough to reset them. The role-bootstrap reference describes the supported forms and replay requirements.

Keep the independent checks

The proposed switch keeps erun’s deployment workflow and its catalog comparison. It adds a regression that asks the generator to create the objects the application actually uses, then checks their behavior and convergence.

The released integration uses 0.11.4. Conditional role generation is the edge addition, so using it requires the newer binary. Both paths preserve the existing SQL migration history.

Put Ptah to work

Example files

← All articles