feat: add pg_partman lifecycle integration#1461
Open
btlqql wants to merge 14 commits into
Open
Conversation
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Assisted-by: OpenAI:GPT-5
Contributor
📝 WalkthroughWalkthroughAdds a containerized IvorySQL/pg_partman integration with environment configuration, lifecycle SQL generation, partition planning and auditing, verification scripts, unit tests, documentation, and GitHub Actions validation. Changespg_partman lifecycle integration
Estimated code review effort: 4 (Complex) | ~60 minutes Sequence Diagram(s)sequenceDiagram
participant Makefile
participant verify-lifecycle
participant partition_harness
participant IvorySQL
Makefile->>verify-lifecycle: start lifecycle verification
verify-lifecycle->>partition_harness: run lifecycle commands
partition_harness->>IvorySQL: install, load workload, maintain, and inventory
IvorySQL-->>partition_harness: return inventory and command results
partition_harness-->>verify-lifecycle: return audit status
verify-lifecycle->>IvorySQL: query final row count
IvorySQL-->>verify-lifecycle: return row count
Possibly related issues
🚥 Pre-merge checks | ✅ 4 | ❌ 1❌ Failed checks (1 warning)
✅ Passed checks (4 passed)
✨ Finishing Touches🧪 Generate unit tests (beta)
Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out. Comment |
btlqql
marked this pull request as ready for review
July 22, 2026 06:29
Contributor
There was a problem hiding this comment.
Actionable comments posted: 5
🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
Inline comments:
In @.github/workflows/ecosystem_pgpartman.yml:
- Around line 3-13: Update the workflow triggers in ecosystem_pgpartman.yml so
lifecycle integration runs beyond the temporary agent/pgpartman-lifecycle
branch, including pushes to the maintained target/default branch and the
appropriate pull request path. Preserve the existing path filters and
workflow_dispatch trigger while ensuring the 60-minute job executes after this
PR is merged.
- Around line 38-39: Update the “Check patch whitespace” step to run Git from
the repository root, using git -C "$GITHUB_WORKSPACE" or an equivalent
working-directory override, so the existing pathspecs correctly target the
workflow and pgpartman files.
In `@tools/ecosystem/pgpartman/compose.yaml`:
- Around line 39-40: Update the Compose ports mapping for the IvorySQL service
to bind the host port to loopback by default, while preserving the existing
configurable IVORYSQL_HOST_PORT value and container port 5333. Ensure remote
exposure requires explicit configuration and Compose-network access remains
unchanged.
In `@tools/ecosystem/pgpartman/partition_harness.py`:
- Around line 803-830: Run the maintenance, inventory, and audit command paths
using the least-privilege owner role by adding the existing owner=True option to
their psql calls. Update the maintenance invocation and the shared
inventory/audit invocation, while leaving install as superuser and preserving
workload’s existing owner=True behavior.
In `@tools/ecosystem/pgpartman/scripts/database-entrypoint.sh`:
- Around line 12-22: Remove the global PGPASSWORD export and scope the superuser
password only to invocations within psql_super. Ensure the password remains
available to the gosu/psql command while no longer being inherited by the
long-lived postgres process started by the entrypoint.
🪄 Autofix (Beta)
Fix all unresolved CodeRabbit comments on this PR:
- Push a commit to this branch (recommended)
- Create a new PR with the fixes
ℹ️ Review info
⚙️ Run configuration
Configuration used: Path: .coderabbit.yaml
Review profile: CHILL
Plan: Pro
Run ID: cd443fbe-2d5d-476f-a45e-228d82c660da
📒 Files selected for processing (11)
.github/workflows/ecosystem_pgpartman.ymltools/ecosystem/pgpartman/.env.exampletools/ecosystem/pgpartman/.gitignoretools/ecosystem/pgpartman/Dockerfiletools/ecosystem/pgpartman/Makefiletools/ecosystem/pgpartman/README.mdtools/ecosystem/pgpartman/compose.yamltools/ecosystem/pgpartman/partition_harness.pytools/ecosystem/pgpartman/scripts/database-entrypoint.shtools/ecosystem/pgpartman/scripts/verify-lifecycle.shtools/ecosystem/pgpartman/tests/test_partition_harness.py
Comment on lines
+3
to
+13
| on: | ||
| push: | ||
| branches: [agent/pgpartman-lifecycle] | ||
| paths: | ||
| - tools/ecosystem/pgpartman/** | ||
| - .github/workflows/ecosystem_pgpartman.yml | ||
| pull_request: | ||
| paths: | ||
| - tools/ecosystem/pgpartman/** | ||
| - .github/workflows/ecosystem_pgpartman.yml | ||
| workflow_dispatch: |
Contributor
There was a problem hiding this comment.
🩺 Stability & Availability | 🟠 Major | ⚡ Quick win
Run lifecycle integration beyond the temporary agent branch.
The 60-minute lifecycle job runs only for pushes to agent/pgpartman-lifecycle; it is skipped for every PR and for merges to the default branch. This leaves the live pg_partman contract unvalidated once this PR is merged. Trigger it on the maintained target branch and/or an appropriate PR path.
Also applies to: 41-62
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In @.github/workflows/ecosystem_pgpartman.yml around lines 3 - 13, Update the
workflow triggers in ecosystem_pgpartman.yml so lifecycle integration runs
beyond the temporary agent/pgpartman-lifecycle branch, including pushes to the
maintained target/default branch and the appropriate pull request path. Preserve
the existing path filters and workflow_dispatch trigger while ensuring the
60-minute job executes after this PR is merged.
Comment on lines
+38
to
+39
| - name: Check patch whitespace | ||
| run: git show --check --oneline HEAD -- .github/workflows/ecosystem_pgpartman.yml tools/ecosystem/pgpartman |
Contributor
There was a problem hiding this comment.
📐 Maintainability & Code Quality | 🟡 Minor | ⚡ Quick win
Run the whitespace check from the repository root.
The job default is tools/ecosystem/pgpartman, so these repository-root-relative pathspecs do not select the intended files. Use git -C "$GITHUB_WORKSPACE" or override this step’s working directory.
Proposed fix
- run: git show --check --oneline HEAD -- .github/workflows/ecosystem_pgpartman.yml tools/ecosystem/pgpartman
+ run: git -C "$GITHUB_WORKSPACE" show --check --oneline HEAD -- .github/workflows/ecosystem_pgpartman.yml tools/ecosystem/pgpartman📝 Committable suggestion
‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.
Suggested change
| - name: Check patch whitespace | |
| run: git show --check --oneline HEAD -- .github/workflows/ecosystem_pgpartman.yml tools/ecosystem/pgpartman | |
| - name: Check patch whitespace | |
| run: git -C "$GITHUB_WORKSPACE" show --check --oneline HEAD -- .github/workflows/ecosystem_pgpartman.yml tools/ecosystem/pgpartman |
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In @.github/workflows/ecosystem_pgpartman.yml around lines 38 - 39, Update the
“Check patch whitespace” step to run Git from the repository root, using git -C
"$GITHUB_WORKSPACE" or an equivalent working-directory override, so the existing
pathspecs correctly target the workflow and pgpartman files.
Comment on lines
+39
to
+40
| ports: | ||
| - "${IVORYSQL_HOST_PORT:-5333}:5333" |
Contributor
There was a problem hiding this comment.
🔒 Security & Privacy | 🟠 Major | ⚡ Quick win
Bind the published database port to loopback by default.
This maps IvorySQL to every host interface. Keep remote exposure opt-in; the verifier uses the Compose network.
Proposed fix
ports:
- - "${IVORYSQL_HOST_PORT:-5333}:5333"
+ - "${IVORYSQL_BIND_ADDRESS:-127.0.0.1}:${IVORYSQL_HOST_PORT:-5333}:5333"📝 Committable suggestion
‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.
Suggested change
| ports: | |
| - "${IVORYSQL_HOST_PORT:-5333}:5333" | |
| ports: | |
| - "${IVORYSQL_BIND_ADDRESS:-127.0.0.1}:${IVORYSQL_HOST_PORT:-5333}:5333" |
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In `@tools/ecosystem/pgpartman/compose.yaml` around lines 39 - 40, Update the
Compose ports mapping for the IvorySQL service to bind the host port to loopback
by default, while preserving the existing configurable IVORYSQL_HOST_PORT value
and container port 5333. Ensure remote exposure requires explicit configuration
and Compose-network access remains unchanged.
Comment on lines
+803
to
+830
| elif args.command == "install": | ||
| output = psql(spec, sql=render_install_sql(spec), runner=runner, timeout=900) | ||
| result = {"installed": spec.policy.parent, "output": output.strip()} | ||
| elif args.command == "workload": | ||
| output = psql( | ||
| spec, | ||
| sql=render_workload_sql(spec, args.rows), | ||
| runner=runner, | ||
| owner=True, | ||
| timeout=900, | ||
| ) | ||
| result = {"inserted": args.rows, "output": output.strip()} | ||
| elif args.command == "maintenance": | ||
| output = psql( | ||
| spec, | ||
| sql=render_maintenance_sql(spec), | ||
| runner=runner, | ||
| session_options={"statement_timeout": "15min", "lock_timeout": "5s"}, | ||
| timeout=900, | ||
| ) | ||
| result = {"maintained": spec.policy.parent, "output": output.strip()} | ||
| elif args.command in {"inventory", "audit"}: | ||
| raw = psql(spec, sql=render_inventory_sql(spec), csv_output=True, runner=runner) | ||
| records = parse_inventory(raw) | ||
| problems = audit_inventory(spec, records) | ||
| result = inventory_dict(records, problems) | ||
| if args.command == "audit" and problems: | ||
| raise AuditError("; ".join(problems)) |
Contributor
There was a problem hiding this comment.
🔒 Security & Privacy | 🟠 Major | ⚡ Quick win
Maintenance/inventory/audit run as superuser instead of the least-privilege owner role.
install (Line 804) correctly needs superuser for CREATE EXTENSION/schema authorization. But maintenance (Line 816-822), inventory, and audit (Line 825) all omit owner=True, so they connect as IVORYSQL_SUPERUSER — only workload (Line 811) uses the owner. Since render_install_sql already makes PARTMAN_OWNER the owner of policy.schema/policy.parent and grants it ALL/EXECUTE on the pg_partman schema (Lines 392-396), the owner has everything needed to run run_maintenance_proc, show_partitions, and show_partition_info itself. Routinely running these with superuser credentials contradicts the PR's stated least-privilege design and needlessly widens the blast radius for a frequently-invoked operation.
🔒️ Proposed fix
elif args.command == "maintenance":
output = psql(
spec,
sql=render_maintenance_sql(spec),
runner=runner,
+ owner=True,
session_options={"statement_timeout": "15min", "lock_timeout": "5s"},
timeout=900,
)
result = {"maintained": spec.policy.parent, "output": output.strip()}
elif args.command in {"inventory", "audit"}:
- raw = psql(spec, sql=render_inventory_sql(spec), csv_output=True, runner=runner)
+ raw = psql(spec, sql=render_inventory_sql(spec), csv_output=True, runner=runner, owner=True)📝 Committable suggestion
‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.
Suggested change
| elif args.command == "install": | |
| output = psql(spec, sql=render_install_sql(spec), runner=runner, timeout=900) | |
| result = {"installed": spec.policy.parent, "output": output.strip()} | |
| elif args.command == "workload": | |
| output = psql( | |
| spec, | |
| sql=render_workload_sql(spec, args.rows), | |
| runner=runner, | |
| owner=True, | |
| timeout=900, | |
| ) | |
| result = {"inserted": args.rows, "output": output.strip()} | |
| elif args.command == "maintenance": | |
| output = psql( | |
| spec, | |
| sql=render_maintenance_sql(spec), | |
| runner=runner, | |
| session_options={"statement_timeout": "15min", "lock_timeout": "5s"}, | |
| timeout=900, | |
| ) | |
| result = {"maintained": spec.policy.parent, "output": output.strip()} | |
| elif args.command in {"inventory", "audit"}: | |
| raw = psql(spec, sql=render_inventory_sql(spec), csv_output=True, runner=runner) | |
| records = parse_inventory(raw) | |
| problems = audit_inventory(spec, records) | |
| result = inventory_dict(records, problems) | |
| if args.command == "audit" and problems: | |
| raise AuditError("; ".join(problems)) | |
| elif args.command == "install": | |
| output = psql(spec, sql=render_install_sql(spec), runner=runner, timeout=900) | |
| result = {"installed": spec.policy.parent, "output": output.strip()} | |
| elif args.command == "workload": | |
| output = psql( | |
| spec, | |
| sql=render_workload_sql(spec, args.rows), | |
| runner=runner, | |
| owner=True, | |
| timeout=900, | |
| ) | |
| result = {"inserted": args.rows, "output": output.strip()} | |
| elif args.command == "maintenance": | |
| output = psql( | |
| spec, | |
| sql=render_maintenance_sql(spec), | |
| runner=runner, | |
| owner=True, | |
| session_options={"statement_timeout": "15min", "lock_timeout": "5s"}, | |
| timeout=900, | |
| ) | |
| result = {"maintained": spec.policy.parent, "output": output.strip()} | |
| elif args.command in {"inventory", "audit"}: | |
| raw = psql(spec, sql=render_inventory_sql(spec), csv_output=True, runner=runner, owner=True) | |
| records = parse_inventory(raw) | |
| problems = audit_inventory(spec, records) | |
| result = inventory_dict(records, problems) | |
| if args.command == "audit" and problems: | |
| raise AuditError("; ".join(problems)) |
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In `@tools/ecosystem/pgpartman/partition_harness.py` around lines 803 - 830, Run
the maintenance, inventory, and audit command paths using the least-privilege
owner role by adding the existing owner=True option to their psql calls. Update
the maintenance invocation and the shared inventory/audit invocation, while
leaving install as superuser and preserving workload’s existing owner=True
behavior.
Comment on lines
+12
to
+22
| export PGPASSWORD=$superpass | ||
|
|
||
| die() { | ||
| printf 'database-entrypoint: %s\n' "$*" >&2 | ||
| exit 1 | ||
| } | ||
|
|
||
| psql_super() { | ||
| gosu ivorysql psql --no-psqlrc --set ON_ERROR_STOP=1 \ | ||
| --host 127.0.0.1 --port "$port" --username "$superuser" --dbname postgres "$@" | ||
| } |
Contributor
There was a problem hiding this comment.
🔒 Security & Privacy | 🟡 Minor | ⚡ Quick win
Do not pass the superuser password to the long-lived server process.
PGPASSWORD remains exported when Line 66 execs postgres. Scope it to psql_super instead.
Proposed fix
-export PGPASSWORD=$superpass
-
psql_super() {
- gosu ivorysql psql --no-psqlrc --set ON_ERROR_STOP=1 \
+ PGPASSWORD="$superpass" gosu ivorysql psql --no-psqlrc --set ON_ERROR_STOP=1 \
--host 127.0.0.1 --port "$port" --username "$superuser" --dbname postgres "$@"
}📝 Committable suggestion
‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.
Suggested change
| export PGPASSWORD=$superpass | |
| die() { | |
| printf 'database-entrypoint: %s\n' "$*" >&2 | |
| exit 1 | |
| } | |
| psql_super() { | |
| gosu ivorysql psql --no-psqlrc --set ON_ERROR_STOP=1 \ | |
| --host 127.0.0.1 --port "$port" --username "$superuser" --dbname postgres "$@" | |
| } | |
| die() { | |
| printf 'database-entrypoint: %s\n' "$*" >&2 | |
| exit 1 | |
| } | |
| psql_super() { | |
| PGPASSWORD="$superpass" gosu ivorysql psql --no-psqlrc --set ON_ERROR_STOP=1 \ | |
| --host 127.0.0.1 --port "$port" --username "$superuser" --dbname postgres "$@" | |
| } |
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.
In `@tools/ecosystem/pgpartman/scripts/database-entrypoint.sh` around lines 12 -
22, Remove the global PGPASSWORD export and scope the superuser password only to
invocations within psql_super. Ensure the password remains available to the
gosu/psql command while no longer being inherited by the long-lived postgres
process started by the entrypoint.
Collaborator
|
Thanks for your work. |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
3 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
Adds a pg_partman 5.4.3 partition-lifecycle integration for IvorySQL with least-privilege ownership, maintenance, retention, and independent catalog auditing.
Pain point
IvorySQL lacked a repeatable lifecycle check that detects missing or overlapping ranges, absent future partitions, overdue retention, reversed boundaries, and rows stranded in the default partition.
Implementation
Validation
Refs #1114
Assisted-by: OpenAI:GPT-5
Summary by CodeRabbit
New Features
Documentation
Tests