+-----------------------------------------------------------------------------------------+
|                  Static Secrets Sprawl vs Zero-Static Architecture                      |
+-----------------------------------------------------------------------------------------+

LEGACY STATIC SECRETS MODEL (High Risk):
[Microservice Pod] ===(Shared Static Password: 'SuperSecret123')===> [PostgreSQL Database]
* Flaws: Credential never rotates; shared across 50 pods; leaked easily in logs!

ZERO-STATIC DYNAMIC SECRETS MODEL (2026 Standard):
[K8s Pod: 'payment-api'] ---> [Authenticates via Projected ServiceAccount OIDC Token]
                                     │
                                     ▼
+-----------------------------------------------------------------+
| HASHICORP VAULT CLUSTER:                                        |
| 1. Verifies Pod OIDC JWT against Kubernetes API Server.         |
| 2. Connects to PostgreSQL: Creates ephemeral user:              |
|    'CREATE USER v_payment_api_984 WITH PASSWORD 'temp_xyz...'   |
|    VALID UNTIL '2026-08-29 17:00:00';'                          |
| 3. Returns ephemeral credentials with a 60-Minute Lease!       |
+--------------------------------+--------------------------------+
                                 │
                                 ▼ (Connects to DB using unique temporary credentials)
[PostgreSQL Database: When lease expires, Vault automatically executes 'DROP USER'!]

+-----------------------------------------------------------------------------------------+
|                  Secretless Workload Identity Authentication Flow                       |
+-----------------------------------------------------------------------------------------+

[1. KUBERNETES KERNEL / POD RUNTIME]
  └── Mints ephemeral OIDC Projected ServiceAccount Token:
      { "sub": "system:serviceaccount:production:payment-service", "aud": "vault.internal" }
        │
        ▼ (Presents OIDC JWT to Vault at /v1/auth/kubernetes/login)
[2. HASHICORP VAULT SERVER]
  ├── Calls Kubernetes TokenReview API to verify cryptographic signature.
  └── Verifies Pod Namespace and ServiceAccount name match Vault Policy!
        │
        ▼
[3. ISSUES SHORT-LIVED VAULT CLIENT TOKEN (Zero Static Passwords Transmitted!)]

# terraform/vault_database_secrets.tf

# 1. Enable Database Secrets Engine in Vault
resource "vault_mount" "database" {
  path = "database"
  type = "database"
}

# 2. Configure PostgreSQL Connection
resource "vault_database_secret_backend_connection" "postgres" {
  backend       = vault_mount.database.path
  name          = "production_postgres"
  allowed_roles = ["payment_service_role", "reporting_service_role"]

  postgresql {
    connection_url = "postgres://{{username}}:{{password}}@postgres.internal.mojostudio.in:5432/production_vault?sslmode=verify-full"
    username       = "vault_admin_provisioner"
    password       = var.vault_admin_db_password
  }
}

# 3. Define Dynamic Role with 1-Hour TTL and Auto-Drop DDL
resource "vault_database_secret_backend_role" "payment_role" {
  backend             = vault_mount.database.path
  name                = "payment_service_role"
  db_name             = vault_database_secret_backend_connection.postgres.name
  default_ttl         = "3600"  # 1 Hour Default Lease
  max_ttl             = "14400" # 4 Hours Maximum Lease

  # SQL Executed by Vault when a Pod requests a secret:
  creation_statements = [
    "CREATE USER \"{{name}}\" WITH ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';",
    "GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO \"{{name}}\";",
    "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO \"{{name}}\";"
  ]

  # SQL Executed by Vault when the Lease expires:
  revocation_statements = [
    "REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"{{name}}\";",
    "REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM \"{{name}}\";",
    "DROP USER IF EXISTS \"{{name}}\";"
  ]
}

// app/database/dynamic_connector.go
package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	vault "github.com/hashicorp/vault/api"
	auth "github.com/hashicorp/vault/api/auth/kubernetes"
	_ "github.com/lib/pq"
)

type DynamicDBManager struct {
	vaultClient *vault.Client
	roleName    string
}

func NewDynamicDBManager(vaultAddr, roleName string) (*DynamicDBManager, error) {
	config := vault.DefaultConfig()
	config.Address = vaultAddr

	client, err := vault.NewClient(config)
	if err != nil {
		return nil, err
	}

	// 1. Authenticate via Kubernetes Projected OIDC ServiceAccount Token! (Secretless!)
	k8sAuth, err := auth.NewKubernetesAuth(
		roleName,
		auth.WithServiceAccountTokenPath("/var/run/secrets/kubernetes.io/serviceaccount/token"),
	)
	if err != nil {
		return nil, err
	}

	authInfo, err := client.Auth().Login(context.Background(), k8sAuth)
	if err != nil {
		return nil, fmt.Errorf("failed to login to Vault via K8s Workload Identity: %w", err)
	}

	log.Printf("✅ Authenticated with Vault via Workload Identity! Token Lease: %d seconds", authInfo.Auth.LeaseDuration)
	return &DynamicDBManager{vaultClient: client, roleName: roleName}, nil
}

// Fetch Dynamic Ephemeral Database Credentials on Pod Startup
func (m *DynamicDBManager) GetDatabaseConnection() (*sql.DB, error) {
	// 2. Request Just-In-Time Credentials from Vault Database Engine
	secret, err := m.vaultClient.Logical().Read("database/creds/payment_service_role")
	if err != nil {
		return nil, err
	}

	dbUser := secret.Data["username"].(string)
	dbPass := secret.Data["password"].(string)
	leaseID := secret.LeaseID
	leaseDuration := secret.LeaseDuration

	log.Printf("🔑 Issued Dynamic DB User: '%s' (Lease Duration: %d seconds, Lease ID: %s)", dbUser, leaseDuration, leaseID)

	// 3. Connect to PostgreSQL using Unique Ephemeral User
	dsn := fmt.Sprintf("postgres://%s:%s@postgres.internal.mojostudio.in:5432/production_vault?sslmode=verify-full", dbUser, dbPass)
	db, err := sql.Open("postgres", dsn)
	if err != nil {
		return nil, err
	}

	// 4. Start Background Goroutine to Auto-Renew Secret Lease
	go m.autoRenewLease(leaseID, leaseDuration)

	return db, nil
}

func (m *DynamicDBManager) autoRenewLease(leaseID string, duration int) {
	ticker := time.NewTicker(time.Duration(duration/2) * time.Second)
	defer ticker.Stop()

	for range ticker.C {
		_, err := m.vaultClient.Logical().Write(fmt.Sprintf("sys/leases/renew/%s", leaseID), nil)
		if err != nil {
			log.Printf("⚠️ Failed to renew database lease %s: %v", leaseID, err)
			return
		}
		log.Printf("🔄 Successfully renewed Vault database credential lease: %s", leaseID)
	}
}

       +-------------------------------------------------------------+
       |             Time to Revoke Compromised Credential           |
       +-------------------------------------------------------------+
 Static Shared Password (Manual Team Rotation)| ==================================== [48.0 Hours]
 Vault Dynamic Secrets Auto-TTL Revocation    | = [0.01 Hours / 30 Seconds] (5,700x Faster!)
                                              +-------------------------------------+
                                              0h      12h     24h     36h     48h