feat(datastore): container remote credentials behind the row encryptor (S04-A per-format columns plan: 7/11)

Radamanthus Batnagrequested to merge
rbatnag/s04a-per-format-credential-columns-step-7 into main

Summary

Step 7 of the S04-A per-format credential columns plan. Container's credential reads and writes move behind crypto.RowEncryptor, the table registers with the namespace-key rotation sweep, and has_credentials becomes the encrypted unit's wrapped_dek IS NOT NULL. The insert stops naming the interim tmp_plaintext_* pair, because a new row holds no earlier value to erase.

S13's fail-closed arms for Container land with it. With no encryption stack configured, a credential write refuses before any statement runs, a read of a credential-bearing row refuses rather than fetching anonymously, and the bearer-discovery arm refuses above the token exchange instead of exchanging without credentials. Discovery on a credential-free row still writes auth_url.

The health-probe arm of that table is vacuous for Container by construction: the probe is an unauthenticated base-URL HEAD and NewHealthTargetResolver() takes no credential store, so a missing stack cannot change it. There is no refusal to make, and so none to assert.

internal/format/oci/remote_credentials.go carries a Modify entry in the step's file list and needed no edit. It already reads the pair through Secret.Value().

Merge order

remote_auth_discovery_failures_total gains a fifth stage, credential_read. S16 declares that domain closed at four, so the amendment merges first. It owes the row's description as well as its set: the sentence there calls every stage a challenge-discovery leg, and the credential read ends the acquisition before discovery runs. docs/dev/observability.md records that the row takes the amendment route rather than being left alone.

The plan's own stale claims — that only Step 3 edits namespace_encryption_keys.go, and the file lists this step outgrew — go to the batched docs(plans) MR. Guardrail 4 bars a step MR from editing the plan, so they are not fixed here.

Size

+3984 −1120 across 65 files, past the 500-LOC guidance. The file count follows from one signature: NewContainerRemoteRepositoryStore takes a new parameter, the row encryptor, so every caller of it is updated here. Half the changed files move 20 lines or fewer, and 8 of those add nothing but the new argument.

Group Files + −
Production Go 21 656 336
Test Go 36 3177 666
Docs and sidecars 7 150 117
Scripts 1 1 1

Tests are 79% of the additions. The work to review is 14 files; the production core is four of them — the store, the table instantiation, and the update and details paths. That core cannot split: a write that seals while a read still projects the interim pair serves every credentialed container remote anonymously in between.

Governing ADRs

Read hunting for a conflict, not confirmed from titles. scripts/adr-freshness.sh reports the mirror current.

  • ADR-007 (database schema) — conforms. The unit's five columns match the ADR's set, all-or-none holds on every write path, the 2048-character cap runs ahead of the encrypt, the FK is (ns_key_id, namespace_id) with ON DELETE RESTRICT, and reads resolve the wrapping key by ns_key_id, never by the denormalized ns_key_version.
  • ADR-023 (code structure) — conforms. go list -deps ./internal/datastore reaches no format package, and the ADR leaves file organization inside a shared package to the implementer.
  • ADR-006 (technology stack) — conforms. Every statement stays on go-jet, the new warn line goes through LabKit, the new label through Prometheus.
  • ADRs 001, 004, 005, 008, 009, 010, 022, 024 — no provision this change touches. The diff's own ADR-009 and ADR-004 citations check out against the ADR text.

Testing

Each acceptance bullet, and what carries it:

Acceptance Evidence
Stack present: set, then fetch, succeeds TestContainerRemoteCredentialStore_SetThenRead, _RunsTheCredentialValidator
No plaintext in the row _EncryptedAtRest, _AADBindsRowAndTable, _ReadNeverRendersCleartext, plus the insert-tuple pin in container_remote_repositories_create_test.go
Stack absent: a credential write refuses _FailsClosedWithoutEncryption, _FailsClosedWithoutEncryptionOverRealRows, and the create and update arms
Stack absent: a credential-bearing read refuses TestUpstreamAuthenticator_FailsClosedOnCredentialRead, _WritesNoVerdict, _FailsClosedBeforeTheDiscoveryHead, _FailsClosedOnReauthorize
Bearer discovery still writes auth_url with no credentials _RecordBearerDiscovery, TestUpstreamAuthenticator_CredentialFreeRowStillRecordsDiscovery

