feat(postgres): allow preloading an allowlist of shipped modules - #3506

What this PR does

• Adds postgresql.sharedPreloadLibraries, a closed enum of four module names that already ship in the CloudNativePG PostgreSQL image, rendered onto the CNPG Cluster field of the same meaning.
• Leaves the shared_preload_libraries entry in the postgresql.parameters blocklist exactly as it is.
• Adds tests/shared_preload_libraries_test.yaml: absent by default, single and multiple libraries, no interference with parameters, schema rejection of a non-allowlisted name and of an absolute path, and the parameter blocklist still firing when the allowlist is in use.
• Adds hack/check-postgres-preload-allowlist.bats, which renders the chart with --skip-schema-validation to exercise the template-side allowlist that helm-unittest never reaches, and checks that it matches the schema enum.

shared_preload_libraries is blocked in postgresql.parameters, and rightly so: the parameter is free text and accepts a path, so it can point at a writable location such as the PGDATA volume. The block does not make every preloadable module unreachable, though. CloudNativePG carries a managed extensions list (ManagedExtensions in pkg/postgres/configuration.go, checked on v1.30.0, the operator version in this repo): as soon as a parameter under the pg_stat_statements., auto_explain. or pgaudit. prefix is set, the operator adds that library to shared_preload_libraries itself, and for pg_stat_statements and pgaudit it also runs CREATE EXTENSION. The chart already passes such keys through, so postgresql.parameters."pg_stat_statements.track": all works on main today.

What this PR adds is narrower than a missing capability. pg_prewarm has no route at all, and its autoprewarm worker only runs when the module is preloaded. For the other three, the field is an explicit switch that does not require setting one of their tuning parameters, even to its default value, just to get the library loaded. The two routes combine rather than override: the operator merges this list with the libraries it preloads on its own, so removing a module from sharedPreloadLibraries does not unload it while a parameter under its prefix is still set. The field description says so.

The distinction this PR draws is between a library name and a library path. Nothing needs to be injected to preload these four: pg_stat_statements.so, auto_explain.so, pgaudit.so and pg_prewarm.so are already in $libdir in the image, and that directory is on the read-only root filesystem, so it cannot be written to. dynamic_library_path stays blocked, so $libdir remains the only search path. A bare name from a fixed enum therefore has no way to reach code that is not already in the image, which is the property the blanket block was protecting.

The enum in values.schema.json is the primary guard and rejects a bad value before rendering starts. The template carries the same list as a backstop for renders that bypass schema validation, using the existing fail-fast SECURITY: idiom from the parameter blocklist a few lines below it. helm-unittest validates the schema on every render, so it never reaches that backstop; the bats suite renders with --skip-schema-validation instead. I checked that it fails when the template condition is replaced with false, and when the template list gains a name the enum does not have.

I verified the mechanism end to end on a live CloudNativePG 1.27.3 cluster rather than assuming it: with spec.postgresql.shared_preload_libraries set, the operator writes shared_preload_libraries = 'pg_stat_statements' into postgresql.conf, SHOW shared_preload_libraries reports it, and SELECT ... FROM pg_stat_statements returns rows. Adding a second module to the list took effect too, and the CNPG instance manager restarts PostgreSQL in place for it, with no pod restart. That restart covers every instance, the primary included, so writes pause briefly. The CRD documents the field as additive to the operator's own defaults, so this does not clobber anything CNPG needs to preload itself.

Scope note: the four names are the modules in the image that are useful to a tenant and safe to preload. Anything requiring a background worker or a shipped-but-privileged module was deliberately left out, and the enum is the single place to extend if that changes.

Screenshots

No UI change.

Downstream repositories

packages/apps/postgres/values.schema.json gains a field, which per the trigger map reaches cozystack/terraform-provider-cozystack: its postgres resource carries a hand-maintained schema and its own expand/flatten pair, with no codegen from values.schema.json, so sharedPreloadLibraries will be invisible to Terraform users until it is added there by hand. The enum matters twice over there, because the provider restates enums as stringvalidator.OneOf lists. I searched that repository and found no open PR or issue covering it (the only open PR is a dependabot Go module bump). I have not filed the follow-up, so I am leaving the box unticked rather than claiming work that does not exist. The same gap applies to users.<name>.createRole in #3505; one follow-up there can cover both fields.

I walked the rest of the map against the diff and found nothing else: no package is added, renamed or removed, no existing version enum changes, no default changes, release.prefix and the output Secret and Service names are untouched, and no CRD outside the regenerated packages/system/postgres-rd/cozyrds/postgres.yaml is affected. The README.md parameter table is regenerated here and postgres is already in the website generator's app list, so the website path stays automated.

• No downstream repository is affected by this change
• cozystack/website - follow-up:
• cozystack/terraform-provider-cozystack - follow-up: not filed yet, see the note above
• cozystack/ansible-cozystack - follow-up:
• cozystack/ccp - follow-up:
• cozystack/talm - follow-up:
• cozystack/cozyhr - follow-up:
• cozystack/cozy-proxy - follow-up:
• cozystack/cozystack-telemetry-server - follow-up:
• cozystack/external-apps-example - follow-up:
• cozystack/examples - follow-up:

Release note

feat(postgres): add `postgresql.sharedPreloadLibraries`, a closed allowlist of modules shipped in the PostgreSQL image (`pg_stat_statements`, `auto_explain`, `pgaudit`, `pg_prewarm`) that are preloaded at server start. It is the first way to preload `pg_prewarm`, and lets the other three be preloaded without setting one of their parameters, which CloudNativePG already treats as a request to preload them. The `shared_preload_libraries` parameter stays blocked: only bare names from the allowlist are accepted, never a path. Changing the list restarts PostgreSQL on every instance, the primary included.

Summary by CodeRabbit

• New Features
  • Added optional PostgreSQL shared preload library configuration for pg_stat_statements, auto_explain, pgaudit, and pg_prewarm.
  • Preserves the configured library order. CloudNativePG may automatically preload the first three libraries when their related parameters are set; pg_prewarm must be enabled through this list.
  • Changing the list restarts every PostgreSQL instance, including the primary.
• Validation
  • Rejects unsupported modules, absolute paths, and attempts to set preload libraries through free-form parameters.
• Documentation
  • Added configuration guidance covering supported libraries and restart behavior.