pg-schema-diff plan ignores VIEW ACL diffs (both missing GRANT and missing REVOKE)

Open
#271 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

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

Research direction

Start by reproducing the issue with the two pg-schema-diff plan commands and compare privilege handling for the table and regular view in each direction. Trace the planner's ACL-diff path for views; done means the generated SQL includes the expected GRANT and REVOKE SELECT statements for the view as well as the table.

Written by the indexing model from the issue text.

Description

Hi.

pg-schema-diff plan does not emit privilege diffs for regular views.

Environment:

  • pg-schema-diff: 1.0.5
  • PostgreSQL: 18

Repro 1: missing GRANT on view

Source (psd_from):

CREATE ROLE psd_role;

CREATE SCHEMA s;
CREATE TABLE s.t(id int);
CREATE VIEW s.v AS SELECT id FROM s.t;

GRANT USAGE ON SCHEMA s TO psd_role;
-- no SELECT grants on s.t / s.v

Target (psd_to):

CREATE SCHEMA s;
CREATE TABLE s.t(id int);
CREATE VIEW s.v AS SELECT id FROM s.t;

GRANT USAGE ON SCHEMA s TO psd_role;
GRANT SELECT ON s.t TO psd_role;
GRANT SELECT ON s.v TO psd_role;

Command:

pg-schema-diff plan \
  --from-dsn "postgres://postgres:postgres@127.0.0.1:5432/psd_from" \
  --to-dsn "postgres://postgres:postgres@127.0.0.1:5432/psd_to" \
  --temp-db-dsn "postgres://postgres:postgres@127.0.0.1:5432/postgres" \
  --output-format sql \
  --disable-plan-validation

Actual:

  • emits:
GRANT SELECT ON "s"."t" TO "psd_role";
  • does not emit grant for:
GRANT SELECT ON "s"."v" TO "psd_role";

Repro 2: missing REVOKE on view

Now reverse source/target ACLs:

Source (psd_from):

GRANT SELECT ON s.t TO psd_role;
GRANT SELECT ON s.v TO psd_role;

Target (psd_to):

-- no SELECT on s.t / s.v

Run the same plan command.

Actual:

  • emits:
REVOKE SELECT ON "s"."t" FROM "psd_role";
  • does not emit revoke for:
REVOKE SELECT ON "s"."v" FROM "psd_role";

Expected:

  • GRANT/REVOKE SELECT diffs for regular views should be included.
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.