Three assertions on this branch were measured rather than reasoned about, by applying the mutation and watching the case redden:

  • Dropping Container's term from the count sum reddens TestCredentialRowsForKeyVersionStmt_NamesEveryCountedTable. It did not before: the case looped Maven's and npm's names only.
  • Dropping Container's (namespace_id, ns_key_id) index reddens TestContainerRemoteCredentialTable_ExplainCountRowsForKeyVersion against a real database. Its previous unqualified substrings passed under that same mutation, because the statement renders every format's term in one plan.
  • Dropping credential_read from the metric's registered Help reddens TestRemoteAuthDiscoveryFailures_HelpEnumeratesEveryStage. Nothing compared the help text with the domain before, which is how that enumeration drifted.

golangci-lint is clean tagless and with --build-tags=integration, as are check-comment-caps.sh, check-jet-null-wrap.sh, datastore-test-names.sh and vale.

e2e scenario catalogs: no scenario is added or affected. docs/testing/ covers behavior a client can observe, and this step changes where credentials are stored, not any request or response a client sees. The fail-closed arms it adds are reachable only by removing the encryption configuration, which no catalog scenario does.

Related to #417 (closed)

Database Review Evidence

No migration is added or modified, so migration mode did not run: the only migrations/ path in the diff is a sidecar .md. Step 6 added Container's columns; Step 8 drops the plaintext pair.

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; server reported 17.10), with synthesized seed data rolled back per run and the container torn down at the end. 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 for the seed shape, rendered SQL, and raw plan.

One deviation to declare: the write targets carry the read seed's 5000 sibling rows rather than the prescribed 49, because both share one transaction. That makes each write's WHERE discriminate against more rows, not fewer.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.countContainerRemoteLeftoverPlaintextStmt Aggregate n/a 1 / 1 361.97 0.447ms 310 / 0 64/64
datastore.credentialRowsForKeyVersionStmt Result n/a 1 / 1 703.59 1.512ms 441 / 0 4/64
datastore.containerRemoteDetailsStmt Nested Loop container_remote_repositories_p_namespace_id_repository_id_idx9 +1 more 1 / 1 16.62 0.014ms 6 / 0 2
datastore.findContainerRemoteCredentialsStmt Limit container_remote_repositories_p_namespace_id_repository_id_idx9 +1 more 1 / 1 16.62 0.012ms 6 / 0 2
datastore.insertContainerRemoteRepositoryStmt.EncNil Insert on container_remote_repositories n/a 0 / 0 0.03 0.052ms 7 / 0 1
datastore.insertContainerRemoteRepositoryStmt.EncSet Insert on container_remote_repositories n/a 0 / 0 0.03 0.053ms 7 / 0 1
datastore.selectContainerRemoteLeftoverPlaintextPageStmt Limit container_remote_repositories__namespace_id_repository_id_idx10 +63 more 10 / 10 52.55 0.894ms 5905 / 0 64/64
datastore.updateContainerRemoteCredentialsStmt.EncNil Update on container_remote_repositories container_remote_repositories_p_namespace_id_repository_id_idx9 +1 more 0 / 0 16.62 0.045ms 20 / 0 2
datastore.updateContainerRemoteCredentialsStmt.EncSet Update on container_remote_repositories container_remote_repositories_p_namespace_id_repository_id_idx9 +1 more 0 / 0 16.62 0.033ms 10 / 0 2
datastore.updateContainerRemoteRepositoryStmt.CredentialSet Update on container_remote_repositories container_remote_repositories_p09_pkey 0 / 0 8.30 0.101ms 19 / 0 1
datastore.updateContainerRemoteRepositoryStmt.URLChangeClears Update on container_remote_repositories container_remote_repositories_p09_pkey 0 / 0 8.30 0.039ms 7 / 0 1

Warning

