OpenSSL

Allgemeines

openssl
Name	subject	Wert
EMAIL	emailAddress	deine.email@adresse.de
STAAT	C	DE
LAND	ST	Hessen
STADT	L	Frankfurt
ORGANISATION	O	Für Einigkeit und Recht und Freiheit
ABTEILUNG	OU	Wachsames Auge
Common Name	CN	FQDN oder Dein Name

nutzbare Verschlüsselungsalgorithmen anzeigen lassen:

Linux:

> openssl enc -list
> openssl list -cipher-commands
> openssl list -cipher-algorithms

FreeBSD:

> openssl enc -ciphers
> openssl list -cipher-commands
> openssl list -cipher-algorithms

aus einer PFX-Datei den Schlüssel und das Zertifikat im DER-Format exportieren

Um eine PFX-Datei in eine PEM-Datei zu konvertieren, die sowohl das Zertifikat als auch den privaten Schlüssel enthält, muss der folgende Befehl verwendet werden:

openssl pkcs12 -nodes -in filename.pfx -out filename.pem

Mit diesem Befehl können Sie den privaten Schlüssel aus einer PFX-Datei in eine PEM-Datei extrahieren:

openssl pkcs12 -nocerts -in filename.pfx -out filename.key

Mit diesem Befehl können Sie das Zertifikat aus einer PFX-Datei in eine PEM-Datei extrahieren:

openssl pkcs12 -clcerts -nokeys -in filename.pfx -out filename.crt

Passwort vom extrahierten privaten Schlüssel entfernen:

openssl rsa -in filename.key -out filename_nopw.key

SSL-Test auf eine MySQL-DB:

# openssl s_client -connect 127.0.0.1:3306 -tls1_2
# echo "STATUS;" | mysql --ssl-mode=required -h127.0.0.1 -P3306

MySQL

https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

per Skript

/root/bin/Creating_MySQL-SSL-Files.sh

#!/bin/bash
 
#------------------------------------------------------------------------------#
# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)
#------------------------------------------------------------------------------#
 
#cd /root/ || exit 1
 
if [ x == "x${1}" ] ; then
        #DER_NAME="openssl"
        DER_NAME="mysql.server.net"
else
        DER_NAME="${1}"
fi
 
ARBEITSVERZEICHNIS=$(pwd)/${DER_NAME}
rm -fr ${ARBEITSVERZEICHNIS}
mkdir -p ${ARBEITSVERZEICHNIS}
 
#
# Konfigurationsdatei erzeugen
#
#cp /etc/ssl/openssl.cnf ${ARBEITSVERZEICHNIS}/
#replace ./demoCA ${ARBEITSVERZEICHNIS} -- ${ARBEITSVERZEICHNIS}/openssl.cnf
#
cat <<EOF > ${ARBEITSVERZEICHNIS}/openssl.cnf
HOME                            = .
oid_section                     = new_oids
 
[ new_oids ]
tsa_policy1                     = 1.2.3.4.1
tsa_policy2                     = 1.2.3.4.5.6
tsa_policy3                     = 1.2.3.4.5.7
 
[ ca ]
default_ca                      = CA_default
 
[ CA_default ]
dir                             = ${ARBEITSVERZEICHNIS}
certs                           = \$dir/certs
crl_dir                         = \$dir/crl
database                        = \$dir/index.txt
new_certs_dir                   = \$dir/newcerts
certificate                     = \$dir/ca-cert.pem
serial                          = \$dir/serial
crlnumber                       = \$dir/crlnumber
crl                             = \$dir/crl.pem
private_key                     = \$dir/ca-key.pem
x509_extensions                 = usr_cert
name_opt                        = ca_default
cert_opt                        = ca_default
default_days                    = 3650
default_crl_days                = 30
default_md                      = default
preserve                        = no
policy                          = policy_match
 
[ policy_match ]
countryName                     = match
stateOrProvinceName             = match
organizationName                = match
organizationalUnitName          = optional
commonName                      = supplied
emailAddress                    = optional
 
[ policy_anything ]
countryName                     = optional
stateOrProvinceName             = optional
localityName                    = optional
organizationName                = optional
organizationalUnitName          = optional
commonName                      = supplied
emailAddress                    = optional
 
[ req ]
default_bits                    = 4096
default_keyfile                 = privkey.pem
distinguished_name              = req_distinguished_name
attributes                      = req_attributes
x509_extensions                 = v3_ca
string_mask                     = utf8only
 
