fix(datastore): stop writing Container's interim plaintext columns part 1/2 (S04-A per-format columns plan: 8/11) (!2658) · Merge requests · GitLab.org / ops / Artifact Registry

Why

Step 8 of the merged plan docs/plans/2026-09-03-s04a-per-format-credential-columns.md drops Container's tmp_plaintext_username and tmp_plaintext_password. Container's credential writes have sat behind the row encryptor since step 7, feat(datastore): container remote credentials b... (!2647 - merged) • Radamanthus Batnag • 19.5, but every write arm still binds the interim pair to NULL. A drop riding the same release as those statements would answer an in-flight credential write with 42703, so the drop takes two releases: this MR stops the statements naming the columns, and part 2/2 drops them.

This is the Container twin of fix(datastore): stop writing Maven's interim pl... (!2629 - merged) • Dzmitry (Dima) Meshcharakou • 19.5. Delivery item for the closed-beta blocker Per-format credential-column encryption wiring (#417 - closed) • Jaime Martinez, Hayley Swimelar+ • 19.4, tracked by S04-A per-format columns Step 8: Drop Container... (#1216) • Radamanthus Batnag.

Stacked MRs

Part	MR	Scope	Reviewable LOC	Target
1/2	this MR	stop binding the interim pair	216	`main`
2/2	feat(datastore): drop Container's interim plain... (!2662 - merged) • Hayley Swimelar, Radamanthus Batnag • 19.5	drop the columns	1,958 (jet output excluded, structure.sql counted)	`hswimelar/s04a-container-stop-writing-plaintext`, Draft

Part 2/2 opens as a Draft and stays Draft until this MR's release ships. Per the plan's Step 8 Depends on, it also waits on a Container spec amendment merging first: docs/specs/S16-container-remote.md, plus the Container half of the generic lines in docs/specs/S13-virtual-remote-foundation.md.

What is non-obvious

The plan predicted two statements to un-bind. At step 7's head both credential writes route through one helper, containerRemoteCredentialUnitAssignments, so removing two SETs there covers both.

The leftover-plaintext count, its page read, and the boot line stay, because the plan drops them with the columns in part 2/2. The plaintextUsername and plaintextPassword fields on the shared credentialUnitBinds also stay, because Maven binds them until fix(datastore): stop writing Maven's interim pl... (!2629 - merged) • Dzmitry (Dima) Meshcharakou • 19.5 lands.

Merge order

This branch bases on main and may land ahead of fix(datastore): stop writing Maven's interim pl... (!2629 - merged) • Dzmitry (Dima) Meshcharakou • 19.5. The plan's "5a merged" dependency covered two shared doc paragraphs rather than any code, and 5a cannot merge yet: it is hard-blocked by feat(datastore): move npm's credentials behind ... (!2627 - merged) • Dzmitry (Dima) Meshcharakou • 19.5, and one thread on it is still open. The doc edits here were written against main's text and assert only Container's fact, so they hold in either landing order.

docs/dev/column-encryption.md and docs/runbooks/key-rotation.md will conflict textually with fix(datastore): stop writing Maven's interim pl... (!2629 - merged) • Dzmitry (Dima) Meshcharakou • 19.5, feat(datastore): drop Maven's interim plaintext... (!2630 - merged) • Dzmitry (Dima) Meshcharakou • 19.5, and feat(datastore): move npm's credentials behind ... (!2627 - merged) • Dzmitry (Dima) Meshcharakou • 19.5. The second lander resolves them as keep-both.

A merged spec disagrees with `main` for one release

docs/specs/S16-container-remote.md:859 says a URL change zeroes the two interim columns. This MR makes that false and does not amend it: the plan routes the S16 amendment to part 2/2, on the drop's irreversibility. That amendment opens as its own MR, ahead of part 2/2.

Before merging

Read Container's leftover-plaintext boot line (remote repositories still hold interim plaintext credentials, format=container) on staging and drive the count to zero.
Whoever plays the production manifest upload reads that line and drives it to zero again, immediately before the play.

Governing ADRs

ADR-007 (docs/adr/007_database_schema.md) governs the encrypted credential unit, which this MR still writes all-or-none on both arms. The ADR does not describe the interim pair, the only thing this MR touches.

Testing

The suites that pinned the nulling now assert the seeded plaintext survives by value on all three arms: set, clear, and URL change. The URL-change integration subtest is new, because that file carried no interim coverage.

Green locally: build, vet, both lint modes including --build-tags=integration, unit, and the datastore integration suite against Postgres with max_locks_per_transaction=4096.

