When implementing secure PostgreSQL connections, certificate creation forms the foundation of your PKI infrastructure. Whether you’re setting up a development environment or deploying enterprise-grade systems, understanding proper certificate creation practices is crucial. This guide explores certificate creation from the simple OpenSSL approach to enterprise-grade practices employed by major financial institutions like Credit Suisse/UBS and media companies like BBC.

Related Reading: For implementing DN-based certificate authentication in PostgreSQL, see our DN Authentication guide.

Basic Certificate Creation (Development Approach)

The fundamental approach to certificate creation uses OpenSSL commands to establish a simple PKI hierarchy suitable for development and testing environments.

Root CA Creation

# Generate CA private key
openssl genrsa -out ca.key 4096

# Create CA certificate with proper constraints
openssl req -new -x509 -days 365 -key ca.key -out ca.crt -config ca.conf -extensions v3_ca

The CA configuration includes essential constraints:

[v3_ca]
basicConstraints = CA:true
keyUsage = keyCertSign, cRLSign
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer

Client Certificate Creation

# Generate client private key
openssl genrsa -out redgatemonitor.key 4096

# Create certificate signing request
openssl req -new -key redgatemonitor.key -out redgatemonitor.csr -config client.conf

# Sign with CA
openssl x509 -req -days 365 -in redgatemonitor.csr -CA ca.crt -CAkey ca.key -out redgatemonitor.crt

This creates certificates with the DN structure: CN=alien,OU=Client,O=PostgreSQLCluster,L=City,ST=State,C=US

Enterprise Certificate Creation Practices

1. PKI Hierarchy Design (Credit Suisse/UBS Model)

Major financial institutions like Credit Suisse (now part of UBS) implement multi-tier PKI hierarchies:

Root CA (Offline, Air-gapped)
├── Policy CA (Intermediate, Offline)
│   ├── Issuing CA - Employee Certificates
│   ├── Issuing CA - Server Certificates  
│   └── Issuing CA - Application Certificates
└── Policy CA (Intermediate, Online)
    ├── Issuing CA - External Partner Certificates
    └── Issuing CA - Temporary Access Certificates

Key Characteristics:

  • Root CA: Kept offline in HSM, only activated for intermediate signing
  • Policy CAs: Define certificate policies and constraints
  • Issuing CAs: Handle day-to-day certificate issuance
  • Cross-Certification: Multiple root CAs for redundancy

2. Certificate Templates and Policies

Enterprise environments use certificate templates defining:

# Employee Certificate Template
[employee_cert_template]
keyUsage = digitalSignature, keyEncipherment, nonRepudiation
extendedKeyUsage = clientAuth, emailProtection
certificatePolicies = 1.3.6.1.4.1.311.21.8.1234567.1.2.3
subjectAltName = email:$upn, otherName:1.3.6.1.4.1.311.20.2.3;UTF8:$upn
validityPeriod = 2years
keySize = 2048

3. Hardware Security Modules (HSMs)

Companies like Credit Suisse use FIPS 140-2 Level 3+ HSMs:

  • Root CA Keys: Stored in offline HSMs with ceremony-based activation
  • Issuing CA Keys: Online HSMs with role-based access controls
  • High-Value Client Keys: Smart cards or USB tokens (FIPS 140-2 Level 2+)
# Example HSM-backed certificate generation
pkcs11-tool --module /usr/lib/pkcs11/opensc-pkcs11.so \
            --login --pin $PIN \
            --keypairgen --key-type rsa:2048 \
            --id 01 --label "Database Client Key"

4. Certificate Authority Integration (BBC Model)

Large media organizations like BBC integrate with enterprise systems:

Active Directory Certificate Services (ADCS)

# Auto-enrollment via Group Policy
certlm.msc -> Certificate Templates -> Duplicate Template
# Configure for PostgreSQL database authentication
Template Name: PostgreSQL_Database_Client
Subject Name: CN=$Common_Name, OU=$Department, O=BBC, C=UK
Key Usage: Digital Signature, Key Encipherment
Enhanced Key Usage: Client Authentication
Validity Period: 1 year

