openssl genrsa -out client.key 2048

openssl req -new -key client.key -out client.csr -config client.cnf

openssl x509 -req -in client.csr \
  -CA ca.crt -CAkey ca.key \
  -CAcreateserial \
  -out client.crt -days 3650 \
  -extensions v3_req -extfile client.cnf

chmod 600 *.key
chmod 644 *.crt

docker inspect ba75551fead2 | grep -A 8 Mounts
        "Mounts": [
            {
                "Type": "bind",
                "Source": "/mnt/postgres",
                "Destination": "/var/lib/postgresql/data",
                "Mode": "rw",
                "RW": true,
                "Propagation": "rprivate"
            }

cp server.crt server.key ca.crt /mnt/postgres

# 如果你是宿主机部署并且给postgres服务设置了单独用户，那么需要下面步骤，没有则跳过
# sudo chown postgres:postgres /mnt/postgres/server.{crt,key}

sudo chmod 600 /mnt/postgres/server.key

sudo vim /mnt/postgres/postgresql.conf
# 添加或修改以下配置,以下配置应该存在文件中直接打开对应注释即可
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

sudo vim /mnt/postgres/pg_hba.conf
# 把下面这行注释掉
# host all all all scram-sha-256
# 内网不加密，使用密码
host    all  all  10.0.0.0/8        scram-sha-256
host    all  all  172.16.0.0/12     scram-sha-256
host    all  all  192.168.0.0/16    scram-sha-256
# 其他来源均需要使用证书验证
hostssl all all 0.0.0.0/0 cert clientcert=verify-full

# Linux systemd
sudo systemctl restart postgresql
# docker
docker restart postgresql

psql "host=localhost port=5432 dbname=your_db user=your_user sslmode=verify-full sslrootcert=/path/to/ca.crt sslcert=/path/to/client.crt sslkey=/path/to/client.key"

scp -r [email protected]:/home/user/pg-certs .

DB_SSL_MODE=verify-full
DB_SSL_ROOT_CERT=./pg-certs/ca.crt
DB_SSL_CERT=./pg-certs/client.crt
DB_SSL_KEY=./pg-certs/client.key

type PostgresDB struct {
    *sqlx.DB
}

func NewPostgresDB(c *config.Config) (*PostgresDB, error) {
    // Build secure DSN with SSL/TLS support. Default to 'require' if not specified
    sslMode := strings.TrimSpace(c.Postgres.SSLMode)
    if sslMode == "" {
        sslMode = "require"
    }

    // Base DSN
    dataSourceName := fmt.Sprintf(
        "postgres://%s:%s@%s:%s/%s?sslmode=%s",
        c.Postgres.Username, c.Postgres.Password, c.Postgres.Host, c.Postgres.Port, c.Postgres.Dbname, sslMode,
    )

    // Optional TLS parameters
    if v := strings.TrimSpace(c.Postgres.SSLRootCert); v != "" {
        dataSourceName += fmt.Sprintf("&sslrootcert=%s", v)
    }
    if v := strings.TrimSpace(c.Postgres.SSLCert); v != "" {
        dataSourceName += fmt.Sprintf("&sslcert=%s", v)
    }
    if v := strings.TrimSpace(c.Postgres.SSLKey); v != "" {
        dataSourceName += fmt.Sprintf("&sslkey=%s", v)
    }

    db, err := sqlx.Connect("postgres", dataSourceName)
    if err != nil {
        zap.S().Error(err)
        return nil, err
    }

    // Configure connection pool with safe defaults
    maxOpenConns := c.Postgres.MaxOpenConns
    if maxOpenConns <= 0 {
        maxOpenConns = 25 // Default: limit max open connections to prevent resource exhaustion
    }
    db.SetMaxOpenConns(maxOpenConns)

    maxIdleConns := c.Postgres.MaxIdleConns
    if maxIdleConns <= 0 {
        maxIdleConns = 10 // Default: keep some idle connections to reduce latency
    }
    db.SetMaxIdleConns(maxIdleConns)

    connMaxLifetime := c.Postgres.ConnMaxLifetime
    if connMaxLifetime <= 0 {
        connMaxLifetime = 10 // Default: 10 minutes
    }
    db.SetConnMaxLifetime(time.Duration(connMaxLifetime) * time.Minute)

    connMaxIdleTime := c.Postgres.ConnMaxIdleTime
    if connMaxIdleTime <= 0 {
        connMaxIdleTime = 5 // Default: 5 minutes
    }
    db.SetConnMaxIdleTime(time.Duration(connMaxIdleTime) * time.Minute)

    return &PostgresDB{db}, nil
}