Conversation
Dependency Review✅ No vulnerabilities or license issues or OpenSSF Scorecard issues found.Snapshot WarningsEnsure that dependencies are being submitted on PR branches and consider enabling retry-on-snapshot-warnings. See the documentation for more information and troubleshooting advice. Scanned FilesNone |
…on, devcontainer, and documentation Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/c3703546-e70d-4977-b24a-7fe895f7427a
…ad of version-specific paths Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/c3703546-e70d-4977-b24a-7fe895f7427a
|
@copilot investigate CodeQL / Analyze (java) (pull_request) can we use ubuntu 26.04 pre release or any other way to install postgres 18 in github workflows and setup. ? if it is not possible, change all from postgres 16 to postgres 17 instead, but prefer to use 18 if possible |
…wed-endpoints, align CloudFormation gp2→gp3 Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/fa5240ec-3c91-41fb-a1c4-127bc7710881
The CI failures were caused by Fixed in
|
There was a problem hiding this comment.
Pull request overview
Repository-wide infrastructure/documentation update to move the project’s PostgreSQL baseline from 16 to 18, aligning CI/CD workflows, CloudFormation RDS configuration, devcontainer setup, and assorted documentation references.
Changes:
- Update GitHub Actions workflows to install PostgreSQL 18 via the PGDG apt repository (and adjust hardened-runner egress allowlists).
- Upgrade CloudFormation RDS settings to PostgreSQL 18.3 and align storage settings (gp3).
- Refresh devcontainer, SQL helper scripts, and documentation to reference PostgreSQL 18 and new filesystem paths.
Reviewed changes
Copilot reviewed 59 out of 59 changed files in this pull request and generated 7 comments.
Show a summary per file
| File | Description |
|---|---|
| service.impl/src/test/resources/README-INTEGRATION-TESTS.md | Updates CI/CD integration-test DB version reference to PostgreSQL 18. |
| service.data.impl/src/main/resources/schema-validation.sql | Updates generated-for version comment to PostgreSQL 18. |
| service.data.impl/src/main/resources/schema-validation-v2.sql | Updates generated-for version comment to PostgreSQL 18. |
| service.data.impl/src/main/resources/schema-health-check.sql | Updates generated-for version comment to PostgreSQL 18. |
| service.data.impl/src/main/resources/schema-coverage-analysis.sql | Updates generated-for version comment to PostgreSQL 18. |
| service.data.impl/src/main/resources/db-changelog-1.53-validation-report.md | Updates validation report metadata to PostgreSQL 18. |
| service.data.impl/src/main/resources/SQL_SCRIPTS_REFERENCE.md | Updates schema regen/version compatibility notes for PostgreSQL 18 baseline. |
| service.data.impl/sample-data/framework-validation/validate_views.sql | Updates database version comment to PostgreSQL 18. |
| service.data.impl/sample-data/framework-validation/DATABASE_VIEW_VALIDATION_REPORT.md | Updates report metadata to PostgreSQL 18. |
| service.data.impl/README-SCHEMA-MAINTENANCE.md | Updates schema maintenance guidance, examples, and links for PostgreSQL 18/current docs. |
| network_analysis_performance.sql | Updates analysis script header DB version to PostgreSQL 18. |
| citizen-intelligence-agency/src/site/markdown/index.md | Updates installation/config path references to PostgreSQL 18. |
| cia-dist-cloudformation/src/main/resources/cia-dist-cloudformation.yml | Upgrades RDS parameter group family/engine version to postgres18/18.3 and StorageType to gp3. |
| cia-dist-cloudformation/src/main/resources/cia-dist-cloudformation.json | Upgrades RDS parameter group family/engine version to postgres18/18.3. |
| analyze_temporal_views.py | Updates generated report metadata to PostgreSQL 18. |
| analyze_comparative_views.sh | Updates script header DB version to PostgreSQL 18. |
| WORKFLOWS.md | Updates workflow runtime docs and adds PGDG repository setup snippet. |
| TEMPORAL_VIEWS_OPTIMIZATION.sql | Updates optimization script header DB version to PostgreSQL 18. |
| TEMPORAL_INDEXES_IMPLEMENTATION_REPORT.md | Updates report metadata to PostgreSQL 18.*. |
| TEMPORAL_ANALYSIS_README.md | Updates analysis package metadata to PostgreSQL 18.*. |
| TEMPORAL_ANALYSIS_QUICKSTART.md | Updates PostgreSQL docs URLs to /docs/current/ and log-path references to v18. |
| TEMPORAL_ANALYSIS_PERFORMANCE_REPORT.md | Updates report metadata and troubleshooting log-path references to v18. |
| README_NETWORK_ANALYSIS_PERFORMANCE.md | Updates report navigation metadata to PostgreSQL 18.*. |
| README.md | Updates local install/config paths to PostgreSQL 18. |
| PRIORITY1_INDEXES_IMPLEMENTATION.md | Updates prerequisite DB version to PostgreSQL 18+. |
| PERFORMANCE_REPORT_README.md | Updates report metadata to PostgreSQL 18.*. |
| PERFORMANCE_ANALYSIS_COMPLETION.md | Updates tooling metadata to PostgreSQL 18.*. |
| PATTERN_RECOGNITION_PERFORMANCE_REPORT.md | Updates report metadata to PostgreSQL 18.*. |
| PATTERN_RECOGNITION_OPTIMIZATION_IMPLEMENTATION.md | Updates report metadata and prerequisites to PostgreSQL 18. |
| NETWORK_ANALYSIS_PERFORMANCE_SUMMARY.md | Updates summary metadata to PostgreSQL 18.*. |
| NETWORK_ANALYSIS_PERFORMANCE_REPORT.md | Updates report references/metadata to PostgreSQL 18.*. |
| NETWORK_ANALYSIS_FRAMEWORK_VALIDATION_REPORT.md | Updates validation report DB version to PostgreSQL 18.*. |
| IMPLEMENTATION_GUIDE.md | Updates troubleshooting log-path references to PostgreSQL 18 logs. |
| FUTURE_ARCHITECTURE.md | Updates architecture future-state references to PostgreSQL 18 baseline. |
| End-of-Life-Strategy.md | Updates technology stack entry to PostgreSQL 18. |
| DATA_QUALITY_MONITORING_DASHBOARD.md | Updates monitoring/performance references to PostgreSQL 18. |
| DATA_ANALYSIS_INTOP_OSINT.md | Updates performance metric version references to PostgreSQL 18.*. |
| CRA-ASSESSMENT.md | Updates CRA evidence links/anchors to PostgreSQL 18 config guide references. |
| COMPARATIVE_ANALYSIS_PERFORMANCE_REPORT.md | Updates analysis methodology/testing requirements metadata to PostgreSQL 18.*. |
| .github/workflows/release.yml | Adds PGDG repo, upgrades PostgreSQL packages/config paths, updates harden-runner endpoints. |
| .github/workflows/copilot-setup-steps.yml | Adds PGDG repo, upgrades PostgreSQL packages/config paths. |
| .github/workflows/codeql-analysis.yml | Adds PGDG repo, upgrades PostgreSQL packages/config paths, updates harden-runner endpoints. |
| .github/skills/integration-testing/SKILL.md | Updates Testcontainers example image to postgres:18. |
| .github/skills/classification-policy/SKILL.md | Updates example RDS EngineVersion to 18.3. |
| .github/skills/backup-recovery-policy/SKILL.md | Updates example RDS EngineVersion to 18.3. |
| .github/release-drafter.yml | Updates release template stack summary DB version to PostgreSQL 18. |
| .github/agents/ui-enhancement-specialist.md | Updates environment DB version reference to PostgreSQL 18. |
| .github/agents/task-agent.md | Updates environment DB version reference to PostgreSQL 18. |
| .github/agents/stack-specialist.md | Updates environment DB version references to PostgreSQL 18. |
| .github/agents/marketing-specialist.md | Updates environment DB version reference to PostgreSQL 18. |
| .github/agents/intelligence-operative.md | Updates environment DB version reference to PostgreSQL 18. |
| .github/agents/business-development-specialist.md | Updates environment DB version reference to PostgreSQL 18. |
| .github/agents/README.md | Updates environment DB version reference to PostgreSQL 18. |
| .github/WORKFLOWS.md | Updates workflow environment DB version references to PostgreSQL 18. |
| .github/CACHE_OPTIMIZATION.md | Updates cached package list to PostgreSQL 18 packages. |
| .github/CACHE_IMPROVEMENTS.md | Updates cached package list references to PostgreSQL 18. |
| .devcontainer/init-postgresql.sh | Updates PostgreSQL filesystem paths and status messages to v18. |
| .devcontainer/devcontainer.json | Updates persistent volume mount path to PostgreSQL 18 data directory. |
| .devcontainer/Dockerfile | Upgrades devcontainer PostgreSQL packages to 18 and updates data directory path. |
| @@ -641,18 +641,18 @@ For local or self-hosted deployment on Debian/Ubuntu 24.4+: | |||
|
|
|||
There was a problem hiding this comment.
The text references Debian/Ubuntu 24.4+, but Ubuntu LTS uses the 24.04 version format. This looks like a typo and may confuse users about the supported OS version.
| ```bash | ||
| sudo apt-get install openjdk-21-jdk postgresql-16 | ||
| sudo apt-get install openjdk-21-jdk postgresql-18 | ||
| ``` | ||
|
|
||
| 2. Install PostgreSQL on Ubuntu: | ||
|
|
||
| ```bash | ||
| sudo apt-get install postgresql-16 postgresql-contrib postgresql-16-pgaudit | ||
| sudo apt-get install postgresql-18 postgresql-contrib postgresql-18-pgaudit | ||
| ``` |
There was a problem hiding this comment.
The Ubuntu install commands assume postgresql-18 is available from the default apt repositories, but on Ubuntu 24.04 it typically requires adding the official PGDG apt repository first (as done in the CI workflows/devcontainer). Consider documenting the PGDG repo setup step here so users can actually install these packages.
TEMPORAL_ANALYSIS_README.md
Outdated
|
|
||
| **Analysis Date:** 2026-01-22 | ||
| **Database:** PostgreSQL 16.11 | ||
| **Database:** PostgreSQL 18.11 |
There was a problem hiding this comment.
Several docs now state the environment is PostgreSQL 18.11, but this PR (and the CloudFormation templates) target PostgreSQL 18.3. To avoid conflicting guidance, update this to the actual target version (18.3) or to a non-patch-specific form like PostgreSQL 18.x.
DATA_ANALYSIS_INTOP_OSINT.md
Outdated
| ##### Performance Metrics | ||
|
|
||
| **Query Execution Times** (PostgreSQL 16.10): | ||
| **Query Execution Times** (PostgreSQL 18.10): |
There was a problem hiding this comment.
This section references PostgreSQL 18.10 for the validated query execution times, but the repo upgrade targets PostgreSQL 18.3. Consider aligning the patch version (or using 18.x) so performance baselines and validation details remain consistent with the documented deployment target.
| **Query Execution Times** (PostgreSQL 18.10): | |
| **Query Execution Times** (PostgreSQL 18.3): |
End-of-Life-Strategy.md
Outdated
| | **Core Framework** | [Spring Framework 5.x](https://spring.io/projects/spring-framework) | **August 31, 2024** | | ||
| | **Security** | [Spring Security](https://spring.io/projects/spring-security), [Bouncy Castle](https://www.bouncycastle.org/) | Aligns with Spring 5.x | | ||
| | **Data Access** | [Hibernate](https://hibernate.org/), JPA, [PostgreSQL](https://www.postgresql.org/), JDBC | Hibernate 5.x: Ended; PostgreSQL 16: **Nov 2028** | | ||
| | **Data Access** | [Hibernate](https://hibernate.org/), JPA, [PostgreSQL](https://www.postgresql.org/), JDBC | Hibernate 5.x: Ended; PostgreSQL 18: **Nov 2028** | |
There was a problem hiding this comment.
The EOL date shown for PostgreSQL 18 is still Nov 2028 (the same date previously used for PostgreSQL 16). If the component version is being updated, the corresponding EOL date should also be updated to the correct lifecycle date for PostgreSQL 18 to avoid misleading lifecycle planning.
| | **Data Access** | [Hibernate](https://hibernate.org/), JPA, [PostgreSQL](https://www.postgresql.org/), JDBC | Hibernate 5.x: Ended; PostgreSQL 18: **Nov 2028** | | |
| | **Data Access** | [Hibernate](https://hibernate.org/), JPA, [PostgreSQL](https://www.postgresql.org/), JDBC | Hibernate 5.x: Ended; PostgreSQL 18: **TBD (align with official PostgreSQL lifecycle once released)** | |
| docs.github.com:433 | ||
| dlcdn.apache.org |
There was a problem hiding this comment.
In the harden-runner allowed-endpoints list, docs.github.com:433 uses the wrong TLS port (should be 443). Also dlcdn.apache.org is missing an explicit port, while the rest of the list is host:port; this can lead to unexpected egress blocking if the policy is tightened (or inconsistent audit logs).
| docs.github.com:433 | |
| dlcdn.apache.org | |
| docs.github.com:443 | |
| dlcdn.apache.org:443 |
README.md
Outdated
| 1. Install prerequisites: | ||
| ```bash | ||
| sudo apt-get install openjdk-21-jdk postgresql-16 postgresql-contrib postgresql-16-pgaudit postgresql-16-pgvector | ||
| sudo apt-get install openjdk-21-jdk postgresql-18 postgresql-contrib postgresql-18-pgaudit postgresql-18-pgvector | ||
| ``` |
There was a problem hiding this comment.
The install instructions call apt-get install ... postgresql-18 ... but README.md doesn’t include the PGDG repository setup step. On Ubuntu 24.04, postgresql-18 is not available in the default apt repos, so this command will fail unless users add apt.postgresql.org first (as the workflows/devcontainer now do).
|
@copilot apply changes based on the comments in this thread and check issues in CodeQL / Analyze (java) (pull_request) |
…rt typo, Ubuntu version, PGDG docs, version alignment, EOL date) Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/9e1d0b36-cd87-4f25-9b54-f39d43a9bd8c
|
|
||
| 2. **Environment**: [.github/workflows/copilot-setup-steps.yml](/.github/workflows/copilot-setup-steps.yml) | ||
| - Java 25, Maven 3.9.9, PostgreSQL 16 | ||
| - Java 25, Maven 3.9.9, PostgreSQL 18 |
There was a problem hiding this comment.
The agent docs state the copilot-setup-steps environment uses Java 25, but the referenced workflow configures java-version: 26-ea. Update this line to match the actual Java version used in CI/Copilot setup to avoid misleading instructions.
| - Java 25, Maven 3.9.9, PostgreSQL 18 | |
| - Java 26, Maven 3.9.9, PostgreSQL 18 |
.github/agents/README.md
Outdated
| |------|---------| | ||
| | [README.md](../../README.md) | Project mission, features, quality metrics | | ||
| | [copilot-setup-steps.yml](../workflows/copilot-setup-steps.yml) | Dev environment: Java 25, Maven 3.9.9, PostgreSQL 16, build commands, workflow permissions | | ||
| | [copilot-setup-steps.yml](../workflows/copilot-setup-steps.yml) | Dev environment: Java 25, Maven 3.9.9, PostgreSQL 18, build commands, workflow permissions | |
There was a problem hiding this comment.
This table describes copilot-setup-steps.yml as using Java 25, but the workflow actually sets up java-version: 26-ea. Update the Java version here to keep the agent onboarding docs consistent with the CI/Copilot environment.
| | [copilot-setup-steps.yml](../workflows/copilot-setup-steps.yml) | Dev environment: Java 25, Maven 3.9.9, PostgreSQL 18, build commands, workflow permissions | | |
| | [copilot-setup-steps.yml](../workflows/copilot-setup-steps.yml) | Dev environment: Java 26, Maven 3.9.9, PostgreSQL 18, build commands, workflow permissions | |
|
|
||
| 2. **Environment**: [.github/workflows/copilot-setup-steps.yml](/.github/workflows/copilot-setup-steps.yml) | ||
| - Java 25, Maven 3.9.9, PostgreSQL 16 | ||
| - Java 25, Maven 3.9.9, PostgreSQL 18 |
There was a problem hiding this comment.
The agent docs state the copilot-setup-steps environment uses Java 25, but the referenced workflow configures java-version: 26-ea. Update this line to match the actual Java version used in CI/Copilot setup to avoid misleading instructions.
| - Java 25, Maven 3.9.9, PostgreSQL 18 | |
| - Java 26, Maven 3.9.9, PostgreSQL 18 |
.github/agents/task-agent.md
Outdated
|
|
||
| 2. **Environment**: [.github/workflows/copilot-setup-steps.yml](/.github/workflows/copilot-setup-steps.yml) | ||
| - Java 25, Maven 3.9.9, PostgreSQL 16 | ||
| - Java 25, Maven 3.9.9, PostgreSQL 18 |
There was a problem hiding this comment.
The agent docs state the copilot-setup-steps environment uses Java 25, but the referenced workflow configures java-version: 26-ea. Update this line to match the actual Java version used in CI/Copilot setup to avoid misleading instructions.
| - Java 25, Maven 3.9.9, PostgreSQL 18 | |
| - Java 26, Maven 3.9.9, PostgreSQL 18 |
.github/agents/stack-specialist.md
Outdated
|
|
||
| 2. **Environment**: [.github/workflows/copilot-setup-steps.yml](/.github/workflows/copilot-setup-steps.yml) | ||
| - Java 25, Maven 3.9.9, PostgreSQL 16 | ||
| - Java 25, Maven 3.9.9, PostgreSQL 18 |
There was a problem hiding this comment.
The agent docs state the copilot-setup-steps environment uses Java 25, but the referenced workflow configures java-version: 26-ea. Update this line to match the actual Java version used in CI/Copilot setup to avoid misleading instructions.
| - Java 25, Maven 3.9.9, PostgreSQL 18 | |
| - Java 26-ea, Maven 3.9.9, PostgreSQL 18 |
|
|
||
| 2. **Environment**: [.github/workflows/copilot-setup-steps.yml](/.github/workflows/copilot-setup-steps.yml) | ||
| - Java 25, Maven 3.9.9, PostgreSQL 16 | ||
| - Java 25, Maven 3.9.9, PostgreSQL 18 |
There was a problem hiding this comment.
The agent docs state the copilot-setup-steps environment uses Java 25, but the referenced workflow configures java-version: 26-ea. Update this line to match the actual Java version used in CI/Copilot setup to avoid misleading instructions.
| - Java 25, Maven 3.9.9, PostgreSQL 18 | |
| - Java 26, Maven 3.9.9, PostgreSQL 18 |
|
|
||
| 2. **Environment**: [.github/workflows/copilot-setup-steps.yml](/.github/workflows/copilot-setup-steps.yml) | ||
| - Java 25, Maven 3.9.9, PostgreSQL 16 | ||
| - Java 25, Maven 3.9.9, PostgreSQL 18 |
There was a problem hiding this comment.
The agent docs state the copilot-setup-steps environment uses Java 25, but the referenced workflow configures java-version: 26-ea. Update this line to match the actual Java version used in CI/Copilot setup to avoid misleading instructions.
| - Java 25, Maven 3.9.9, PostgreSQL 18 | |
| - Java 26 (26-ea), Maven 3.9.9, PostgreSQL 18 |
|
@copilot apply changes based on the comments in this thread |
…p-steps.yml Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/4946da9b-7723-4f0b-bdde-c02b5795c35d
All 7 review comments applied in |
| ### Prerequisites | ||
|
|
||
| 1. Install OpenJDK and PostgreSQL: | ||
| 1. Add the PostgreSQL PGDG repository (required for PostgreSQL 18 on Ubuntu 24.04): |
There was a problem hiding this comment.
In this section, the sentence immediately above this list refers to “Ubuntu 24.4+”, which is a typo. Please change it to “Ubuntu 24.04+” to match the actual Ubuntu release versioning used elsewhere.
| sudo apt-get install openjdk-21-jdk postgresql-18 postgresql-contrib-18 postgresql-18-pgaudit | ||
| ``` |
There was a problem hiding this comment.
The installation command omits postgresql-18-pgvector, but full_schema.sql requires the vector extension (CREATE EXTENSION vector). Without installing the pgvector package, schema loading will fail on a fresh machine.
|
@copilot apply changes based on the comments in this thread |
…dex.md install instructions Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/7f8b127f-dbf7-4227-af33-275c762f9a34
Both review comments applied in
|
There was a problem hiding this comment.
Pull request overview
Copilot reviewed 59 out of 59 changed files in this pull request and generated 1 comment.
Comments suppressed due to low confidence (2)
service.data.impl/README-SCHEMA-MAINTENANCE.md:288
- In the new "PostgreSQL 18 Configuration" section, the bullet still says "PostgreSQL Version: 16 with extensions". This contradicts the section title and the rest of the repo’s PG 18 upgrade; update it to 18 to avoid misleading setup guidance.
.devcontainer/init-postgresql.sh:153 - The script verifies
SHOW sslbefore restarting PostgreSQL after appending SSL settings topostgresql.conf. This check may reflect the pre-change config (and doesn’t validate the new cert/key settings); move the verification to after the restart/reload, or restart before running the check.
# Verify SSL configuration
if ! su - postgres -c "psql -c 'SHOW ssl'" | grep -q 'on'; then
echo "SSL configuration verification failed"
exit 1
fi
| 2. Secure the certificate and key: | ||
| ```bash | ||
| chmod 700 /var/lib/postgresql/16/main/server.key | ||
| chmod 700 /var/lib/postgresql/16/main/server.crt | ||
| chown -R postgres:postgres /var/lib/postgresql/16/main/ | ||
| chmod 700 /var/lib/postgresql/18/main/server.key | ||
| chmod 700 /var/lib/postgresql/18/main/server.crt | ||
| chown -R postgres:postgres /var/lib/postgresql/18/main/ | ||
| ``` |
There was a problem hiding this comment.
The README’s SSL hardening commands set server.key and server.crt to mode 700. This is inconsistent with the workflows in this repo (which use 600 for the private key and 644 for the cert) and 700 is unusual for files (adds execute bit). Align the documented permissions with the workflow values to avoid confusing users and to match PostgreSQL’s recommended key permissions.
|
@copilot apply changes based on the comments in this thread |
…-postgresql.sh to match workflows Co-authored-by: pethers <1726836+pethers@users.noreply.github.com> Agent-Logs-Url: https://github.com/Hack23/cia/sessions/68c93989-6735-4df7-97e5-93b9ec672fc5
Fixed in |
|
Description
Upgrade PostgreSQL from 16 to 18 across the entire repository: CI/CD workflows, CloudFormation templates (to 18.3), devcontainer, and all documentation/config references. Also aligned Java version references from 25 to 26 across all agent documentation to match the actual
java-version: 26-eaconfigured incopilot-setup-steps.yml.66+ files changed — packages (
postgresql-18 postgresql-contrib-18 postgresql-18-pgaudit postgresql-18-pgvector), config paths (/etc/postgresql/18/main/), RDS parameter group family (postgres18), engine version (18.3), doc URLs updated to/docs/current/, and Java version references aligned to 26.PostgreSQL 18 is not available in Ubuntu 24.04's default apt repos, so all workflows now add the official PGDG (PostgreSQL Global Development Group) apt repository before installation. GitHub Actions runners have PostgreSQL 14 pre-installed, which occupies port 5432 and prevents auto-creation of the PostgreSQL 18 cluster — all workflows now explicitly drop old clusters and idempotently create/start a PostgreSQL 18 cluster via
pg_dropcluster/pg_lsclusters/pg_createcluster. Theharden-runnerallowed-endpoints lists are updated accordingly forapt.postgresql.org:443andwww.postgresql.org:443.The YAML CloudFormation template
StorageTypewas also upgraded fromgp2togp3to match the JSON template. All RDS-related resources verified consistent between JSON and YAML templates (Family:postgres18, EngineVersion:18.3, StorageType:gp3).Type of Change
Primary Changes
Technical Changes
Impact Analysis
Political Analysis Impact
Technical Impact
rds.force_ssl = 1; PGDG signing key verified via--failflag on curl; SSL file permissions aligned to600/644across all docs and scriptsKey files changed
copilot-setup-steps.yml,codeql-analysis.yml,release.ymlcia-dist-cloudformation.jsonpostgres17/17.5→postgres18/18.3cia-dist-cloudformation.ymlpostgres14/14.4→postgres18/18.3,gp2→gp3Dockerfile,devcontainer.json,init-postgresql.sh700→600/644)postgresql-18-pgvectoradded to install commands, SSL file permissions fixed (700→600/644)copilot-setup-steps.ymlTesting
Documentation
24.4+→24.04+, PGDG repo setup added,postgresql-contrib-18versioned, SSL file permissions fixed700→600/644)24.4+→24.04+, PGDG repo setup added,postgresql-contrib-18versioned,postgresql-18-pgvectoradded forCREATE EXTENSION vectorsupport, duplicate install steps merged)/docs/16/to/docs/current/for forward compatibilityapt-get updatebefore install18.11→18.xacross 10 docs,18.10→18.3in DATA_ANALYSIS_INTOP_OSINT.md)Screenshots
N/A — infrastructure/config changes only.
Related Issues
Checklist
Additional Notes
The YAML CloudFormation template was on PostgreSQL 14.4 (significantly outdated vs the JSON template at 17.5). Both are now aligned at 18.3 with
StorageType: gp3.PostgreSQL 18 packages are not in Ubuntu 24.04 (noble) default repos. The fix adds the official PGDG apt repository (
apt.postgresql.org) in all three workflow files beforeapt-get update, matching the approach already used in the.devcontainer/Dockerfile. Theharden-runnerallowed-endpoints incodeql-analysis.ymlandrelease.ymlare updated to permit egress toapt.postgresql.org:443andwww.postgresql.org:443.GitHub Actions runners (Ubuntu 24.04) have PostgreSQL 14 pre-installed, which occupies port 5432 and prevents auto-creation of the PostgreSQL 18 cluster. All three workflows now include a "Create PostgreSQL 18 cluster" step that drops pre-installed PG14/PG16 clusters (
pg_dropcluster --stop) and idempotently creates/starts a PG18 cluster — checkingpg_lsclusters 18 mainfirst and either starting an existing cluster or creating a new one. This ensures the step won't fail if a future runner image auto-creates the PG18 cluster.Fixes applied from PR review feedback (rounds 1–5):
docs.github.com:433→443anddlcdn.apache.org→dlcdn.apache.org:443in codeql-analysis.yml harden-runner endpoints24.4+→24.04+in README.md and index.md18.11→18.x,18.10→18.3)pg_createclusteridempotent in all 3 workflows (checkspg_lsclustersbefore creating)postgresql-contribtopostgresql-contrib-18in README.md and index.md install instructionsapt-get updatestep in WORKFLOWS.md documentation snippetcopilot-setup-steps.yml(java-version: 26-ea)postgresql-18-pgvectorto index.md install command (required forCREATE EXTENSION vectorin full_schema.sql)700to600(private key) and644(certificate) in README.md and.devcontainer/init-postgresql.shto match workflow values and PostgreSQL recommendationsSecurity Considerations
600/644(private key/cert) across all docs and scriptsRelease Notes
Upgraded PostgreSQL from 16 to 18 across all CI/CD workflows (with PGDG repository for package availability and idempotent cluster creation), CloudFormation templates (RDS 18.3, gp3 storage), devcontainer environment, and documentation. Fixed harden-runner endpoint port typos, aligned PostgreSQL version references across all documentation, ensured all install instructions use versioned
postgresql-contrib-18andpostgresql-18-pgvectorpackages, updated Java version references from 25 to 26 across all agent documentation to match the CI/Copilot environment, and aligned SSL certificate/key file permissions (600/644) in documentation and devcontainer scripts to match workflow values.✨ Let Copilot coding agent set things up for you — coding agent works faster and does higher quality work when set up for your repo.