Feed aggregator

If you were wondering, it’s the SBOM thing that I mentioned the other day.

Postgres Extensions in containers with full inventory, provenance and attestation. I’ve been using plenty of AI Agents to put this together. This blog is a little scattered (apologies) but as they say, I didn’t have time to write a short letter.

Here’s what I believe to be the exact structure of current CloudNativePG images:

I have a bunch of irons in the fire related to this project:

• A downstream fork of CloudNativePG/postgres-extensions-containers which can host a bunch of Open Source code which isn’t allowed by CNCF.
  • I track upstream build infra, design my changes to minimize merge conflicts
• A set of patches fixing issues I’ve found, which I’ve submitted upstream
  • Patches are applied on my fork so that I can get stuff working
  • Upstream often tweaks stuff, so then I need to deal with the merge
• This huge new feature – adding proper SBOMs – which is in a feature branch off my repo
  • Planning to submit this upstream
  • Stacked on top of my other fix PRs
• Another huge new feature – PGRX build support – which is stacked on this SBOM feature
  • Not submitted upstream, will only live in my fork
  • Still want to structure code to minimize merge conflicts from upstream

After working on this for like a week, I realized that the set of commands to validate the security and provenance info was going to be totally different for PGRX than for upstream.

I think this is too confusing to users. There needs to be one simple, consistent command to verify provenance and SBOM material. I didn’t have that at the beginning and the AI Agent enabled racing ahead with the code. I didn’t realize the issue until now.

One thing I had been focused on was not having a bunch of things to copy, if someone needs to mirror images to a private container registry. With that focus, this is what I had inadvertantly ended up with.

For Debian-based extensions:

On the PGRX side, builds were timing out because they ran under qemu emulation. (Which is fine if you’re just installing debian packages.) I refactored the PGRX build to use native GitHub Runners, so that it would use this new design:

But after that refactor, the Debian images would look the same as previously, while the PGRX images would now look like this:

Here’s how Codex summarized it for me:

The main issue is that now it’s a totally different set of commands for users to validate PGRX containers. Most users don’t know or care how I built the container. They just want to use a Postgres extension.

Well this was no good. Time to go back to the drawing board and start over on the design.

Docker has a neat feature where you can provide a custom SBOM generator. This approach didn’t get used in my original design.

So using the agents, I rebuilt everything to use this approach. I also included a plugin API which was designed to be used by my PGRX build process later. Codex was able to leverage the previous code for actually composing the SBOM when it did the refactor. So far, this re-design is looking far more elegant and clean.

Most of the logic ends up self-contained in the custom sbom-generator module. The required changes to the build pipelines were surprisingly minimal. After a lot of work, here’s what I ended up with:

This link will probably break once I’ve merged things and cleaned up working branches, but if anyone is curious: https://github.com/ardentperf/postgres-extensions-containers/tree/x-ai/ardentperf/cnpg-sbom-generator/sbom-generator

One thing that AI could not do: correctly tell me what the right design was. Because it relies on me to know the right questions to ask, telling it the goals & priorities of the design.

Next up: refactoring the PGRX branch on top of this one (without losing native build hosts), cleaning up docs & code here and submitting this PR upstream, continuing to chase my handful of other open PRs upstream.

In the meantime – if anyone is running CloudNativePG and you need extensions… the CNPG-Extensions Project is available for testing now! It’s got quite a bit more advanced automation than the upstream at this point. Renovate is fully automated so that when a new extension version drops, you should automatically see it here (and in the Image Catalogs) quickly.

PG-Cron, PG-Partman, PG-Hint-Plan, PG-Stat-KCache, PGSentinel, PLDebugger, PLProfiler, MySQL/MSSQL FDWs… and lots more!

https://github.com/cnpg-extensions/postgres-extensions-containers

And soon (once I merge the first big PR): with full detailed and attested SBOMs and Provenance.

In the past couple weeks, I’ve learned more about renovate, SBOMs, provenance and attestations than I ever wanted to know. (But if I’m being honest, I do enjoy learning a bit more about it.)

Backstory is that I decided to make CNPG-Extensions an actually serious project. The original name was “Not-CNPG” as a joke about CNCF’s restrictive licensing policies which forbid hosting open source software with licenses like GPL. https://github.com/cnpg-extensions/

As a “serious” project I wanted to provide provenance info so users can more have assurance about the contents of a container image, and so that scanners can accurately report licenses and compare software versions against vulnerability databases. This week I also started exploring support for pgrx extensions with full rust dependency graphs in the SBOM so that tools like trivy can flag RUSTSEC vulns even on packages buried in the dependency tree.

Example Trivy output for a Debian-based extension:

https://github.com/ardentperf/postgres-extensions-containers/blob/x-ai/ardentperf/final-payload-sbom/examples/trivy-sbom-examples.txt

Example Trivy output for a pgrx-based extension (this is not final):

https://github.com/ardentperf/postgres-extensions-containers/blob/x-ai/ardentperf/pgrx-implementation/pgrx/examples/trivy-sbom-examples.txt

A few things I’ve learned along the way:

• Renovate auto-update problem: some extensions (MySQL FDW, PL/Debugger) have a sql version that’s completely different from the package version. There’s no way to know the SQL version outside of manually inspecting source code or firing up a full test container.
• Renovate auto-update decision: I don’t want to promote “release candidate” or “beta” versions on channels that users consider to be stable releases. How this is reflected in a version string varies by extension; requires manual check before promotion. But I want stuff as automated as possible ~ generally I don’t want to have to be approving PRs all the time. I might do a little research and only disable auto-update for extensions that have had beta/rc versions in the past.
• Two ways to sign attestations: Cosign/Sigstore or GitHub Artifact Attestations. CNPG currently uses Cosign. I ended up trying out GitHub Artifact Attestations… until a few days later when I stumbled across a random GH Issue on the ParadeDB project which pointed out that GitHub might have paywalled some of the functionality here behind Enterprise subscriptions (I think for people mirroring repos). So then I went and migrated all my working branches back to Cosign, re-ran tests, etc.
• SBOM problem: docker/moby buildkit uses syft to generate an SBOM. latest version of syft still has open issues around debian packages and basically it can’t detect licenses for a huge number of them. so the default docker SBOM is always missing a bunch of license info.
  • my workaround: adding CodeScan. then CodeScan promptly crashed on the PL/R license file because the file was too big. so i split the file into chunks using a marker at the beginning of each license and scanned each file. this worked and gave me a super highly reliable license scan, though I had to manually re-assemble an SBOM.
• If you install the latest rust using the method where it compiles cargo-about and cargo-cyclonedx, those two packages take… a loooooong time. For two large pgrx builds (pg-parquet and pg-search), my GitHub action workflows hit the six hour timeout and got killed.
• Turns out trivy can also generate SBOMs including Cargo.lock parsing… but you need the latest version because even something as recent as 0.66.0 has bugs where it couldn’t parse Cargo.lock if the project used workspaces. https://github.com/aquasecurity/trivy/issues/10007
  • Even the latest version of Trivy has a bug where it misses git-only deps. AI told me there wasn’t an existing issue… hopefully was right; I filed a new one https://github.com/aquasecurity/trivy/discussions/11236
  • Trivy doesn’t seem to have any capability to emit CONTAINS records in the SPDX SBOM. So I still need syft because I want to work backwards from files actually copied to the extension scratch container, to figure out which packages are included.
• “Modern” software languages (um, like less than a couple decades old?) that handle dependencies are wonderful. But I wanted to add pg-duckdb which is in C++ and so now I need bespoke pg-duckdb dependency processing build scripts… at least this gets easier with AI agents to help do the coding… but yeah not wanting to commit myself to maintaining any custom build systems unless it’s really worthwhile (AI or not)
• GitHub CI being as powerful as it is – and offering free compute – is a much more significant contribution to Open Source than they get credit for. My project to build, test, host and distribute a bunch of kubernetes postgres extensions with strong provenance across two architectures, two major Debian OS base containers, three versions of CloudNativePG… this can trigger some rather impressive counts of GitHub jobs! Like hundreds! And I’m amazed just how much compute GitHub gives away for free, which supports Open Source projects like this.

There are still some big open questions in my mind around how to best manage Postgres Extensions with CloudNativePG. How high of a bar for contributions? How much review is needed? What level of commitment from a contributor is expected – do we want to avoid drive-by contributions of big chunks of code which could become a liability – and how to decide whether to trust someone?

Maybe my CNPG-Extensions project can be an option for a lower bar, in addition to the licensing concerns. But I’m not sure. How important is provenance? I’ve put a lot of effort into it this past week. I’m not sure about random debian packages downloaded from GitHub; how do we know they were built correctly? Do we care? If a package is in the official Debian or PGDG repositories then I tend to have a little more trust in it – is that justified?

And then there are the AI related topics: just because you can ask an AI agent to rewrite the operating system on your laptop, doesn’t actually change the fundamentals of computing that much. Decades ago I had fun running my own wordpress site. I learned a bit and it fueled my enthusiasm. But after the third time cleaning up a hack I decided I’d rather spend my time elsewhere, and I started paying someone else to manage that part. AI agents are getting lots of people excited about coding again which is great. But eventually it comes back to boring and well maintained platforms.

Anybody can throw some code on the internet. That doesn’t mean anybody will maintain the code – ensuring that things are rebuilt after Log4Shell happens again, two years from now. AI is great at writing code but you still better pay attention to what human is at the steering wheel and whether they seem like they are paying attention, or seem like they have any level of commitment to stick around. Or alternatively you need to be willing to take full ownership/responsibility yourself.

I like the OpenSSF scorecard for this; it’s worth a read. https://scorecard.dev/

Just a reminder that AI doesn’t change this – it’s more important than ever.

And a final random musing… here’s a picture of my whiteboard right now where I’m trying to track a hierarchy of five different work streams that I have going all at the same time! Each is a separate git branch, forked from the branch above it (and requiring a rebase whenever an upper branch changes something). Agents mean I can fire off long-running tests and come back 12 hours later to check results and make design decisions, but having five different work streams in parallel is a lot of mental overhead – it’s a little tiring!

SSMA (SQL Server Migration Assistant) handles the whole Oracle-to-SQL Server move: it reads the source data dictionary, converts the schema, then generates a SELECT per table to pull the rows across. That last step is where this story goes wrong.

Migrating ~5,600 Oracle tables to SQL Server with SSMA. Most load fine; ~100 tables fail Migrate Data with the same error:

ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904:
"SYS_C00004_21081414:28:22$": invalid identifier

The named column exists in no DDL anyone wrote. The [Ora] prefix says Oracle itself is rejecting the query: SSMA built an extraction SELECT naming a column Oracle refuses to resolve. Tellingly, SELECT * and COUNT(*) run fine against the same table, whatever this column is, Oracle is happy to ignore it, but not to be asked for it by name.

The name is the giveaway: SYS_C00004_21081414:28:22$ is what Oracle calls a column after ALTER TABLE … SET UNUSED COLUMN.

Oracle offers two ways to get rid of a column: a logical delete and a physical one. The physical delete (ALTER TABLE … DROP COLUMN) is the honest one, but on a large table it is very time- and resource-consuming. That’s why people reach for the logical delete instead:

ALTER TABLE table_name SET UNUSED (column_name);

That statement is metadata-only and instant. The column immediately stops being visible to users, and the physical removal is deferred to whenever there is time for it (see Oracle Documentation):

ALTER TABLE table_name DROP UNUSED COLUMNS;
-- on large tables, cap undo growth by checkpointing every N rows:
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;

To free the original name for reuse, Oracle renames the column to SYS_C<internal column number>_<YYMMDDHH24:MI:SS>$, sets USER_GENERATED to NO, HIDDEN_COLUMN to YES and releases its COLUMN_ID. So the timestamp is not when the column was added, it is the second someone ran SET UNUSED. Ours says 14 August 2021, 14:28:22.

That also explains the error pattern. The operation is one-way and the column is unreadable by design, so naming it gets you ORA-00904 «invalid identifier». SELECT * and COUNT(*) keep working because the column no longer has a COLUMN_ID and is simply excluded from the star. SSMA, however, lists it and builds an explicit column list Oracle then refuses.

Not to be confused with SYS_NC…$. Those are a different animal: virtual columns backing a function-based index or extended statistics.

Bottom line: returning NULL costs you nothing. This is a column its owner already decided to delete, holding data Oracle itself will no longer hand out. There is no information left to lose.

• SSMA setting Ignore hidden system columns = Yes was not making any effect on this use case
• Dropping the column on SQL Server resolves nothing because the error is on the source SELECT, unaffected.
• Dropping it on Oracle could not be done in our scenario because the source is frozen; DDL not allowed.
• Custom select, column removed or bare NULL: SSMA still expects the name in its mapping and fails with “key not present” or “does not match up” before the query ever reaches Oracle.

Before editing anything, get the full list. Discovering the affected tables one failed migration at a time is a waste of an afternoon because the data dictionary already knows.

The reason the columns are findable at all is an asymmetry between two views: an unused column is gone from ALL_TAB_COLUMNS, but still listed in ALL_TAB_COLS with HIDDEN_COLUMN = 'YES'. That second view is what you query:

SELECT owner,
table_name,
column_name,
data_type,
internal_column_id,
TO_DATE(REGEXP_SUBSTR(column_name, '\d{8}:\d{2}:\d{2}'),
'YYMMDDHH24:MI:SS') AS set_unused_at
FROM dba_tab_cols
WHERE hidden_column = 'YES'
AND user_generated = 'NO'
AND REGEXP_LIKE(column_name, '^SYS_C\d+_\d{8}:\d{2}:\d{2}\$$')
-- AND owner = '<SCHEMA_NAME>'
ORDER BY owner, table_name, internal_column_id;

The regex is deliberately strict: it matches only the SET UNUSED naming pattern, so virtual columns and other system-generated names stay out of the result.

One more view is worth a look, as a cross-check:

SELECT owner, table_name, count AS unused_columns
FROM   dba_unused_col_tabs
--WHERE owner = '<SCHEMA_NAME>'
ORDER  BY count DESC, table_name;

DBA_UNUSED_COL_TABS gives the number of unused columns per table. Sorting by that number puts the dangerous tables first: those with two or three hidden columns are the ones where you’ll forget a line in the custom select and be back at square one.

Keep the hidden column’s name as an alias, but return a literal NULL instead of reading it. SSMA’s mapping finds the name (no “key not present”); Oracle never resolves the real column (no ORA-00904).

• Tools → Project Settings → General → Migration → enable Extended data migration options.

• Data Migration Settings tab → tick Use custom select → replace each hidden-column line with:

 SELECT ...   
    TO_CHAR("<COLUMN_NAME>", 'TM', 'NLS_NUMERIC_CHARACTERS = ''.,''') as "<COLUMN_NAME>",
    NULL as "SYS_C00004_21081414:28:22$"
from <OWNER>.<TABLE_NAME> t

• Migrate Data → 100%. Drop the NULL-filled column(s) on SQL Server in post-migration cleanup.

SYS_C…$ is not an exotic Oracle feature, it’s an ordinary column someone deleted years ago, logically. Oracle keeps the name on file; SSMA finds it, insists on naming it, and Oracle refuses to hand it over. Aliasing a NULL satisfies both, then you drop the column on the target. No source DDL, no external tooling, everything inside SSMA, behind a project setting that’s hidden by default.

L’article Fixing ORA-00904 on Oracle hidden columns during SSMA data migration est apparu en premier sur dbi Blog.

Posted by Pete On 19/08/26 At 12:42 PM

Posted by Pete On 17/08/26 At 12:51 PM

Posted by Pete On 05/08/26 At 09:35 AM

Posted by Pete On 03/08/26 At 01:59 PM

Posted by Pete On 29/07/26 At 02:20 PM

Posted by Pete On 28/07/26 At 08:41 AM

Posted by Pete On 23/07/26 At 12:01 PM

Posted by Pete On 13/07/26 At 09:11 AM

In a previous blog, I presented how to set up a distribution path between two GoldenGate deployments both secured with NGINX. The method I used there was purely through the Web UI. But GoldenGate also exposes a full REST API, and everything you can do in the UI can be done through the API as well, which is useful for automation, scripting, or when the UI is not reachable.

This blog covers the exact same setup, using the REST API instead. I will show two ways of doing it :

• Using oggrestapi.py, the GoldenGate REST client I released in another blog.
• Using the requests library to call the REST API directly.

The prerequisites are the same as in the previous blog :

• Two GoldenGate Microservices deployments, ogg_test_01 (source) on oggvm1 and ogg_test_02 (target) on oggvm2. I will use the latest 26ai version.
• Both OGG setups secured with NGINX acting as a reverse proxy, so everything goes through port 443.
• A running extract on the source, writing to a trail (aa in my case).

Just like in the Web UI, there are three steps to get a working distribution path :

• Create a path connection on the source, to authenticate against the target.
• Register the target’s CA certificate on the source Service Manager.
• Create and start the distribution path.

A quick note on URLs before we start. Behind an NGINX reverse proxy, each service has its own path prefix :

• Administration Service : /services/<deployment>/adminsrvr/v2/...
• Distribution Service : /services/<deployment>/distsrvr/v2/...
• Service Manager : /services/ServiceManager/v2/...

The oggrestapi.py client builds these for you as soon as you pass reverse_proxy=True and the deployment name, so let’s connect once and reuse the client. If you don’t provide the password argument, you will be prompted for it.

from oggrestapi import OGGRestAPI

ogg_source = OGGRestAPI(
    url="https://oggvm1",
    username="ogg",
    deployment="ogg_test_01",
    reverse_proxy=True,
)

As explained in Creating Path Connections with GoldenGate REST API, a path connection is simply an alias in the Network domain. It stores the credentials of a user that exists on the target deployment, and its alias is only known on the source side.

With the client, just call the create_alias method :

ogg_source.create_alias(
    alias="ogg_target",
    domain="Network",
    data={
        "userid": "ogg_user_on_target",
        "password": "***",
    },
)

As mentioned in the introduction, here is the same call with requests, calling the Administration Service of oggvm1 through NGINX :

import requests

auth = ("ogg", "ogg_password")

response = requests.post(
    "https://oggvm1/services/ogg_test_01/adminsrvr/v2/credentials/Network/ogg_target",
    auth=auth,
    json={
        "userid": "ogg_user_on_target",
        "password": "***",
    },
)

After refreshing the source Web UI, the new path connection is visible under the Path Connections tab :

But of course, you can also view the new path connection by calling the REST API:

# Since path connections are aliases of the Network domain, we use the get_alias method to retrieve them
>>> ogg_source.get_alias('Network', 'ogg_target')
{'$schema': 'ogg:credentials', 'userid': 'ogg_user_on_target', 'type': 'PASSWORD'}

Because the deployments are secured with NGINX, the source has to trust the certificate authority that signed the target’s certificate. This is done on the source Service Manager, by registering the target’s root CA certificate.

With the client, use create_deployment_certificate against the source deployment. The certificate type to use is truststore, and the certificate content goes under trustpointBundle.trustpointPem:

target_ca = open("rootCA_ogg_test_02.pem").read()

ogg_source.create_deployment_certificate(
    deployment="ogg_test_01",
    type="truststore",
    certificate="rootCA_ogg_test_02",
    data={
        "trustpointBundle": {
            "trustpointPem": target_ca,
        }
    },
)

The same call with requests, this time on the Service Manager prefix :

target_ca = open("rootCA_ogg_test_02.pem").read()

response = requests.post(
    "https://oggvm1/services/ServiceManager/v2/deployments/ogg_test_01/certificates/truststore/rootCA_ogg_test_02",
    auth=auth,
    json={
        "trustpointBundle": {
            "trustpointPem": target_ca,
        }
    },
)

Registering under the specific deployment (ogg_test_01) is the equivalent of the Local option in the Web UI. To get the Shared behavior instead, register the same certificate under the ServiceManager deployment name, so it becomes available to every deployment on that node.

If the certificate file contains a chain of certificates, you must register each certificate individually, since GoldenGate does not accept them in one go. I described that issue in detail in a blog about the OGG-30007 error.

We can now create the distribution path itself. It has a source endpoint (the local trail) and a target endpoint (the target’s Receiver Service, reached over wss through NGINX). Because the target is NGINX-secured, the target URI :

• uses the wss protocol on port 443,
• points at the Receiver Service path prefix, recvsrvr, not distsrvr (that prefix is only for the Distribution Service on the source side),
• does not carry the path connection alias itself. The alias goes in a separate authenticationMethod key.

With the client :

ogg_source.create_distribution_path(
    distpath="path12",
    name="path12",
    source={
        "uri": "trail://localhost/services/v2/sources?trail=PDB1/aa",
    },
    target={
        "uri": "wss://oggvm2/services/ogg_test_02/recvsrvr/v2/targets?trail=PDB1/bb",
        "authenticationMethod": {
            "domain": "Network",
            "alias": "ogg_target",
        },
    },
    begin="now",
    status="running",
)

And the equivalent requests call, on the Distribution Service prefix (/services/ogg_test_01/distsrvr/):

response = requests.post(
    "https://oggvm1/services/ogg_test_01/distsrvr/v2/sources/path12",
    auth=auth,
    json={
        "name": "path12",
        "source": {
            "uri": "trail://localhost/services/v2/sources?trail=PDB1/aa",
        },
        "target": {
            "uri": "wss://oggvm2/services/ogg_test_02/recvsrvr/v2/targets?trail=PDB1/bb",
            "authenticationMethod": {
                "domain": "Network",
                "alias": "ogg_target",
            },
        },
        "begin": "now",
        "status": "running",
    },
)

The trail value in both URIs also has to match the path the extract actually registers, EXTTRAIL PDB1/aa on the source becomes trail=PDB1/aa in the source URI, and the same logic applies to the target’s bb trail. A bare trail=aa without the PDB path segment matches neither what the extract writes nor what the target’s own directory layout expects.

Once the path is created with status: "running", the trail files start flowing. You can confirm it on the target :

oracle@oggvm2:~/ ll $OGG_DEPLOYMENT_HOME/var/lib/data/PDB1
total 0
-rw-r-----. 1 oracle oinstall 0 Mar 22 07:34 bb000000000

If your distribution path doesn’t start and generates a “certificate that failed validation” error, it means that you incorrectly registered your certificates. Make sure that the target deployment’s CA certificate is registered on the source Service Manager, and not the other way around.

And that’s it. With three REST calls, through oggrestapi.py or using the requests module, you get the exact same NGINX-secured distribution path as the Web UI method, but in a form you can script and repeat.

L’article NGINX Secured Distribution Path with GoldenGate REST API est apparu en premier sur dbi Blog.