SCEP Integration for Automated Enrollment

# SCEP-based certificate enrollment
sscep getca -u http://ca.bbc.co.uk/certsrv/mscep/mscep.dll -c ca.crt
sscep enroll -u http://ca.bbc.co.uk/certsrv/mscep/mscep.dll \
             -k client.key -r client.csr -c ca.crt -l client.crt

5. Enterprise Certificate Lifecycle Management

Automated Certificate Management Environment (ACME)

# Modern enterprises use ACME for automation
certbot certonly --standalone \
  --server https://ca.company.com/acme \
  --email admin@company.com \
  --cert-name postgresql-client \
  --domains client.company.com

HashiCorp Vault PKI Engine

# Enterprise-grade certificate automation
vault write pki/issue/database-client \
  common_name="postgresql.company.com" \
  ou="Database Services" \
  organization="Company Name" \
  ttl="8760h"

Certificate Creation Best Practices for PostgreSQL

1. Key Generation Standards

# Use appropriate key sizes (RSA 2048+ or ECDSA P-256+)
openssl genrsa -out client.key 2048
# Or use ECDSA for better performance
openssl ecparam -genkey -name prime256v1 -out client.key

2. Certificate Validity Periods

  • Development: 1 year (like repository example)
  • Production: 90 days to 1 year maximum
  • High-Security: 30-90 days with automated renewal

3. Distinguished Name Standardization

# Enterprise DN structure
[req_distinguished_name]
C = US
ST = New York
L = New York City
O = Company Name
OU = Database Services
CN = postgresql-client-prod-01
emailAddress = dbadmin@company.com

4. Certificate Extensions for PostgreSQL

[v3_req]
basicConstraints = CA:FALSE
keyUsage = digitalSignature, keyEncipherment, nonRepudiation
extendedKeyUsage = clientAuth
subjectAltName = @alt_names
certificatePolicies = 1.2.3.4.5.6.7.8.9.10

[alt_names]
DNS.1 = postgresql-client.company.com
email.1 = service-account@company.com

Certificate Distribution and Management

1. Enterprise Certificate Stores

# Windows Certificate Store integration
certutil -addstore "My" client.pfx
certutil -addstore "Root" ca.crt

# Linux system certificate store
cp ca.crt /etc/ssl/certs/company-ca.crt
update-ca-certificates

2. Automated Distribution Systems

# Ansible playbook for certificate distribution
- name: Deploy PostgreSQL client certificates
  copy:
    src: "{{ item }}"
    dest: "/etc/postgresql/ssl/"
    mode: '0600'
    owner: postgres
  with_items:
    - client.crt
    - client.key
    - ca.crt

3. Certificate Renewal Automation

#!/bin/bash
# Enterprise certificate renewal script
CERT_PATH="/etc/postgresql/ssl/client.crt"
EXPIRY_DAYS=$(openssl x509 -in $CERT_PATH -noout -dates | grep notAfter | cut -d= -f2)
DAYS_LEFT=$(( ($(date -d "$EXPIRY_DAYS" +%s) - $(date +%s)) / 86400 ))

if [ $DAYS_LEFT -lt 30 ]; then
    # Trigger certificate renewal via enterprise CA API
    curl -X POST https://ca.company.com/api/renew \
         -H "Authorization: Bearer $API_TOKEN" \
         -d '{"common_name": "postgresql-client"}'
fi

Security Considerations in Certificate Creation

1. Private Key Protection

# Generate keys with proper entropy
openssl genrsa -out client.key 2048
chmod 600 client.key

# For high-security environments, use hardware-backed keys
openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 \
                -pkeyopt rsa_keygen_pubexp:65537 \
                -provider pkcs11 -out client.key

2. Certificate Transparency and Monitoring

