Reworking changes and dependencies

[Henri Maurer]

Hello!

I just picked up Sqitch and I have three unrelated questions.

1. My project is using PostgreSQL grants and row-level security to manage permissions. It seemed to me that it would be a good idea to keep all grants and row-level security policies definitions in a single sql file (so I can see what the permission model is with one glance of an eye, instead of spreading it across a bunch of changes). The way I see this working with Sqitch would be to make this file idempotent (by revoking all grants and dropping all policies at the beginning of the script) so I can use Sqitch rework to update permissions at a later point. Roughly speaking, my workflow would be:

• Have deploy/permissions.sql (idempotent file which drops all grants and policies, then creates grants and policies).
• Have revert/permissions.sql which drops all grants and policies
• Edit those and "sqitch rebase" during development. If I add a new table during development (through a change), move "permissions" in the plan so it's the last line.
• When deploying, tag the release (e.g. @v1), then use "sqitch rework" to edit permissions in the next dev cycle.

First of all, is it a good idea to handle permissions this way? (I am a bit of a Postgres noob as well).

And second, is that the "proper" way to use Sqitch in this situation?

2. I don't quite understand the purpose of Sqitch dependencies. It seems to me that all Sqitch cares about is the plan, which specifies a total order on the changes. In that context, I fail to see what specifying dependencies between changes does.

3. On workflow, am I correct in thinking of Sqitch as "edit deploy/revert files during development up to the latest release tag"? As in, release tags form boundaries below which changes should not be edited. So roughly speaking, the development workflow should be "sqitch rebase --onto @latest_deployment"

Thanks for this great project!

H.

[Rod Taylor]

On Sunday, 27 May 2018 12:24:28 UTC-4, Henri Maurer wrote:

Hello!

I just picked up Sqitch and I have three unrelated questions.

1. My project is using PostgreSQL grants and row-level security to manage permissions. It seemed to me that it would be a good idea to keep all grants and row-level security policies definitions in a single sql file (so I can see what the permission model is with one glance of an eye, instead of spreading it across a bunch of changes). The way I see this working with Sqitch would be to make this file idempotent (by revoking all grants and dropping all policies at the beginning of the script) so I can use Sqitch rework to update permissions at a later point. Roughly speaking, my workflow would be:

I had that thought as well but it makes reworks challenging. In a single change-set I might add a column to table A, rework permissions, add a column to table B, rework permission (FAIL: need to manually edit the plan to move the rework later). Yuck.

Worse still is removing a column. You remove the column, rework the permission file BUT rollback runs the permission grants first THEN recreates the column (rename it to deprecated_col so you can rename it back; do actual removal some time later).

What I ended up with is each table (including different views using only that table for calculated columns) including it's permissions having it's own file. There is a DDL section at the top which changes table structure and a Permissions section afterward which sets permissions including any functions needed for row level controls.

BEGIN

-- DDL, which makes some assumptions about the existing state of the DB

ALTER TABLE ... ADD...

ALTER TABLE ... RENAME col TO deprecated_col;

-- IDEMPOTENT STUFF

REVOKE ALL;

GRANT;

COMMENT;

ENABLE RLS;

DROP POLICY;

CREATE POLICY;

COMMIT;

 

2. I don't quite understand the purpose of Sqitch dependencies. It seems to me that all Sqitch cares about is the plan, which specifies a total order on the changes. In that context, I fail to see what specifying dependencies between changes does.

Dependencies are helpful if you have multiple feature branches being merged into the master branch simultaneously. It ensure the plan throws an error if the plan lines merge in the wrong order. Parallel development across a team is the same motivation for avoiding versioning for migrations as well.

If you're working by yourself and don't have long-lived feature branches it probably isn't a helpful feature.

3. On workflow, am I correct in thinking of Sqitch as "edit deploy/revert files during development up to the latest release tag"? As in, release tags form boundaries below which changes should not be edited. So roughly speaking, the development workflow should be "sqitch rebase --onto @latest_deployment"

That's what I do though rebase by default goes all the way up to the start by default.

I wrote a quick wrapper that takes a rebase tag but defaults to the most recent tag: `sqitch tag | tail -n 1`

Also, I make sure sqitch tags match my git repo tags with both getting stamped at the same time (stamp sqitch, commit, stamp repo, ...)

[David E. Wheeler]

On May 27, 2018, at 17:52, Rod Taylor <rod.t...@gmail.com> wrote:

> What I ended up with is each table (including different views using only that table for calculated columns) including it's permissions having it's own file. There is a DDL section at the top which changes table structure and a Permissions section afterward which sets permissions including any functions needed for row level controls.

This is what I do, too, except that views get their own files.

> Dependencies are helpful if you have multiple feature branches being merged into the master branch simultaneously. It ensure the plan throws an error if the plan lines merge in the wrong order. Parallel development across a team is the same motivation for avoiding versioning for migrations as well.
>
> If you're working by yourself and don't have long-lived feature branches it probably isn't a helpful feature.

They really come into their own if you make cross-project dependencies. That is, project foo’s yup change can depend on change widgets in project bar:

yup [bar:widgets] …

So this will fail if the bar Sqitch project is not already deployed, or its widgets change hasn’t been deployed.

> 3. On workflow, am I correct in thinking of Sqitch as "edit deploy/revert files during development up to the latest release tag"? As in, release tags form boundaries below which changes should not be edited. So roughly speaking, the development workflow should be "sqitch rebase --onto @latest_deployment"
>
> That's what I do though rebase by default goes all the way up to the start by default.

Yes, exactly right.

> I wrote a quick wrapper that takes a rebase tag but defaults to the most recent tag: `sqitch tag | tail -n 1`

Hrm. Might be helpful to develop some sort of syntax for the most recent tag.

> Also, I make sure sqitch tags match my git repo tags with both getting stamped at the same time (stamp sqitch, commit, stamp repo, …)

Yeah, I try to do this, too. I always planned to make it so that Sqitch could integrate more tightly with VCSes, but it was never a priority at work, so I never got around to it. One idea, for example, was to bootstrap a Sqitch plan from an existing repo by reading the history and writing changes for past commits, tags for past tags, etc. Kind of a special case, though.

D