Partition fan-out. Two statements scan every partition of container_remote_repositories:

  • datastore.countContainerRemoteLeftoverPlaintextStmt: 64/64 partitions, 64 Seq Scans, 310 shared buffer hits. The statement binds no namespace_id, so nothing prunes — by design: the boot report asks a fleet-wide question ("does any row still hold an interim pair?"), and a namespace-scoped count cannot answer it. ADR-007 accepts fan-out for a bounded background job, and the merged Maven and npm arms have the same shape. What this step changes is that a third format now shares one timeout budget, so each format's slice is a third of leftoverPlaintextReadTimeout rather than a half — recorded in docs/dev/column-encryption.md.
  • datastore.selectContainerRemoteLeftoverPlaintextPageStmt: 64/64 partitions via Merge Append over per-partition (namespace_id, repository_id) index scans, 5905 shared buffer hits for a LIMIT 10. Same missing-namespace_id root cause, and it only runs when the count above is non-zero.

Note

One caveat on the count statement's own test. credentialRowsForKeyVersionStmt chose Seq Scan on the three format partitions here (4/64 partitions touched, 441 buffer hits, 1.5 ms), not the (namespace_id, ns_key_id) index that TestContainerRemoteCredentialTable_ExplainCountRowsForKeyVersion asserts. Both plans are correct for their data: that case seeds one row, where the index wins; this seed puts 5000 matching rows per table in one partition, where scanning it is cheaper. So the test pins that the index exists and is usable for a selective key version — it does not predict the plan for a key version that covers most of a namespace's rows.

datastore.countContainerRemoteLeftoverPlaintextStmt

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

SELECT COUNT(container_remote_repositories.id) AS "count"
FROM public.container_remote_repositories
WHERE (container_remote_repositories.tmp_plaintext_username IS NOT NULL) OR (container_remote_repositories.tmp_plaintext_password IS NOT NULL);

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Aggregate  (cost=361.96..361.97 rows=1 width=8) (actual time=0.443..0.447 rows=1 loops=1)
   Buffers: shared hit=310
   ->  Append  (cost=0.00..361.31 rows=261 width=16) (actual time=0.272..0.441 rows=100 loops=1)
         Buffers: shared hit=310
         ->  Seq Scan on container_remote_repositories_p09 container_remote_repositories_10  (cost=0.00..205.00 rows=198 width=16) (actual time=0.266..0.274 rows=100 loops=1)
               Rows Removed by Filter: 4900
               Buffers: shared hit=155
               Buffers: shared hit=155
 Planning:
   Buffers: shared hit=765
 Planning Time: 2.999 ms
 Execution Time: 0.655 ms
[elided: per-partition scans that returned 0 rows, with their repeated Filter/Index Cond lines]

Timings: planning 2.999ms, execution 0.655ms.

datastore.credentialRowsForKeyVersionStmt

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