# Monitor certificate issuance via CT logs
curl -s "https://crt.sh/?q=%.company.com&output=json" | jq '.[].name_value'

3. Certificate Revocation Infrastructure

# Generate Certificate Revocation List
openssl ca -gencrl -out ca.crl -config ca.conf

# OCSP responder setup (enterprise environments)
openssl ocsp -port 8080 -text -sha256 \
             -index ca-database.txt \
             -CA ca.crt -rkey ca.key \
             -rsigner ca.crt

Integration with PostgreSQL Certificate Creation

Repository Enhancement Suggestions

The current repository could be enhanced with enterprise practices:

# Enhanced CA generation with policy constraints
cat > enhanced_ca.conf << 'EOF'
[req]
distinguished_name = req_distinguished_name
req_extensions = v3_ca
prompt = no

[req_distinguished_name]
C = US
ST = State
L = City
O = PostgreSQLCluster
OU = Certificate Authority
CN = PostgreSQL-Cluster-Root-CA

[v3_ca]
basicConstraints = critical,CA:true,pathlen:2
keyUsage = critical,keyCertSign,cRLSign
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer
certificatePolicies = 1.2.3.4.5.6.7.8.9.10
crlDistributionPoints = URI:http://ca.postgresql-cluster.local/ca.crl
EOF

Client Certificate Templates

# Template for different client types
generate_client_cert() {
    local CLIENT_TYPE=$1
    local CLIENT_NAME=$2
    
    case $CLIENT_TYPE in
        "application")
            OU="Applications"
            VALIDITY_DAYS=90
            ;;
        "service")
            OU="Services" 
            VALIDITY_DAYS=365
            ;;
        "user")
            OU="Users"
            VALIDITY_DAYS=30
            ;;
    esac
    
    # Generate with appropriate template
    openssl req -new -key $CLIENT_NAME.key -out $CLIENT_NAME.csr \
                -subj "/C=US/ST=State/L=City/O=PostgreSQLCluster/OU=$OU/CN=$CLIENT_NAME"
}

Enterprise Certificate Creation Checklist

  • ✅ Key Size Standards: RSA 2048+ or ECDSA P-256+ for all certificates
  • ✅ Validity Periods: Appropriate certificate lifetimes based on security requirements
  • ✅ DN Standardization: Consistent Distinguished Name structure across organization
  • ✅ Certificate Extensions: Proper key usage and extended key usage settings
  • ✅ PKI Hierarchy: Multi-tier CA structure with offline root CA
  • ✅ HSM Integration: Hardware-backed key storage for high-value certificates
  • ✅ Certificate Templates: Standardized templates for different certificate types
  • ✅ Automated Enrollment: SCEP, ACME, or API-based certificate issuance
  • ✅ Distribution Systems: Automated certificate deployment and management
  • ✅ Renewal Automation: Proactive certificate renewal before expiration
  • ✅ Revocation Infrastructure: CRL or OCSP implementation for certificate revocation
  • ✅ Monitoring: Certificate transparency logging and expiration monitoring
  • ✅ Compliance: FIPS 140-2, Common Criteria, or other regulatory requirements

Conclusion

Proper certificate creation forms the backbone of secure PostgreSQL communications. While development environments can use simple OpenSSL-based approaches, production deployments—especially in enterprise environments—require sophisticated PKI hierarchies, automated certificate lifecycle management, and hardware-backed security modules.

The practices employed by major financial institutions and media companies demonstrate that certificate creation is not just about generating keys and signing certificates—it’s about building a comprehensive security infrastructure that supports business operations while maintaining the highest security standards.

Whether you’re implementing basic certificate authentication or enterprise-grade PKI systems, the key is to start with solid foundations and progressively enhance your certificate management capabilities as your security requirements evolve.

Next Steps: Once you have your certificates created, learn how to implement flexible authentication mapping with our PostgreSQL DN Authentication guide.