Skip to content

Commit df78882

Browse files
authored
Authenticate bundled PostgreSQL logins with separate pod certificates (agent-substrate#2266)
## Summary Part of: agent-substrate#2097 Bundled PostgreSQL currently accepts any client certificate signed by the pod-identity CA without binding it to the requested database username. Require certificate authentication with a common name matching the login, using a dedicated PostgreSQL client CA. - Add a pod-certificate signer restricted to `ate-system` pods using the `ate-api-server` service account. It issues only the two permitted identities: `substrate_owner_user` and `substrate_readwrite_user`. - Project separate certificates for the owner and runtime connections. Configure passwordless connection strings with server certificate verification, and retain the existing owner/runtime role grants. - Have `ate-setup` provision the CA and configure bundled PostgreSQL automatically, including on an existing installation. Disable passwords on the bundled application users and keep administrator access local to the PostgreSQL pod. - Reuse existing rotation support: new database connections from ateapi reread projected credentials and trust roots, while the bundled PostgreSQL TLS-reloader detects server-certificate or client-trust-bundle changes every 60 seconds and requests a configuration reload. External PostgreSQL and Cloud SQL continue to use their configured connection strings and authentication settings. Both application certificates are available to the API-server process; separate credentials preserve database role separation but do not isolate the roles from a compromised API server. ## Validation - Passed `go test -race ./cmd/podcertcontroller/... ./cmd/ate-setup/... ./cmd/ateapi/internal/store/atepg ./pkg/postgressetup ./internal/installdefaults` after rebasing onto updated main. - Signer tests cover both allowed identities, unauthorized requests, pod identity checks, certificate lifetimes, and trust bundles. - Docker integration tests use the bundled PostgreSQL configuration and verify successful passwordless connections, bootstrap/migrations, and runtime operations. They reject mismatched usernames, untrusted certificates, missing certificates, remote administrator access, plaintext connections, and runtime access to owner privileges.
1 parent 36d6d22 commit df78882

18 files changed

Lines changed: 1089 additions & 64 deletions

File tree

‎cmd/ate-setup/internal/steps/apienv_test.go‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -61,17 +61,17 @@ func TestCreateAPIServerEnvVarsPostgresIdentities(t *testing.T) {
6161
{
6262
name: "bundled identities",
6363
cfg: config.Config{PostgresReadWriteRole: config.DefaultPostgresReadWriteRole, PostgresOwnerRole: config.DefaultPostgresOwnerRole},
64-
readWriteDSN: bundledPostgresDSN(postgressetup.ReadWriteUser, postgressetup.ReadWritePassword),
65-
ownerDSN: bundledPostgresDSN(postgressetup.OwnerUser, postgressetup.OwnerPassword),
64+
readWriteDSN: bundledPostgresDSN(postgressetup.ReadWriteUser),
65+
ownerDSN: bundledPostgresDSN(postgressetup.OwnerUser),
6666
readWriteRole: config.DefaultPostgresReadWriteRole, ownerRole: config.DefaultPostgresOwnerRole,
6767
},
6868
{
6969
name: "size10 bundled identities",
7070
cfg: config.Config{
7171
ClusterSize: config.ClusterSizeSize10, PostgresReadWriteRole: config.DefaultPostgresReadWriteRole, PostgresOwnerRole: config.DefaultPostgresOwnerRole,
7272
},
73-
readWriteDSN: bundledPostgresDSN(postgressetup.ReadWriteUser, postgressetup.ReadWritePassword) + config.Size10PostgresPoolParams,
74-
ownerDSN: bundledPostgresDSN(postgressetup.OwnerUser, postgressetup.OwnerPassword),
73+
readWriteDSN: bundledPostgresDSN(postgressetup.ReadWriteUser) + config.Size10PostgresPoolParams,
74+
ownerDSN: bundledPostgresDSN(postgressetup.OwnerUser),
7575
readWriteRole: config.DefaultPostgresReadWriteRole, ownerRole: config.DefaultPostgresOwnerRole,
7676
},
7777
{

‎cmd/ate-setup/internal/steps/create.go‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ const (
3535
SecretActorIDCACerts = "actor-id-ca-certs"
3636
SecretServiceDNSCA = "service-dns-ca-pool"
3737
SecretPodIdentityCA = "pod-identity-ca-pool"
38+
SecretPostgresCA = "postgres-ca-pool"
3839
SecretEgressMITMCAPool = "egress-mitm-ca-pool"
3940
SecretAPIEnvVars = "ate-api-server-secret-envvars"
4041
SecretPostgresServerCA = "postgres-server-ca"
@@ -89,7 +90,7 @@ func (e *Env) EnsureEgressMITMCAPoolSecret(ctx context.Context) error {
8990
return e.ensureSecret(ctx, e.Namespace(), SecretEgressMITMCAPool, e.CreateEgressMITMCAPoolSecret)
9091
}
9192

92-
// CreatePodCertificateControllerCAs generates the two signer pools the
93+
// CreatePodCertificateControllerCAs generates the signer pools the
9394
// podcertificate controller issues from.
9495
func (e *Env) CreatePodCertificateControllerCAs(ctx context.Context) error {
9596
log.Step("create_podcertificate_controller_cas")
@@ -99,7 +100,10 @@ func (e *Env) CreatePodCertificateControllerCAs(ctx context.Context) error {
99100
if err := e.createCAPool(ctx, NamespacePodCert, SecretServiceDNSCA); err != nil {
100101
return err
101102
}
102-
return e.createCAPool(ctx, NamespacePodCert, SecretPodIdentityCA)
103+
if err := e.createCAPool(ctx, NamespacePodCert, SecretPodIdentityCA); err != nil {
104+
return err
105+
}
106+
return e.createCAPool(ctx, NamespacePodCert, SecretPostgresCA)
103107
}
104108

105109
// CreateActorIDCACertsSecret derives a certificate-only trust bundle from the

‎cmd/ate-setup/internal/steps/create_test.go‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
package steps
1616

1717
import (
18+
"bytes"
1819
"crypto/tls"
1920
"crypto/x509"
2021
"maps"
@@ -23,6 +24,7 @@ import (
2324

2425
"github.com/google/go-cmp/cmp"
2526
corev1 "k8s.io/api/core/v1"
27+
metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
2628

2729
"github.com/agent-substrate/substrate/internal/localca"
2830
"github.com/agent-substrate/substrate/internal/localjwtauthority"
@@ -194,3 +196,38 @@ func TestNewJWTPoolSecretData(t *testing.T) {
194196
})
195197
}
196198
}
199+
200+
// An existing install must gain the dedicated PostgreSQL CA without rotating
201+
// the signer pools it already uses.
202+
func TestEnsurePodCertificateCAs(t *testing.T) {
203+
e := &Env{Kube: fakeKube(t, &corev1.Namespace{ObjectMeta: metav1.ObjectMeta{Name: NamespacePodCert}})}
204+
if err := e.CreatePodCertificateControllerCAs(t.Context()); err != nil {
205+
t.Fatal(err)
206+
}
207+
before, err := e.Kube.Typed.CoreV1().Secrets(NamespacePodCert).Get(t.Context(), SecretServiceDNSCA, metav1.GetOptions{})
208+
if err != nil {
209+
t.Fatal(err)
210+
}
211+
if err := e.Kube.Typed.CoreV1().Secrets(NamespacePodCert).Delete(t.Context(), SecretPostgresCA, metav1.DeleteOptions{}); err != nil {
212+
t.Fatal(err)
213+
}
214+
if err := e.EnsurePodCertificateCAs(t.Context()); err != nil {
215+
t.Fatal(err)
216+
}
217+
for _, name := range []string{SecretServiceDNSCA, SecretPodIdentityCA, SecretPostgresCA} {
218+
secret, err := e.Kube.Typed.CoreV1().Secrets(NamespacePodCert).Get(t.Context(), name, metav1.GetOptions{})
219+
if err != nil {
220+
t.Fatal(err)
221+
}
222+
if name == SecretServiceDNSCA && !bytes.Equal(secret.Data["pool"], before.Data["pool"]) {
223+
t.Fatal("existing service DNS CA rotated")
224+
}
225+
pool, err := localca.Unmarshal(secret.Data["pool"])
226+
if err != nil {
227+
t.Fatal(err)
228+
}
229+
if _, err := pool.TrustAnchors(); err != nil {
230+
t.Fatal(err)
231+
}
232+
}
233+
}

‎cmd/ate-setup/internal/steps/postgres.go‎

Lines changed: 6 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -31,13 +31,10 @@ import (
3131
"github.com/agent-substrate/substrate/pkg/postgressetup"
3232
)
3333

34-
// The serving certificate is signed with Ed25519, which pgx cannot hash for SCRAM
35-
// channel binding. PostgreSQL rejects pgx's fallback as a downgrade, so
36-
// disable channel binding while retaining TLS and client-certificate checks.
37-
const postgresTLSParams = "sslmode=verify-full&sslrootcert=/run/servicedns.podcert.ate.dev/trust-bundle.pem&sslcert=/run/podidentity.podcert.ate.dev/credential-bundle.pem&sslkey=/run/podidentity.podcert.ate.dev/credential-bundle.pem&channel_binding=disable"
38-
39-
func bundledPostgresDSN(user, password string) string {
40-
return fmt.Sprintf("postgresql://%s:%[email protected]:5432/atepg?%s", user, password, postgresTLSParams)
34+
// Each bundled connection presents a certificate for its own login user.
35+
func bundledPostgresDSN(user string) string {
36+
bundle := "/run/postgres.podcert.ate.dev/" + user + ".pem"
37+
return fmt.Sprintf("postgresql://%[email protected]:5432/atepg?sslmode=verify-full&sslrootcert=/run/servicedns.podcert.ate.dev/trust-bundle.pem&sslcert=%s&sslkey=%s", user, bundle, bundle)
4138
}
4239

4340
func (e *Env) postgresReadWriteConnectionStrings() (string, string, error) {
@@ -47,11 +44,11 @@ func (e *Env) postgresReadWriteConnectionStrings() (string, string, error) {
4744
return "", "", fmt.Errorf("bundled PostgreSQL requires roles %q and %q and schema %q",
4845
config.DefaultPostgresReadWriteRole, config.DefaultPostgresOwnerRole, config.DefaultPostgresSchema)
4946
}
50-
readWriteDSN := bundledPostgresDSN(postgressetup.ReadWriteUser, postgressetup.ReadWritePassword)
47+
readWriteDSN := bundledPostgresDSN(postgressetup.ReadWriteUser)
5148
if e.Cfg.Size10() {
5249
readWriteDSN += config.Size10PostgresPoolParams
5350
}
54-
return readWriteDSN, bundledPostgresDSN(postgressetup.OwnerUser, postgressetup.OwnerPassword), nil
51+
return readWriteDSN, bundledPostgresDSN(postgressetup.OwnerUser), nil
5552
}
5653

5754
// setupBundledPostgres creates the fixed development identities before ateapi

‎cmd/ate-setup/internal/steps/postgres_test.go‎

Lines changed: 101 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -15,11 +15,13 @@
1515
package steps
1616

1717
import (
18+
"net/url"
1819
"os"
1920
"path/filepath"
2021
"strings"
2122
"testing"
2223

24+
appsv1 "k8s.io/api/apps/v1"
2325
corev1 "k8s.io/api/core/v1"
2426
metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
2527
"k8s.io/apimachinery/pkg/apis/meta/v1/unstructured"
@@ -106,7 +108,7 @@ func TestApplyPostgresSize10Overrides(t *testing.T) {
106108
}
107109
// And the TLS wiring the base's postgresql.conf carried must not be lost
108110
// by the replacement, or the server refuses connections.
109-
for _, want := range []string{"ssl = on", "hba_file = "} {
111+
for _, want := range []string{"ssl = on", "hba_file = ", "ssl_ca_file = '/run/postgres.podcert.ate.dev/trust-bundle.pem'"} {
110112
if !strings.Contains(data["postgresql.conf"], want) {
111113
t.Errorf("size10 postgresql.conf lacks %q", want)
112114
}
@@ -283,10 +285,10 @@ func TestBundledPostgresIdentityConfiguration(t *testing.T) {
283285
if err != nil {
284286
t.Fatal(err)
285287
}
286-
if readWrite != bundledPostgresDSN(postgressetup.ReadWriteUser, postgressetup.ReadWritePassword) {
288+
if readWrite != bundledPostgresDSN(postgressetup.ReadWriteUser) {
287289
t.Errorf("read/write DSN = %q", readWrite)
288290
}
289-
if owner != bundledPostgresDSN(postgressetup.OwnerUser, postgressetup.OwnerPassword) {
291+
if owner != bundledPostgresDSN(postgressetup.OwnerUser) {
290292
t.Errorf("owner DSN = %q", owner)
291293
}
292294
}
@@ -307,7 +309,7 @@ func TestBundledPostgresManifestAuthentication(t *testing.T) {
307309
for _, want := range []string{
308310
"local all all trust",
309311
"hostssl all postgres all reject",
310-
"hostssl atepg all all scram-sha-256 clientcert=verify-ca",
312+
"hostssl atepg substrate_owner_user,substrate_readwrite_user all cert",
311313
"name: POSTGRES_HOST_AUTH_METHOD",
312314
"value: trust",
313315
} {
@@ -316,3 +318,98 @@ func TestBundledPostgresManifestAuthentication(t *testing.T) {
316318
}
317319
}
318320
}
321+
322+
// Resolve the shipped DSN's client credential paths through the API pod's
323+
// projections, and the database's client trust bundle through its projection.
324+
func TestPostgresCertificateProjections(t *testing.T) {
325+
e := &Env{Cfg: &config.Config{Root: repoRoot(t)}}
326+
manifest, err := e.render(e.Cfg.Manifest("ate-api-server.yaml"))
327+
if err != nil {
328+
t.Fatal(err)
329+
}
330+
objs, err := kube.DecodeManifestBytes(manifest)
331+
if err != nil {
332+
t.Fatal(err)
333+
}
334+
deployment := findObject(objs, "Deployment", "ate-api-server")
335+
if deployment == nil {
336+
t.Fatal("missing API deployment")
337+
}
338+
var api appsv1.Deployment
339+
if err := runtime.DefaultUnstructuredConverter.FromUnstructured(deployment.Object, &api); err != nil {
340+
t.Fatal(err)
341+
}
342+
e.Cfg.PostgresReadWriteRole = config.DefaultPostgresReadWriteRole
343+
e.Cfg.PostgresOwnerRole = config.DefaultPostgresOwnerRole
344+
readWriteDSN, ownerDSN, err := e.postgresReadWriteConnectionStrings()
345+
if err != nil {
346+
t.Fatal(err)
347+
}
348+
paths := map[string]bool{}
349+
for _, tc := range []struct{ dsn, username string }{{readWriteDSN, postgressetup.ReadWriteUser}, {ownerDSN, postgressetup.OwnerUser}} {
350+
dsn, err := url.Parse(tc.dsn)
351+
if err != nil {
352+
t.Fatal(err)
353+
}
354+
_, hasPassword := dsn.User.Password()
355+
if dsn.User.Username() != tc.username || hasPassword || dsn.Query().Get("sslmode") != "verify-full" {
356+
t.Fatalf("DSN must use %s with verified TLS and no password", tc.username)
357+
}
358+
certPath := dsn.Query().Get("sslcert")
359+
if paths[certPath] {
360+
t.Fatal("owner and runtime share a certificate")
361+
}
362+
paths[certPath] = true
363+
if certPath != dsn.Query().Get("sslkey") || filepath.Base(certPath) != tc.username+".pem" {
364+
t.Fatal("DSN does not use the login's credential bundle")
365+
}
366+
for _, param := range []string{"sslcert", "sslkey"} {
367+
if signer := projectedFileSigner(t, api.Spec.Template.Spec, dsn.Query().Get(param), false); signer != "postgres.podcert.ate.dev/identity" {
368+
t.Fatalf("%s projected by %q", param, signer)
369+
}
370+
}
371+
if signer := projectedFileSigner(t, api.Spec.Template.Spec, dsn.Query().Get("sslrootcert"), true); signer != "servicedns.podcert.ate.dev/identity" {
372+
t.Fatalf("server trust projected by %q", signer)
373+
}
374+
}
375+
376+
for _, kind := range []bool{false, true} {
377+
objs := postgresObjects(t, kind)
378+
var database appsv1.StatefulSet
379+
if err := runtime.DefaultUnstructuredConverter.FromUnstructured(findObject(objs, "StatefulSet", "postgres").Object, &database); err != nil {
380+
t.Fatal(err)
381+
}
382+
if signer := projectedFileSigner(t, database.Spec.Template.Spec, "/run/postgres.podcert.ate.dev/trust-bundle.pem", true); signer != "postgres.podcert.ate.dev/identity" {
383+
t.Fatalf("database client trust projected by %q", signer)
384+
}
385+
}
386+
}
387+
388+
func projectedFileSigner(t *testing.T, pod corev1.PodSpec, path string, trust bool) string {
389+
t.Helper()
390+
for _, container := range pod.Containers {
391+
for _, mount := range container.VolumeMounts {
392+
if filepath.Dir(path) != mount.MountPath {
393+
continue
394+
}
395+
for _, volume := range pod.Volumes {
396+
if volume.Name != mount.Name || volume.Projected == nil {
397+
continue
398+
}
399+
for _, source := range volume.Projected.Sources {
400+
if trust && source.ClusterTrustBundle != nil && source.ClusterTrustBundle.Path == filepath.Base(path) {
401+
return *source.ClusterTrustBundle.SignerName
402+
}
403+
if !trust && source.PodCertificate != nil && source.PodCertificate.CredentialBundlePath == filepath.Base(path) {
404+
if got := source.PodCertificate.UserAnnotations["postgres.podcert.ate.dev/username"]; got != strings.TrimSuffix(filepath.Base(path), ".pem") {
405+
t.Fatalf("projection for %s requests username %q", path, got)
406+
}
407+
return source.PodCertificate.SignerName
408+
}
409+
}
410+
}
411+
}
412+
}
413+
t.Fatalf("no projected %s in pod", path)
414+
return ""
415+
}

‎cmd/ate-setup/internal/steps/prereqs.go‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ import (
2727
var trustBundleNames = []string{
2828
"podidentity.podcert.ate.dev:identity:primary-bundle",
2929
"servicedns.podcert.ate.dev:identity:primary-bundle",
30+
"postgres.podcert.ate.dev:identity:primary-bundle",
3031
}
3132

3233
// EnsureAPIServerPrerequisites creates the secrets and config ate-api-server
@@ -44,7 +45,7 @@ func (e *Env) EnsureAPIServerPrerequisites(ctx context.Context) error {
4445
if err := e.ensureSecret(ctx, e.Namespace(), SecretActorIDCACerts, e.CreateActorIDCACertsSecret); err != nil {
4546
return err
4647
}
47-
if err := e.ensureSecret(ctx, NamespacePodCert, SecretServiceDNSCA, e.CreatePodCertificateControllerCAs); err != nil {
48+
if err := e.EnsurePodCertificateCAs(ctx); err != nil {
4849
return err
4950
}
5051
// Always reconcile the PostgreSQL connection settings, so that a changed
@@ -63,10 +64,10 @@ func (e *Env) EnsureAPIServerPrerequisites(ctx context.Context) error {
6364
return nil
6465
}
6566

66-
// EnsurePodCertificateCAs creates the podcertificate signer pools if either is
67+
// EnsurePodCertificateCAs creates the podcertificate signer pools if any is
6768
// missing.
6869
func (e *Env) EnsurePodCertificateCAs(ctx context.Context) error {
69-
for _, name := range []string{SecretServiceDNSCA, SecretPodIdentityCA} {
70+
for _, name := range []string{SecretServiceDNSCA, SecretPodIdentityCA, SecretPostgresCA} {
7071
exists, err := e.Kube.SecretExists(ctx, NamespacePodCert, name)
7172
if err != nil {
7273
return err
@@ -79,7 +80,7 @@ func (e *Env) EnsurePodCertificateCAs(ctx context.Context) error {
7980
}
8081

8182
// WaitForPodCertificateTrustBundles blocks until the podcertificate controller
82-
// has published both identity bundles.
83+
// has published all signer bundles.
8384
func (e *Env) WaitForPodCertificateTrustBundles(ctx context.Context) error {
8485
log.Infof("Waiting for podcertificate ClusterTrustBundles to be ready...")
8586
err := e.Kube.WaitClusterTrustBundles(ctx, trustBundleNames, e.Cfg.WaitTimeout(TrustBundleTimeout))

0 commit comments

Comments
 (0)