SELECT (((
SELECT COUNT(*)
FROM public.maven_remote_repositories
WHERE (maven_remote_repositories.namespace_id = $1::uuid) AND (maven_remote_repositories.ns_key_id = (
SELECT namespace_encryption_keys.id AS "namespace_encryption_keys.id"
FROM public.namespace_encryption_keys
WHERE (namespace_encryption_keys.namespace_id = $2::uuid) AND (namespace_encryption_keys.version = $3::integer)
))
) + (
SELECT COUNT(*)
FROM public.npm_remote_repositories
WHERE (npm_remote_repositories.namespace_id = $4::uuid) AND (npm_remote_repositories.ns_key_id = (
SELECT namespace_encryption_keys.id AS "namespace_encryption_keys.id"
FROM public.namespace_encryption_keys
WHERE (namespace_encryption_keys.namespace_id = $5::uuid) AND (namespace_encryption_keys.version = $6::integer)
))
)) + (
SELECT COUNT(*)
FROM public.container_remote_repositories
WHERE (container_remote_repositories.namespace_id = $7::uuid) AND (container_remote_repositories.ns_key_id = (
SELECT namespace_encryption_keys.id AS "namespace_encryption_keys.id"
FROM public.namespace_encryption_keys
WHERE (namespace_encryption_keys.namespace_id = $8::uuid) AND (namespace_encryption_keys.version = $9::integer)
))
)) AS "count";

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Result  (cost=703.57..703.59 rows=1 width=8) (actual time=1.511..1.512 rows=1 loops=1)
   Buffers: shared hit=441
   InitPlan 2
     ->  Aggregate  (cost=240.51..240.52 rows=1 width=8) (actual time=0.533..0.533 rows=1 loops=1)
           Buffers: shared hit=153
           InitPlan 1
             ->  Seq Scan on namespace_encryption_keys_p09 namespace_encryption_keys  (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1)
                   Filter: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (version = 1))
                   Buffers: shared hit=1
           ->  Seq Scan on maven_remote_repositories_p09 maven_remote_repositories  (cost=0.00..227.00 rows=5000 width=0) (actual time=0.005..0.416 rows=5000 loops=1)
                 Filter: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (ns_key_id = (InitPlan 1).col1))
                 Buffers: shared hit=153
   InitPlan 4
     ->  Aggregate  (cost=219.51..219.52 rows=1 width=8) (actual time=0.444..0.444 rows=1 loops=1)
           Buffers: shared hit=132
           InitPlan 3
             ->  Seq Scan on namespace_encryption_keys_p09 namespace_encryption_keys_1  (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)
                   Filter: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (version = 1))
                   Buffers: shared hit=1
           ->  Seq Scan on npm_remote_repositories_p09 npm_remote_repositories  (cost=0.00..206.00 rows=5000 width=0) (actual time=0.003..0.337 rows=5000 loops=1)
                 Filter: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (ns_key_id = (InitPlan 3).col1))
                 Buffers: shared hit=132
   InitPlan 6
     ->  Aggregate  (cost=243.51..243.52 rows=1 width=8) (actual time=0.533..0.533 rows=1 loops=1)
           Buffers: shared hit=156
           InitPlan 5
             ->  Seq Scan on namespace_encryption_keys_p09 namespace_encryption_keys_2  (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)
                   Filter: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (version = 1))
                   Buffers: shared hit=1
           ->  Seq Scan on container_remote_repositories_p09 container_remote_repositories  (cost=0.00..230.00 rows=5000 width=0) (actual time=0.005..0.411 rows=5000 loops=1)
                 Filter: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (ns_key_id = (InitPlan 5).col1))
                 Buffers: shared hit=156
 Planning:
   Buffers: shared hit=327
 Planning Time: 0.817 ms
 Execution Time: 1.529 ms
[elided: per-partition scans that returned 0 rows, with their repeated Filter/Index Cond lines]

Timings: planning 0.817ms, execution 1.529ms.

datastore.containerRemoteDetailsStmt

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

SELECT container_remote_repositories.id AS "container_remote_repositories.id",
container_remote_repositories.repository_id AS "container_remote_repositories.repository_id",
container_remote_repositories.url AS "container_remote_repositories.url",
(container_remote_repositories.wrapped_dek IS NOT NULL) AS "has_credentials",
container_remote_repositories.cache_validity_hours AS "container_remote_repositories.cache_validity_hours",
container_remote_repositories.last_health_status AS "container_remote_repositories.last_health_status",
container_remote_repositories.last_health_checked_at AS "container_remote_repositories.last_health_checked_at"
FROM public.container_remote_repositories
INNER JOIN public.repositories ON ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id))
WHERE ((container_remote_repositories.namespace_id = $1::uuid) AND (container_remote_repositories.repository_id = $2::uuid)) AND ((repositories.namespace_id = $3::uuid) AND (((repositories.format IN ($4, $5)) AND (repositories.kind = $6)) AND (repositories.soft_deleted_at IS NULL)));

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Nested Loop  (cost=0.56..16.62 rows=1 width=73) (actual time=0.013..0.014 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Index Scan using container_remote_repositories_p_namespace_id_repository_id_idx9 on container_remote_repositories_p09 container_remote_repositories  (cost=0.28..8.30 rows=1 width=121) (actual time=0.007..0.007 rows=1 loops=1)
         Index Cond: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (repository_id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid))
         Buffers: shared hit=3
   ->  Index Scan using repositories_p09_pkey on repositories_p09 repositories  (cost=0.28..8.31 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)
         Index Cond: ((id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid) AND (namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid))
         Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (kind = 2))
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=13
 Planning Time: 0.200 ms
 Execution Time: 0.026 ms

