[BUG] Policy ordering: policy created before referenced table exists

Open
#266 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
45/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Stale
Tech stack
go, postgresql
Domain
databases

Research direction

Start at the migration diff generator and reproduce the issue with the tenant.projects and tenant.project_members schema described here. Trace how policy USING and WITH CHECK references are handled when ordering statements. Done means referenced tables are emitted before the policy, so the generated migration applies successfully.

Written by the indexing model from the issue text.

Description

bug
Describe the bug**

When generating a migration diff, a policy that references another table in its USING clause is ordered before the referenced table is created, causing the migration to fail.

Expected behavior**

The diff generator should analyze policy USING and WITH CHECK clauses for table references and order statements so that:

  1. All tables referenced by a policy are created first
  2. The policy is created after its dependent tables exist

Correct ordering would be:

  1. CREATE TABLE tenant.projects
  2. CREATE TABLE tenant.project_members
  3. CREATE POLICY project_members_org_policy
To Reproduce

Given a schema with:

  1. Table tenant.project_members with a row-level security policy:
CREATE POLICY project_members_org_policy ON tenant.project_members
   AS PERMISSIVE
   FOR ALL
   TO fun_fundament_api
   USING (EXISTS (
     SELECT 1 FROM projects
     WHERE projects.id = project_members.project_id
     AND projects.organization_id = current_setting('app.current_organization_id')::uuid
));
  1. Table tenant.projects (referenced by the policy above)

Observed Behavior

The generated migration orders statements as:

  1. CREATE TABLE tenant.project_members
  2. CREATE POLICY project_members_org_policy (fails - references tenant.projects which doesn't exist yet)
  3. ... other statements ...
  4. CREATE TABLE tenant.projects
Context

pg-schema-diff version: v1.0.5
pg-schema-diff usage: LIBRARY
Postgres version: 18

Dominant language
Go
Stars
884
Forks
82
No merged PRs in 30d

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from stripe/pg-schema-diff

All issues in stripe/pg-schema-diff

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.