[ req_distinguished_name ]
countryName                     = Country Name (2 letter code)
countryName_default             = DE
countryName_min                 = 2
countryName_max                 = 2
stateOrProvinceName             = State or Province Name (full name)
stateOrProvinceName_default     = Hessen
localityName                    = Locality Name (eg, city)
localityName_default            = Maintal
0.organizationName              = Organization Name (eg, company)
0.organizationName_default      = Für Einigkeit und Recht und Freiheit
organizationalUnitName          = Organizational Unit Name (eg, section)
organizationalUnitName_default  = Wachsames Auge
commonName                      = Common Name (e.g. server FQDN or YOUR name)
commonName_max                  = 64
commonName_default              = ${DER_NAME}
emailAddress                    = Email Address
emailAddress_max                = 64
emailAddress_default            = deine.email@adresse.de
 
[ req_attributes ]
challengePassword               = A challenge password
challengePassword_min           = 4
challengePassword_max           = 20
challengePassword_default       = abcd
unstructuredName                = An optional company name
unstructuredName_default        = Fernglas
 
[ usr_cert ]
basicConstraints                = CA:FALSE
nsComment                       = "OpenSSL Generated Certificate"
subjectKeyIdentifier            = hash
authorityKeyIdentifier          = keyid,issuer
 
[ v3_req ]
basicConstraints                = CA:FALSE
keyUsage                        = nonRepudiation, digitalSignature, keyEncipherment
 
[ v3_ca ]
subjectKeyIdentifier            = hash
authorityKeyIdentifier          = keyid:always,issuer
basicConstraints                = critical,CA:true
 
[ crl_ext ]
authorityKeyIdentifier          = keyid:always
 
[ proxy_cert_ext ]
basicConstraints                = CA:FALSE
nsComment                       = "OpenSSL Generated Certificate"
subjectKeyIdentifier            = hash
authorityKeyIdentifier          = keyid,issuer
proxyCertInfo                   = critical,language:id-ppl-anyLanguage,pathlen:3,policy:foo
 
[ tsa ]
default_tsa                     = tsa_config1
 
[ tsa_config1 ]
dir                             = /root/Creating_MySQL-SSL-Files/openssl
serial                          = \$dir/tsaserial
crypto_device                   = builtin
signer_cert                     = \$dir/tsacert.pem
certs                           = \$dir/cacert.pem
signer_key                      = \$dir/private/tsakey.pem
signer_digest                   = sha256
default_policy                  = tsa_policy1
other_policies                  = tsa_policy2, tsa_policy3
digests                         = sha256, sha384, sha512
accuracy                        = secs:1, millisecs:500, microsecs:100
clock_precision_digits          = 0
ordering                        = yes
tsa_name                        = yes
ess_cert_id_chain               = no
ess_cert_id_alg                 = sha512
EOF
# -config ${ARBEITSVERZEICHNIS}/openssl.cnf
 
touch ${ARBEITSVERZEICHNIS}/index.txt
echo "01" > ${ARBEITSVERZEICHNIS}/serial
 
DAYS="3650"
BITS="4096"
 
echo "
#
# 1. Generation of Certificate Authority(CA)
# => Common Name (eg, YOUR name) []:MySQL admin"
openssl req -new -x509 -nodes -days ${DAYS} -keyout ${ARBEITSVERZEICHNIS}/ca-key.pem -out ${ARBEITSVERZEICHNIS}/ca.pem -config ${ARBEITSVERZEICHNIS}/openssl.cnf
 
if [ ! -r ${ARBEITSVERZEICHNIS}/ca.pem ] ; then
        echo "Das erstellen von ${ARBEITSVERZEICHNIS}/ca.pem ist fehlgeschlagen."
        echo "ABBRUCH"
        exit 1
fi
 
echo "
#------------------------------------------------------------------------------#
# 2. Create server request and server key
# => Common Name (eg, YOUR name) []:MySQL server"
openssl req -newkey rsa:${BITS} -days ${DAYS} -nodes -keyout ${ARBEITSVERZEICHNIS}/server-key.pem -out ${ARBEITSVERZEICHNIS}/server-req.pem -config ${ARBEITSVERZEICHNIS}/openssl.cnf
 
echo "
#
# 3. Remove the passphrase from the server key
#"
openssl rsa -in ${ARBEITSVERZEICHNIS}/server-key.pem -out ${ARBEITSVERZEICHNIS}/server-key.pem
 