Timings: planning 0.200ms, execution 0.026ms.

datastore.findContainerRemoteCredentialsStmt

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

SELECT container_remote_repositories.id AS "container_remote_repositories.id",
container_remote_repositories.wrapped_dek AS "container_remote_repositories.wrapped_dek",
container_remote_repositories.ns_key_id AS "container_remote_repositories.ns_key_id",
container_remote_repositories.ns_key_version AS "container_remote_repositories.ns_key_version",
container_remote_repositories.encrypted_username AS "container_remote_repositories.encrypted_username",
container_remote_repositories.encrypted_password AS "container_remote_repositories.encrypted_password"
FROM public.container_remote_repositories
INNER JOIN public.repositories ON ((repositories.id = container_remote_repositories.repository_id) AND (repositories.namespace_id = container_remote_repositories.namespace_id))
WHERE ((container_remote_repositories.namespace_id = $1::uuid) AND (container_remote_repositories.repository_id = $2::uuid)) AND ((repositories.namespace_id = $3::uuid) AND (((repositories.format IN ($4, $5)) AND (repositories.kind = $6)) AND (repositories.soft_deleted_at IS NULL)))
LIMIT $7;

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Limit  (cost=0.56..16.62 rows=1 width=135) (actual time=0.012..0.012 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Nested Loop  (cost=0.56..16.62 rows=1 width=135) (actual time=0.011..0.012 rows=1 loops=1)
         Buffers: shared hit=6
         ->  Index Scan using container_remote_repositories_p_namespace_id_repository_id_idx9 on container_remote_repositories_p09 container_remote_repositories  (cost=0.28..8.30 rows=1 width=167) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (repository_id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid))
               Buffers: shared hit=3
         ->  Index Scan using repositories_p09_pkey on repositories_p09 repositories  (cost=0.28..8.31 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: ((id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid) AND (namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid))
               Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (kind = 2))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=78
 Planning Time: 0.351 ms
 Execution Time: 0.020 ms

Timings: planning 0.351ms, execution 0.020ms.

datastore.insertContainerRemoteRepositoryStmt.EncNil

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

INSERT INTO public.container_remote_repositories (id, namespace_id, repository_id, url, wrapped_dek, ns_key_id, ns_key_version, encrypted_username, encrypted_password, cache_validity_hours)
VALUES ($1::uuid, $2::uuid, $3::uuid, $4::text, NULL::bytea, NULL::uuid, NULL::integer, NULL::bytea, NULL::bytea, DEFAULT);

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Insert on container_remote_repositories  (cost=0.00..0.03 rows=0 width=0) (actual time=0.052..0.052 rows=0 loops=1)
   Buffers: shared hit=7
   ->  Result  (cost=0.00..0.03 rows=1 width=306) (actual time=0.007..0.007 rows=1 loops=1)
 Planning Time: 0.017 ms
 Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p09: time=0.009 calls=1
 Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p09: time=0.083 calls=1
 Trigger for constraint fk_container_remote_repositories_ns_key_id_ns_enc_keys on container_remote_repositories_p09: time=0.001 calls=1
 Execution Time: 0.156 ms

Timings: planning 0.017ms, execution 0.156ms.

datastore.insertContainerRemoteRepositoryStmt.EncSet

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

INSERT INTO public.container_remote_repositories (id, namespace_id, repository_id, url, wrapped_dek, ns_key_id, ns_key_version, encrypted_username, encrypted_password, cache_validity_hours)
VALUES ($1::uuid, $2::uuid, $3::uuid, $4::text, $5::bytea, $6::uuid, $7::integer, $8::bytea, $9::bytea, DEFAULT);

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Insert on container_remote_repositories  (cost=0.00..0.03 rows=0 width=0) (actual time=0.053..0.053 rows=0 loops=1)
   Buffers: shared hit=7
   ->  Result  (cost=0.00..0.03 rows=1 width=306) (actual time=0.015..0.015 rows=1 loops=1)
 Planning Time: 0.020 ms
 Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p09: time=0.007 calls=1
 Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p09: time=0.070 calls=1
 Trigger for constraint fk_container_remote_repositories_ns_key_id_ns_enc_keys on container_remote_repositories_p09: time=0.026 calls=1
 Execution Time: 0.164 ms

Timings: planning 0.020ms, execution 0.164ms.

datastore.selectContainerRemoteLeftoverPlaintextPageStmt

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

SELECT container_remote_repositories.id AS "container_remote_repositories.id",
container_remote_repositories.namespace_id AS "container_remote_repositories.namespace_id",
container_remote_repositories.repository_id AS "container_remote_repositories.repository_id"
FROM public.container_remote_repositories
WHERE (container_remote_repositories.tmp_plaintext_username IS NOT NULL) OR (container_remote_repositories.tmp_plaintext_password IS NOT NULL)
ORDER BY container_remote_repositories.namespace_id, container_remote_repositories.repository_id
LIMIT $1;

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Limit  (cost=10.20..52.55 rows=10 width=48) (actual time=0.797..0.894 rows=10 loops=1)
   Buffers: shared hit=5905
   ->  Merge Append  (cost=10.20..1115.41 rows=261 width=48) (actual time=0.796..0.893 rows=10 loops=1)
         Sort Key: container_remote_repositories.namespace_id, container_remote_repositories.repository_id
         Buffers: shared hit=5905
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
         ->  Index Scan using container_remote_repositories_p_namespace_id_repository_id_idx9 on container_remote_repositories_p09 container_remote_repositories_10  (cost=0.28..591.41 rows=198 width=48) (actual time=0.033..0.126 rows=10 loops=1)
               Rows Removed by Filter: 680
               Buffers: shared hit=704
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=5139
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
               Buffers: shared hit=1
 Planning:
   Buffers: shared hit=439
 Planning Time: 2.050 ms
 Execution Time: 1.266 ms
[elided: per-partition scans that returned 0 rows, with their repeated Filter/Index Cond lines]

Timings: planning 2.050ms, execution 1.266ms.

datastore.updateContainerRemoteCredentialsStmt.EncNil

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

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,
tmp_plaintext_username = NULL::text,
tmp_plaintext_password = NULL::text
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)))
));

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Update on container_remote_repositories  (cost=0.56..16.62 rows=0 width=0) (actual time=0.045..0.045 rows=0 loops=1)
   Update on container_remote_repositories_p09 container_remote_repositories_1
   Buffers: shared hit=20
   ->  Nested Loop Semi Join  (cost=0.56..16.62 rows=1 width=200) (actual time=0.011..0.012 rows=1 loops=1)
         Buffers: shared hit=7
         ->  Index Scan using container_remote_repositories_p_namespace_id_repository_id_idx9 on container_remote_repositories_p09 container_remote_repositories_1  (cost=0.28..8.30 rows=1 width=42) (actual time=0.006..0.006 rows=1 loops=1)
               Index Cond: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (repository_id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid))
               Buffers: shared hit=4
         ->  Index Scan using repositories_p09_pkey on repositories_p09 repositories  (cost=0.28..8.31 rows=1 width=42) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: ((id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid) AND (namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid))
               Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (kind = 2))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.159 ms
 Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p09: time=0.006 calls=1
 Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p09: time=0.058 calls=1
 Execution Time: 0.127 ms

Timings: planning 0.159ms, execution 0.127ms.

datastore.updateContainerRemoteCredentialsStmt.EncSet

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

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,
tmp_plaintext_username = NULL::text,
tmp_plaintext_password = NULL::text
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)))
));

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Update on container_remote_repositories  (cost=0.56..16.62 rows=0 width=0) (actual time=0.033..0.033 rows=0 loops=1)
   Update on container_remote_repositories_p09 container_remote_repositories_1
   Buffers: shared hit=10
   ->  Nested Loop Semi Join  (cost=0.56..16.62 rows=1 width=200) (actual time=0.008..0.009 rows=1 loops=1)
         Buffers: shared hit=7
         ->  Index Scan using container_remote_repositories_p_namespace_id_repository_id_idx9 on container_remote_repositories_p09 container_remote_repositories_1  (cost=0.28..8.30 rows=1 width=42) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: ((namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid) AND (repository_id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid))
               Buffers: shared hit=4
         ->  Index Scan using repositories_p09_pkey on repositories_p09 repositories  (cost=0.28..8.31 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: ((id = 'f0e1ed63-d81b-7074-9b05-06be2c7b09e5'::uuid) AND (namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid))
               Filter: ((soft_deleted_at IS NULL) AND (format = ANY ('{0,3}'::integer[])) AND (kind = 2))
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.135 ms
 Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p09: time=0.004 calls=1
 Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p09: time=0.047 calls=1
 Trigger for constraint fk_container_remote_repositories_ns_key_id_ns_enc_keys on container_remote_repositories_p09: time=0.022 calls=1
 Execution Time: 0.124 ms

Timings: planning 0.135ms, execution 0.124ms.

datastore.updateContainerRemoteRepositoryStmt.CredentialSet

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

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,
tmp_plaintext_username = NULL::text,
tmp_plaintext_password = NULL::text
WHERE (container_remote_repositories.namespace_id = $6::uuid) AND (container_remote_repositories.id = $7::uuid);

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Update on container_remote_repositories  (cost=0.28..8.30 rows=0 width=0) (actual time=0.101..0.101 rows=0 loops=1)
   Update on container_remote_repositories_p09 container_remote_repositories_1
   Buffers: shared hit=19
   ->  Index Scan using container_remote_repositories_p09_pkey on container_remote_repositories_p09 container_remote_repositories_1  (cost=0.28..8.30 rows=1 width=190) (actual time=0.007..0.007 rows=1 loops=1)
         Index Cond: ((id = '2adbaeb6-fffa-782a-bc99-544f2dbffbbb'::uuid) AND (namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid))
         Buffers: shared hit=3
 Planning Time: 0.063 ms
 Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p09: time=0.010 calls=1
 Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p09: time=0.057 calls=1
 Trigger for constraint fk_container_remote_repositories_ns_key_id_ns_enc_keys on container_remote_repositories_p09: time=1.847 calls=1
 Execution Time: 2.060 ms

Timings: planning 0.063ms, execution 2.060ms.

datastore.updateContainerRemoteRepositoryStmt.URLChangeClears

Seed shape: namespaces=1, namespace_encryption_keys=1, repositories=5002, container_remote_repositories=5000 (100 with a leftover plaintext pair), maven_remote_repositories=5000, npm_remote_repositories=5000

Rendered SQL:

UPDATE public.container_remote_repositories
SET url = $1::text,
wrapped_dek = NULL::bytea,
ns_key_id = NULL::uuid,
ns_key_version = NULL::integer,
encrypted_username = NULL::bytea,
encrypted_password = NULL::bytea,
tmp_plaintext_username = NULL::text,
tmp_plaintext_password = NULL::text,
auth_url = NULL::text,
auth_status = $2,
last_health_status = $3,
last_health_checked_at = NULL::timestamp with time zone
WHERE (container_remote_repositories.namespace_id = $4::uuid) AND (container_remote_repositories.id = $5::uuid);

Plan (EXPLAIN (ANALYZE, BUFFERS)):

 Update on container_remote_repositories  (cost=0.28..8.30 rows=0 width=0) (actual time=0.039..0.039 rows=0 loops=1)
   Update on container_remote_repositories_p09 container_remote_repositories_1
   Buffers: shared hit=7
   ->  Index Scan using container_remote_repositories_p09_pkey on container_remote_repositories_p09 container_remote_repositories_1  (cost=0.28..8.30 rows=1 width=266) (actual time=0.006..0.006 rows=1 loops=1)
         Index Cond: ((id = '2adbaeb6-fffa-782a-bc99-544f2dbffbbb'::uuid) AND (namespace_id = '4be667c4-04ac-7c14-b654-f65e97e83352'::uuid))
         Buffers: shared hit=4
 Planning Time: 0.072 ms
 Trigger for constraint fk_container_remote_repositories_namespace_id_namespaces on container_remote_repositories_p09: time=0.008 calls=1
 Trigger for constraint fk_container_remote_repositories_repository_id_repositories on container_remote_repositories_p09: time=0.077 calls=1
 Execution Time: 0.145 ms
ROLLBACK

Timings: planning 0.072ms, execution 0.145ms.

Edited by Radamanthus Batnag

Merge request reports