No scenario in docs/testing/ names the interim columns and the un-bind is not API-observable, so none is added or affected.

Context for LLM agents

Rationale

Stacking on 5a's branch. Rejected because 5a cannot merge yet, hard-blocked by npm's step-10 part 3 with one thread still open, and 8a's code depends on nothing 5a introduces.
Amending docs/specs/S16-container-remote.md here. Rejected because the plan routes it to 8b on the drop's irreversibility, and docs/specs/ sits outside this step's file list.
Chosen: base on main, un-bind through the one helper, and leave the leftover report in place.

Consequences: S16 contradicts main for one release, and the key-rotation runbook's operator SQL is the only way to clear a Container row until 8b.

Non-goals

Dropping the columns. Part 2/2 does that, in its own release.
Removing the leftover-plaintext count, page read, and boot line. They go with the columns.
Maven's and npm's un-binds. Steps 5a and 11a own those.
The S16 and S13 amendments. Separate MRs, ahead of part 2/2.
The re-encrypt CLI. Outside this plan step.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan. Row counts are the driving scan's, because an Update root node always reports rows=0.

No migration files changed, so migration mode did not run.

These four statements are the ones whose rendered SQL this MR changes. All consume containerRemoteCredentialUnitAssignments, which loses two SET targets. Rendering main's helper through the same builder gives seven assignments where this branch gives five, the two extra being tmp_plaintext_username = NULL::text and tmp_plaintext_password = NULL::text. Nothing else moves: both removed binds were literal NULL casts consuming no placeholder, so the WHERE clauses and their positional numbering are byte-identical on both sides.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
`datastore.updateContainerRemoteCredentialsStmt.EncNil`	Update	`repositories_p11_pkey`	1 / 1	10.07	0.134ms	19 / 0	1/64, 1/64
`datastore.updateContainerRemoteCredentialsStmt.EncNonNil`	Update	`repositories_p37_pkey`	1 / 1	10.07	0.149ms	24 / 0	1/64, 1/64
`datastore.updateContainerRemoteRepositoryStmt.CredentialClear`	Update	n/a	1 / 1	1.75	0.136ms	16 / 0	1/64
`datastore.updateContainerRemoteRepositoryStmt.CredentialSet`	Update	n/a	1 / 1	1.75	0.135ms	21 / 0	1/64

datastore.updateContainerRemoteCredentialsStmt.EncNil

Summary: Plan matches the method's intent. Both partition keys are bound to a literal, so container_remote_repositories and repositories each prune to 1 of 64 hash partitions, and the parent gate resolves as a Nested Loop Semi Join over an Index Scan on the partition primary key. The Seq Scan on the target partition is the 50-row write-target seed discriminating 1 row from 49 siblings, not a missing index. No anomalies.

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5000, container_remote_repositories=50

Rendered SQL:

UPDATE public.container_remote_repositories
SET wrapped_dek = NULL::bytea,
    ns_key_id = NULL::uuid,
    ns_key_version = NULL::integer,
    encrypted_username = NULL::bytea,
    encrypted_password = NULL::bytea
WHERE ((container_remote_repositories.namespace_id = $1::uuid) AND (container_remote_repositories.repository_id = $2::uuid)) AND (EXISTS (
           SELECT repositories.id AS "repositories.id"
           FROM public.repositories
           WHERE ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id)) AND ((repositories.namespace_id = $3::uuid) AND (((repositories.format IN ($4, $5)) AND (repositories.kind = $6)) AND (repositories.soft_deleted_at IS NULL)))
      ));

Bound args: ['f342606c-ed91-74ec-af7c-9975db6922d5', '006557e2-06a1-7be9-bf64-3fd03ec1f595', 'f342606c-ed91-74ec-af7c-9975db6922d5', 0, 3, 2]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Update on container_remote_repositories  (cost=0.28..10.07 rows=0 width=0) (actual time=0.133..0.134 rows=0 loops=1)
  Update on container_remote_repositories_p11 container_remote_repositories_1
  Buffers: shared hit=19
  ->  Nested Loop Semi Join  (cost=0.28..10.07 rows=1 width=136) (actual time=0.015..0.018 rows=1 loops=1)
        Buffers: shared hit=4
        ->  Seq Scan on container_remote_repositories_p11 container_remote_repositories_1  (cost=0.00..1.75 rows=1 width=42) (actual time=0.006..0.008 rows=1 loops=1)
              Filter: ((namespace_id = 'f342606c-ed91-74ec-af7c-9975db6922d5'::uuid) AND (repository_id = '006557e2-06a1-7be9-bf64-3fd03ec1f595'::uuid))
              Rows Removed by Filter: 49
              Buffers: shared hit=1
        ->  Index Scan using repositories_p11_pkey on repositories_p11 repositories  (cost=0.28..8.31 rows=1 width=42) (actual time=0.008..0.008 rows=1 loops=1)
              Index Cond: ((id = '006557e2-06a1-7be9-bf64-3fd03ec1f595'::uuid) AND (namespace_id = 'f342606c-ed91-74ec-af7c-9975db6922d5'::uuid))
              Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::bigint[])) AND (kind = '2'::bigint))
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=280
Planning Time: 1.220 ms
Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p11: time=0.022 calls=1
Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p11: time=0.139 calls=1
Execution Time: 0.464 ms

Timings: planning 1.220ms, execution 0.464ms, total 1.684ms.

datastore.updateContainerRemoteCredentialsStmt.EncNonNil

Summary: Same access path as the clear arm, which is what the change should leave unchanged: the five ciphertext binds move the SET values, not the plan. Both partition keys prune to 1 of 64, the parent gate takes an Index Scan on the partition primary key, and the estimate matches reality at 1 row. The 9.855 ms execution is one cold FK trigger (fk_container_remote_repositories_repository_id_repositories, 8.832 ms) that lands on whichever statement runs first in a fresh session, and the same trigger costs 0.139 ms on the next one. No anomalies.

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5000, container_remote_repositories=50

Rendered SQL:

UPDATE public.container_remote_repositories
SET wrapped_dek = $1::bytea,
    ns_key_id = $2::uuid,
    ns_key_version = $3::integer,
    encrypted_username = $4::bytea,
    encrypted_password = $5::bytea
WHERE ((container_remote_repositories.namespace_id = $6::uuid) AND (container_remote_repositories.repository_id = $7::uuid)) AND (EXISTS (
           SELECT repositories.id AS "repositories.id"
           FROM public.repositories
           WHERE ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id)) AND ((repositories.namespace_id = $8::uuid) AND (((repositories.format IN ($9, $10)) AND (repositories.kind = $11)) AND (repositories.soft_deleted_at IS NULL)))
      ));

Bound args: ['\x0102030405060708090a0b0c0d0e0f10', 'e0c99b72-e99f-7c94-98a4-2314177fcac9', 1, '\xaabb', '\xccdd', '3e901c1b-629c-774c-8325-6d162750eb0e', '00b2f567-fb9b-73b5-8419-b133e95d3952', '3e901c1b-629c-774c-8325-6d162750eb0e', 0, 3, 2]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Update on container_remote_repositories  (cost=0.28..10.07 rows=0 width=0) (actual time=0.148..0.149 rows=0 loops=1)
  Update on container_remote_repositories_p37 container_remote_repositories_1
  Buffers: shared hit=24
  ->  Nested Loop Semi Join  (cost=0.28..10.07 rows=1 width=136) (actual time=0.016..0.019 rows=1 loops=1)
        Buffers: shared hit=4
        ->  Seq Scan on container_remote_repositories_p37 container_remote_repositories_1  (cost=0.00..1.75 rows=1 width=42) (actual time=0.006..0.008 rows=1 loops=1)
              Filter: ((namespace_id = '3e901c1b-629c-774c-8325-6d162750eb0e'::uuid) AND (repository_id = '00b2f567-fb9b-73b5-8419-b133e95d3952'::uuid))
              Rows Removed by Filter: 49
              Buffers: shared hit=1
        ->  Index Scan using repositories_p37_pkey on repositories_p37 repositories  (cost=0.28..8.31 rows=1 width=42) (actual time=0.009..0.009 rows=1 loops=1)
              Index Cond: ((id = '00b2f567-fb9b-73b5-8419-b133e95d3952'::uuid) AND (namespace_id = '3e901c1b-629c-774c-8325-6d162750eb0e'::uuid))
              Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::bigint[])) AND (kind = '2'::bigint))
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=281
Planning Time: 1.244 ms
Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p37: time=0.023 calls=1
Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p37: time=8.832 calls=1
Trigger for constraint fk_container_remote_repositories_ns_key_id_ns_enc_keys on container_remote_repositories_p37: time=0.662 calls=1
Execution Time: 9.855 ms

Timings: planning 1.244ms, execution 9.855ms, total 11.099ms.

datastore.updateContainerRemoteRepositoryStmt.CredentialClear

Summary: The repository update path carries no parent-gate subquery, because the caller's FOR UPDATE read already resolved the row. The statement keys on (namespace_id, id) and prunes to 1 of 64 partitions on the bound namespace. Seq Scan over the 50-row partition is the write-target seed size, and the filter discriminates 1 row from 49. No anomalies.

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5000, container_remote_repositories=50

Rendered SQL:

UPDATE public.container_remote_repositories
SET wrapped_dek = NULL::bytea,
    ns_key_id = NULL::uuid,
    ns_key_version = NULL::integer,
    encrypted_username = NULL::bytea,
    encrypted_password = NULL::bytea
WHERE (container_remote_repositories.namespace_id = $1::uuid) AND (container_remote_repositories.id = $2::uuid);

Bound args: ['183b0550-b60e-7d8f-8c07-193230ffb7c6', '0158fe8b-bbae-7e44-9a35-534ac4b374a2']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Update on container_remote_repositories  (cost=0.00..1.75 rows=0 width=0) (actual time=0.135..0.136 rows=0 loops=1)
  Update on container_remote_repositories_p62 container_remote_repositories_1
  Buffers: shared hit=16
  ->  Seq Scan on container_remote_repositories_p62 container_remote_repositories_1  (cost=0.00..1.75 rows=1 width=126) (actual time=0.005..0.007 rows=1 loops=1)
        Filter: ((namespace_id = '183b0550-b60e-7d8f-8c07-193230ffb7c6'::uuid) AND (id = '0158fe8b-bbae-7e44-9a35-534ac4b374a2'::uuid))
        Rows Removed by Filter: 49
        Buffers: shared hit=1
Planning:
  Buffers: shared hit=66
Planning Time: 0.406 ms
Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p62: time=0.024 calls=1
Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p62: time=0.639 calls=1
Execution Time: 0.952 ms

Timings: planning 0.406ms, execution 0.952ms, total 1.358ms.

datastore.updateContainerRemoteRepositoryStmt.CredentialSet

Summary: Same access path as the clear arm, plus the ns_key_id FK trigger a non-NULL unit exercises. Pruned to 1 of 64 partitions on the bound namespace, with 1 row estimated and 1 matched. No anomalies.

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5000, container_remote_repositories=50

Rendered SQL:

UPDATE public.container_remote_repositories
SET wrapped_dek = $1::bytea,
    ns_key_id = $2::uuid,
    ns_key_version = $3::integer,
    encrypted_username = $4::bytea,
    encrypted_password = $5::bytea
WHERE (container_remote_repositories.namespace_id = $6::uuid) AND (container_remote_repositories.id = $7::uuid);

Bound args: ['\x0102030405060708090a0b0c0d0e0f10', '8841651e-84c4-71f6-8157-50b6de994fc1', 1, '\xaabb', '\xccdd', '9d1fe15c-3642-7d65-9e24-ed598b807334', '00aa8570-c300-7733-8c6a-5514f9f6c69f']

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Update on container_remote_repositories  (cost=0.00..1.75 rows=0 width=0) (actual time=0.135..0.135 rows=0 loops=1)
  Update on container_remote_repositories_p56 container_remote_repositories_1
  Buffers: shared hit=21
  ->  Seq Scan on container_remote_repositories_p56 container_remote_repositories_1  (cost=0.00..1.75 rows=1 width=126) (actual time=0.006..0.007 rows=1 loops=1)
        Filter: ((namespace_id = '9d1fe15c-3642-7d65-9e24-ed598b807334'::uuid) AND (id = '00aa8570-c300-7733-8c6a-5514f9f6c69f'::uuid))
        Rows Removed by Filter: 49
        Buffers: shared hit=1
Planning:
  Buffers: shared hit=67
Planning Time: 0.442 ms
Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p56: time=0.024 calls=1
Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p56: time=0.610 calls=1
Trigger for constraint fk_container_remote_repositories_ns_key_id_ns_enc_keys on container_remote_repositories_p56: time=0.561 calls=1
Execution Time: 1.481 ms

Timings: planning 0.442ms, execution 1.481ms, total 1.923ms.

Related to #1216

🤖 Generated with Claude Code

Edited Sep 15, 2026 by Hayley Swimelar

fix(datastore): stop writing Container's interim plaintext columns part 1/2 (S04-A per-format columns plan: 8/11)

Why

Stacked MRs

What is non-obvious

Merge order

A merged spec disagrees with main for one release

Before merging

Governing ADRs

Testing

Rationale

Non-goals

Database Review Evidence

Queries

Merge request reports

A merged spec disagrees with `main` for one release