echo "
#
# 4. Sign server cert
#"
openssl x509 -req -in ${ARBEITSVERZEICHNIS}/server-req.pem -days ${DAYS} -CA ${ARBEITSVERZEICHNIS}/ca.pem -CAkey ${ARBEITSVERZEICHNIS}/ca-key.pem -set_serial 01 -out ${ARBEITSVERZEICHNIS}/server-cert.pem
 
echo "
#------------------------------------------------------------------------------#
# 5. Create client request and client key
# => Common Name (eg, YOUR name) []:MySQL user"
openssl req -newkey rsa:${BITS} -days ${DAYS} -nodes -keyout ${ARBEITSVERZEICHNIS}/client-key.pem -out ${ARBEITSVERZEICHNIS}/client-req.pem -config ${ARBEITSVERZEICHNIS}/openssl.cnf
 
echo "
#
# 6. Remove the passphrase from the key
#"
openssl rsa -in ${ARBEITSVERZEICHNIS}/client-key.pem -out ${ARBEITSVERZEICHNIS}/client-key.pem
 
echo "
#
# 7. Sign client cert
#"
openssl x509 -req -in ${ARBEITSVERZEICHNIS}/client-req.pem -days ${DAYS} -CA ${ARBEITSVERZEICHNIS}/ca.pem -CAkey ${ARBEITSVERZEICHNIS}/ca-key.pem -set_serial 01 -out ${ARBEITSVERZEICHNIS}/client-cert.pem
 
echo "
#
# 8. Create a my.cnf file that you can use to test the certificates
#"
cat <<EOF > ${ARBEITSVERZEICHNIS}/my.cnf
[client]
ssl-ca=${ARBEITSVERZEICHNIS}/ca.pem
ssl-cert=${ARBEITSVERZEICHNIS}/client-cert.pem
ssl-key=${ARBEITSVERZEICHNIS}/client-key.pem
 
[mysqld]
ssl_ca=${ARBEITSVERZEICHNIS}/ca.pem
ssl_cert=${ARBEITSVERZEICHNIS}/server-cert.pem
ssl_key=${ARBEITSVERZEICHNIS}/server-key.pem
EOF

</hidden>

> /root/bin/Creating_MySQL-SSL-Files.sh mysql.server.net
...
#
# 4. Sign server cert
#
Signature ok
subject=C = DE, ST = Hessen, L = Maintal, O = Für Einigkeit und Recht und Freiheit, OU = Wachsames Auge, CN = mysql.server.net, emailAddress = deine.email@adresse.de
Getting CA Private Key

...
#
# 7. Sign client cert
#
Signature ok
subject=C = DE, ST = Hessen, L = Maintal, O = Für Einigkeit und Recht und Freiheit, OU = Wachsames Auge, CN = mysql.server.net, emailAddress = deine.email@adresse.de
Getting CA Private Key

...
> find * -type f | grep -Fi .pem
mysql.server.net/ca-key.pem
mysql.server.net/ca.pem
mysql.server.net/client-key.pem
mysql.server.net/client-req.pem
mysql.server.net/client-cert.pem
mysql.server.net/server-key.pem
mysql.server.net/server-req.pem
mysql.server.net/server-cert.pem

von Hand

Create clean environment

rm -rf newcerts
mkdir newcerts && cd newcerts

Create CA certificate

openssl req -new -x509 -nodes -days 3650 -keyout ca-key.pem -out ca.pem

Create server certificate, remove passphrase, and sign it server-cert.pem = public key, server-key.pem = private key

openssl req -newkey rsa:4096 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Create client certificate, remove passphrase, and sign it client-cert.pem = public key, client-key.pem = private key

openssl req -newkey rsa:4096 -days 3650 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

CLI-Test

META-Daten des Zertifikates anzeigen:

> openssl x509 -text -in /var/lib/mysql/ca.pem
> openssl x509 -text -in /var/lib/mysql/server-cert.pem
> openssl x509 -text -in /var/lib/mysql/client-cert.pem

ohne Zertifikatsprüfung:

> mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED

Einweg-Zertifikatsprüfung (One-Way TLS):

> mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED --ssl-mode=VERIFY_CA --ssl-ca=/var/lib/mysql/ca.pem

Zweiwege-Zertifikatsprüfung (Two-Way TLS):

> mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED --ssl-mode=VERIFY_CA --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem