Posts Tagged ‘pem’

Display Content of SSL certificate .pem file with openssl command

Thursday, October 11th, 2018

display-content-of-pem-der-and-scr-file-how-to-view-pem-file-linux

If you have generated a .pem formatted SSL certificate or you have multiple .pem SSL certificates and you're not sure which .pem file is generated for which domain / subdomain it is useful to Display content of SSL Certificate .PEM file with openssl command.

Viewing certificate's content is also very useful if you have hosted multiple websites hosted on a server and you want to check which of the SSLs assigned in the Virtualhosts has Expired (for example if you have domains that expire in short term period (365 days).


1. How to Display Content of SSL certificate .pem file?

 

root@pcfreak:~# openssl x509 -in cert.pem -text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            04:d1:ad:55:91:f3:f9:ef:3e:53:ea:2c:3a:f4:5f:e6:ce:c1
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: C = US, O = Let's Encrypt, CN = Let's Encrypt Authority X3
        Validity
            Not Before: Oct 10 17:49:34 2018 GMT
            Not After : Jan  8 17:49:34 2019 GMT
        Subject: CN = mail.pc-freak.net

        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:bb:b0:c9:1c:83:82:61:47:f9:c2:73:60:c0:48:
                    e6:0c:f2:a1:ff:db:ae:f1:84:17:14:5d:fc:a3:b2:
                    e4:00:3a:d1:85:42:90:da:41:a9:e9:a8:af:20:3d:
                    12:ef:8e:ca:61:a1:71:f2:cc:43:bf:40:0d:fa:08:
                    7d:d9:61:2b:ea:5d:30:e0:52:43:db:18:30:92:0c:
                    2c:ce:87:93:84:ea:91:61:b7:70:db:11:7c:b6:a4:
                    33:de:d8:3f:d6:61:47:42:f2:36:12:7f:3d:e3:f7:
                    5b:11:3e:1c:f0:af:96:cd:61:8a:1a:a0:f0:b5:23:
                    65:73:b6:b4:9c:19:a7:09:dd:43:96:37:ac:48:fc:
                    21:07:02:52:67:26:2c:81:24:f4:d7:10:e6:f4:12:
                    69:53:ef:91:2a:15:6a:21:06:22:ea:fe:31:38:82:
                    b4:5a:b5:9b:67:90:16:b8:31:e8:27:38:f2:41:b9:
                    19:02:8f:c7:6e:e1:2c:84:75:19:6d:bb:30:3b:d2:
                    02:f0:65:f1:76:82:15:9c:ce:31:3a:d4:7c:83:ca:
                    d1:f9:e1:b7:76:f6:78:93:47:d2:00:f9:63:aa:94:
                    41:d4:78:d0:ee:bc:e6:e9:14:14:e4:ae:54:31:88:
                    f8:58:8d:7b:3e:9f:87:5c:f2:04:e5:07:e0:4c:9a:
                    81:eb
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication, TLS Web Client Authentication
            X509v3 Basic Constraints: critical
                CA:FALSE
            X509v3 Subject Key Identifier:
                DB:AB:81:E3:14:5F:6D:BE:B4:78:7B:5E:7D:FB:66:BF:56:37:C5:1D
            X509v3 Authority Key Identifier:
                keyid:A8:4A:6A:63:04:7D:DD:BA:E6:D1:39:B7:A6:45:65:EF:F3:A8:EC:A1

 

            Authority Information Access:
                OCSP – URI:http://ocsp.int-x3.letsencrypt.org
                CA Issuers – URI:http://cert.int-x3.letsencrypt.org/

            X509v3 Subject Alternative Name:
                DNS:mail.pc-freak.net
            X509v3 Certificate Policies:
                Policy: 2.23.140.1.2.1
                Policy: 1.3.6.1.4.1.44947.1.1.1
                  CPS: http://cps.letsencrypt.org
                  User Notice:
                    Explicit Text: This Certificate may only be relied upon by Relying Parties and only in accordance with the Certificate Policy found at https://letsencrypt.org/repository/

            CT Precertificate SCTs:
                Signed Certificate Timestamp:
                    Version   : v1 (0x0)
                    Log ID    : E2:69:4B:AE:26:E8:E9:40:09:E8:86:1B:B6:3B:83:D4:
                                3E:E7:FE:74:88:FB:A4:8F:28:93:01:9D:DD:F1:DB:FE
                    Timestamp : Oct 10 18:49:34.453 2018 GMT
                    Extensions: none
                    Signature : ecdsa-with-SHA256
                                30:46:02:21:00:D6:DE:47:AD:D2:32:BE:BE:DD:B3:EB:
                                EE:84:9E:02:8A:4F:33:E2:63:21:D5:F7:4D:47:82:92:
                                AB:B9:0A:49:62:02:21:00:E8:7D:17:81:32:E3:4F:CF:
                                2D:79:8C:97:46:E1:EF:5E:99:F4:8A:8B:B5:6D:23:5F:
                                05:84:E2:14:6A:56:8E:A0
                Signed Certificate Timestamp:
                    Version   : v1 (0x0)
                    Log ID    : 29:3C:51:96:54:C8:39:65:BA:AA:50:FC:58:07:D4:B7:
                                6F:BF:58:7A:29:72:DC:A4:C3:0C:F4:E5:45:47:F4:78
                    Timestamp : Oct 10 18:49:34.451 2018 GMT
                    Extensions: none
                    Signature : ecdsa-with-SHA256
                                30:44:02:20:6C:8E:E7:E2:70:AD:33:A6:5C:E0:89:84:
                                FB:0B:F6:E1:5C:05:06:0A:A8:DB:8B:1C:7A:D0:52:99:
                                5F:3F:A2:64:02:20:4B:CD:0B:E7:A0:27:04:31:19:18:
                                58:99:51:73:49:6B:77:25:A7:E7:5B:10:8C:BD:ED:54:
                                03:DD:40:E4:2D:31
    Signature Algorithm: sha256WithRSAEncryption
         9c:86:b3:34:64:af:ac:9d:c4:d3:a7:cc:fc:8a:32:18:75:95:
         95:47:9b:9c:3c:0e:3b:61:f9:88:61:38:1a:a6:92:69:3d:14:
         6a:53:13:14:65:e6:ca:fa:b9:8e:48:c9:d4:73:f6:e4:74:8a:
         1f:2b:f2:14:86:f1:18:55:26:1b:a0:97:89:15:0b:62:c6:2b:
         27:81:6f:60:af:55:68:b3:2c:5b:10:56:a2:7d:28:cb:8e:fc:
         f0:21:65:78:9b:3a:52:d3:9d:27:ff:d7:24:95:de:0f:d8:3d:
         a2:43:6e:fc:a5:2d:f2:ad:37:e9:ea:db:b5:75:b8:7c:ad:23:
         45:1d:bd:fe:4e:36:c7:f4:e2:3d:47:c9:06:fc:cb:75:ba:d4:
         0a:90:17:ea:e1:7f:49:e6:68:27:97:8a:70:c7:50:e9:19:4a:
         8a:21:18:26:79:a3:61:ff:1b:26:9e:fe:85:8f:20:ed:c6:4d:
         c1:0e:04:21:a8:05:d4:29:69:99:53:63:81:c7:d5:58:71:df:
         02:b5:94:c9:36:48:c9:35:80:ab:71:78:d9:12:f6:f5:10:25:
         3d:38:c5:40:75:25:b1:95:18:d8:1c:96:f1:c6:1a:d2:c4:99:
         f5:01:2e:f4:e1:4a:1f:10:42:0e:34:ed:92:8e:53:9f:c2:7b:
         11:51:78:6a
—–BEGIN CERTIFICATE—–
MIIGDTCCBPWgAwIBAgISBNGtVZHz+e8+U+osOvRf5s7BMA0GCSqGSIb3DQEBCwUA
MEoxCzAJBgNVBAYTAlVTMRYwFAYDVQQKEw1MZXQncyBFbmNyeXB0MSMwIQYDVQQD
ExpMZXQncyBFbmNyeXB0IEF1dGhvcml0eSBYMzAeFw0xODEwMTAxNzQ5MzRaFw0x
OTAxMDgxNzQ5MzRaMBwxGjAYBgNVBAMTEW1haWwucGMtZnJlYWsubmV0MIIBIjAN
BgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAu7DJHIOCYUf5wnNgwEjmDPKh/9uu
8YQXFF38o7LkADrRhUKQ2kGp6aivID0S747KYaFx8sxDv0AN+gh92WEr6l0w4FJD
2xgwkgwszoeThOqRYbdw2xF8tqQz3tg/1mFHQvI2En894/dbET4c8K+WzWGKGqDw
tSNlc7a0nBmnCd1DljesSPwhBwJSZyYsgST01xDm9BJpU++RKhVqIQYi6v4xOIK0
WrWbZ5AWuDHoJzjyQbkZAo/HbuEshHUZbbswO9IC8GXxdoIVnM4xOtR8g8rR+eG3
dvZ4k0fSAPljqpRB1HjQ7rzm6RQU5K5UMYj4WI17Pp+HXPIE5QfgTJqB6wIDAQAB
o4IDGTCCAxUwDgYDVR0PAQH/BAQDAgWgMB0GA1UdJQQWMBQGCCsGAQUFBwMBBggr
BgEFBQcDAjAMBgNVHRMBAf8EAjAAMB0GA1UdDgQWBBTbq4HjFF9tvrR4e159+2a/
VjfFHTAfBgNVHSMEGDAWgBSoSmpjBH3duubRObemRWXv86jsoTBvBggrBgEFBQcB
AQRjMGEwLgYIKwYBBQUHMAGGImh0dHA6Ly9vY3NwLmludC14My5sZXRzZW5jcnlw
dC5vcmcwLwYIKwYBBQUHMAKGI2h0dHA6Ly9jZXJ0LmludC14My5sZXRzZW5jcnlw
dC5vcmcvMBwGA1UdEQQVMBOCEW1haWwucGMtZnJlYWsubmV0MIH+BgNVHSAEgfYw
gfMwCAYGZ4EMAQIBMIHmBgsrBgEEAYLfEwEBATCB1jAmBggrBgEFBQcCARYaaHR0
cDovL2Nwcy5sZXRzZW5jcnlwdC5vcmcwgasGCCsGAQUFBwICMIGeDIGbVGhpcyBD
ZXJ0aWZpY2F0ZSBtYXkgb25seSBiZSByZWxpZWQgdXBvbiBieSBSZWx5aW5nIFBh
cnRpZXMgYW5kIG9ubHkgaW4gYWNjb3JkYW5jZSB3aXRoIHRoZSBDZXJ0aWZpY2F0
ZSBQb2xpY3kgZm91bmQgYXQgaHR0cHM6Ly9sZXRzZW5jcnlwdC5vcmcvcmVwb3Np
dG9yeS8wggEEBgorBgEEAdZ5AgQCBIH1BIHyAPAAdwDiaUuuJujpQAnohhu2O4PU
Puf+dIj7pI8okwGd3fHb/gAAAWZfUA/1AAAEAwBIMEYCIQDW3ket0jK+vt2z6+6E
ngKKTzPiYyHV901HgpKruQpJYgIhAOh9F4Ey40/PLXmMl0bh716Z9IqLtW0jXwWE
4hRqVo6gAHUAKTxRllTIOWW6qlD8WAfUt2+/WHopctykwwz05UVH9HgAAAFmX1AP
8wAABAMARjBEAiBsjuficK0zplzgiYT7C/bhXAUGCqjbixx60FKZXz+iZAIgS80L
56AnBDEZGFiZUXNJa3clp+dbEIy97VQD3UDkLTEwDQYJKoZIhvcNAQELBQADggEB
AJyGszRkr6ydxNOnzPyKMhh1lZVHm5w8Djth+YhhOBqmkmk9FGpTExRl5sr6uY5I
ydRz9uR0ih8r8hSG8RhVJhugl4kVC2LGKyeBb2CvVWizLFsQVqJ9KMuO/PAhZXib
OlLTnSf/1ySV3g/YPaJDbvylLfKtN+nq27V1uHytI0Udvf5ONsf04j1HyQb8y3W6
1AqQF+rhf0nmaCeXinDHUOkZSoohGCZ5o2H/Gyae/oWPIO3GTcEOBCGoBdQpaZlT
Y4HH1Vhx3wK1lMk2SMk1gKtxeNkS9vUQJT04xUB1JbGVGNgclvHGGtLEmfUBLvTh
Sh8QQg407ZKOU5/CexFReGo=
—–END CERTIFICATE—–

 

Same way a .der files content / encryption algorithm and domain name could be grasped.
 

root@pcfreak:~# openssl x509 -in cert.der -inform der -text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            ad:c2:96:6f:4b:db:31:5c
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = example.com
        Validity
            Not Before: Jun 22 04:00:37 2015 GMT
            Not After : Jul 22 04:00:37 2015 GMT

        Subject: CN = example.com
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (512 bit)

                Modulus:
                    00:ac:75:73:b4:51:ed:1f:dd:ae:70:52:43:fc:df:
                    c7:5b:d0:2c:75:1b:14:b8:75:01:04:10:e5:1f:03:
                    65:45:dd:df:a7:9f:34:ae:fd:be:e9:05:84:df:47:
                    16:81:d9:89:4b:ce:8e:6d:1c:fa:95:44:e8:af:84:
                    74:4f:ed:c2:e5
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Key Identifier:
                26:CF:C8:ED:4B:D7:94:B2:E4:25:03:58:24:8F:04:C0:74:D5:97:8A
            X509v3 Authority Key Identifier:
                keyid:26:CF:C8:ED:4B:D7:94:B2:E4:25:03:58:24:8F:04:C0:74:D5:97:8A

 

            X509v3 Basic Constraints:
                CA:TRUE
    Signature Algorithm: sha256WithRSAEncryption
         0c:8b:ff:12:80:9e:4c:90:bc:26:b0:96:20:ab:76:0c:64:71:
         d2:15:48:a5:33:f6:47:e4:03:df:76:5e:0f:cd:e1:1b:5e:d1:
         4d:c2:1f:8d:b8:63:2f:c9:7d:6e:5c:3b:cb:cd:a3:d0:d8:27:
         74:66:a3:76:06:a5:fb:81:3a:b6
—–BEGIN CERTIFICATE—–
MIIBdTCCAR+gAwIBAgIJAK3Clm9L2zFcMA0GCSqGSIb3DQEBCwUAMBYxFDASBgNV
BAMMC2V4YW1wbGUuY29tMB4XDTE1MDYyMjA0MDAzN1oXDTE1MDcyMjA0MDAzN1ow
FjEUMBIGA1UEAwwLZXhhbXBsZS5jb20wXDANBgkqhkiG9w0BAQEFAANLADBIAkEA
rHVztFHtH92ucFJD/N/HW9AsdRsUuHUBBBDlHwNlRd3fp580rv2+6QWE30cWgdmJ
S86ObRz6lUTor4R0T+3C5QIDAQABo1AwTjAdBgNVHQ4EFgQUJs/I7UvXlLLkJQNY
JI8EwHTVl4owHwYDVR0jBBgwFoAUJs/I7UvXlLLkJQNYJI8EwHTVl4owDAYDVR0T
BAUwAwEB/zANBgkqhkiG9w0BAQsFAANBAAyL/xKAnkyQvCawliCrdgxkcdIVSKUz
9kfkA992Xg/N4Rte0U3CH424Yy/JfW5cO8vNo9DYJ3Rmo3YGpfuBOrY=
—–END CERTIFICATE—–

 

2. How to display content and info about .CSR (Certificate Signing request)

 

root@pcfreak:~# openssl req -in cert.csr -noout -text
 

Certificate Request:
    Data:
        Version: 1 (0x0)
        Subject: C = BG, ST = BG, L = Dobrich, O = Pc Freak, CN = mail.pc-freak.net, emailAddress = hipo@pc-freak.net
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:b1:83:a7:07:62:19:d4:60:95:58:49:de:b3:48:
                    a8:39:31:fa:5a:bd:2b:d6:73:94:50:36:72:74:18:
                    58:b6:27:d3:0b:26:75:15:a8:ba:1b:77:a7:c9:54:
                    96:1f:c7:8d:da:bd:c9:17:91:53:85:9e:0a:f4:71:
                    3c:fb:d6:e4:75:41:c1:95:32:e1:22:fc:7b:1f:36:
                    23:12:00:ca:37:27:d5:f9:9b:29:48:4a:51:95:d1:
                    40:d0:ea:94:51:98:98:6b:d3:d9:79:1d:a1:65:bb:
                    a9:d1:ab:c9:46:6e:03:ee:24:45:e5:f2:73:e5:f4:
                    82:4a:08:57:b1:06:52:c3:cc:42:9a:02:5b:7a:7c:
                    bd:34:d5:5f:d7:ba:ef:27:d5:3d:97:16:69:06:c7:
                    c1:06:5e:d9:07:16:3f:a3:61:50:9d:dd:ea:95:32:
                    f1:ee:93:82:48:df:20:8b:ae:d2:95:89:05:e4:3d:
                    0c:d7:e1:cf:07:ae:55:84:11:06:92:be:34:b4:a2:
                    a1:ce:07:06:bf:21:bc:80:e2:03:d2:85:b4:64:02:
                    8d:cd:d2:86:1c:49:41:52:43:a8:12:f8:ef:2c:f4:
                    be:a0:dc:ac:ea:27:3a:f9:ab:ab:27:da:28:63:1d:
                    10:5a:4f:b8:51:42:40:ae:be:c0:2d:e9:a3:5a:5a:
                    23:7f
                Exponent: 65537 (0x10001)
        Attributes:
            a0:00
    Signature Algorithm: sha256WithRSAEncryption
         47:f0:54:cd:5e:46:6f:2c:cc:48:7e:85:f0:a8:96:10:ca:a3:
         15:98:77:d3:02:95:8c:67:84:e3:55:d2:0c:e8:d5:a7:ba:82:
         95:fb:ce:73:4c:bc:8f:da:85:97:0c:a8:59:32:b3:a4:af:0a:
         80:4c:78:7f:62:cd:1b:00:01:e8:51:27:9c:eb:75:29:80:e9:
         99:24:fc:86:e2:09:28:be:47:5a:1d:bf:b1:b4:c4:29:4e:6e:
         f3:70:b4:58:f8:d9:a6:63:03:8b:a1:ef:ee:6d:1a:35:33:1e:
         b2:32:25:c1:33:37:3d:46:82:37:9b:0d:4c:40:20:ae:ff:e0:
         cc:51:a2:6b:dd:74:26:d6:93:26:89:c7:76:29:13:cf:6e:5a:
         0f:7c:1b:f5:80:be:3b:6a:a3:c0:10:cd:07:1e:a2:31:8b:49:
         94:d7:63:cf:93:8d:80:03:75:4a:76:b4:cd:14:fe:96:62:61:
         6b:96:8f:c0:a5:ef:67:c7:5e:c0:a5:4b:4f:95:57:b6:43:03:
         8b:6d:10:5f:ab:f2:95:54:ba:85:8e:8b:c1:99:ea:fd:3f:5e:
         23:01:d4:27:f3:e9:20:37:c4:05:47:30:67:94:53:f0:87:27:
         48:73:57:55:f2:70:04:b1:e9:29:eb:2e:2c:9a:cc:55:f4:cc:
         a4:71:c2:5a


That's all folks 🙂

 

Converting .crt .cer .der to PEM, converting .PEM to .DER and convert .PFX PKCS#12 (.P12) to .PEM file using OpenSSL

Friday, September 1st, 2017

openssl_check_verify_crt_csr_key_certificate_consistency-with-openssl-command-openssl-logo

These commands allow you to convert certificates and keys to different formats to make them compatible with specific types of servers or software. For example, you can convert a normal PEM file that would work with Apache to a PFX (PKCS#12) file and use it with Tomcat or IIS.

  • Convert a DER file (.crt .cer .der) to PEM

     

    openssl x509 -inform der -in certificate.cer -out certificate.pem
    
  • Convert a PEM file to DER

     

    openssl x509 -outform der -in certificate.pem -out certificate.der
    
  • Convert a PKCS#12 file (.pfx .p12) containing a private key and certificates to PEM

     

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


    You can add -nocerts to only output the private key or add -nokeys to only output the certificates.

  • Convert a PEM certificate file and a private key to PKCS#12 (.pfx .p12)

     

    openssl pkcs12 -export -out certificate.pfx -inkey privateKey.key \
    -in certificate.crt -certfile CACert.crt

MySQL SSL Configure Howto – How to Make MySQL communication secured

Wednesday, January 15th, 2014

mysql-over-ssl-how-to-configure-logo how to configure ssl on mysql server

Recently I've been asked How to make communication to MySQL database encrypted. The question was raised by a fellow developer who works on developing a Desktop standalone application in Delphi Programming Language with DevArt an (SQL Connection Component capable to connect Delphi applications to multiple databases like MySQL, Oracle, PostgreSQL, Interbase, Firebird etc.

Communicating in Secured form to MySQL database is not common task to do, as MySQL usually communicates to applications hosted on same server or applications to communicate to MySQL are in secured DMZ or administrated via phpMyAdmin web interface.

MySQL supports encrypted connections to itself using Secure Socket Layer (SSL) encryption. Setting up MySQL db to be communicated encrypted is a must for standalone Desktop applications which has to extract / insert data via remote SQL.
Configuring SQL to support communicated queries encrpytion is supported by default and easily configured on most standard Linux version distributions (Debian, RHEL, Fedora) with no need to recompile it.
1. Generate SSL Certificates

$ mkdir /etc/mysql-ssl && cd mysql-ssl

# Create CA certificate
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3600 \
         -key ca-key.pem -out ca-cert.pem

Create server certificate, remove passphrase, and sign it
server-cert.pem is public key, server-key.pem is private key
$ openssl req -newkey rsa:2048 -days 3600 \
         -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 3600 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Create client certificate, remove passphrase, and sign it
client-cert.pem is public key and client-key.pem is private key
$ openssl req -newkey rsa:2048 -days 3600 \
         -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 3600 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

After generating the certificates, verify them:

$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
 

2. Add SSL support variables to my.cnf

Once SSL key pair files are generated in order to active SSL encryption support in MySQL server, add to (/etc/my.cnf,  /etc/mysql/my.cnf, /usr/local/etc/my.cnf … ) or wherever config is depending on distro

# SSL
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem

3. Restart MySQL server

/etc/init.d/mysqld restart
...

4. Create SQL user to require SSL login

Create new user with access to database;

GRANT ALL ON Sql_User_DB.* TO Sql_User@localhost;
FLUSH PRIVILEGES;

To create administrator privileges user:

GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@'%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;
FLUSH PRIVILEGES;

5. Test SSL Connection with MySQL CLI client or with few lines of PHP

To use mysql cli for testing whether SSL connection works:

$ mysql -u ssluser -p'pass' –ssl-ca /etc/mysql-ssl/client-cert.pem –ssl-cert /etc/mysql-ssl/client-key.pem

Once connected to MySQL to verify SSL connection works fine:

mysql> SHOW STATUS LIKE 'Ssl_Cipher';
 +---------------+--------------------+
| Variable_name | Value              |
 +---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

If you get this output this means MySQL SSL Connection is working as should.

Alternative way is to use test-mysqli-ssl.php script to test availability to mysql over SSL.

$conn=mysqli_init();
mysqli_ssl_set($conn, '/etc/mysql-ssl/client-key.pem', '/etc/mysql-ssl/client-cert.pem', NULL, NULL, NULL);
if (!mysqli_real_connect($conn, '127.0.0.1', 'ssluser', 'pass')) { die(); }
$res = mysqli_query($conn, 'SHOW STATUS like "Ssl_cipher"');
print_r(mysqli_fetch_row($res));
mysqli_close($conn);

Note: Change username password according to your user / pass before using the script

That's all now you have mysql communicating queries data over SSL

 

How to generate self signed SSL certificate with openssl on Linux / BSD

Wednesday, November 6th, 2013

How to generate self signed SSL certificate with openssl on Linux / BSD

It is common solution for personal use to generate SSL certificates which are self-signed. Self-signed certificates are dangerous as no authority or company guarantees that remote site is trustable. However for private use having encrypted connection whether you need to transfer personal data is better than not having such. There are plenty of tutorials online pointing how to set-up Apache webserver to provide access via SSL port 443 with self-signed certifacate, but anyways I decided to blog here a one-liner command way, which makes generating self-signed certificate a piece of cake. Self-signed certificates on UNIX are generated with openssl command part of openssl (Secure Socket Layer cryptocgraphic tools).

On Debian Linux to install openssl (if you still don't have it):

apt-get install --yes openssl

On Fedora, RHEL, CentOS etc. same install should be done with:

yum install -y openssl

On FreeBSD to install it use ports;

cd /usr/ports/security/openssl
make install clean

Once openssl is available, here is command to generate self signed SSL certitifacate;
 

openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout my-sitename.key -out my-sitename.crt
Generating a 2048 bit RSA private key
............................................+++
..............................................................+++
writing new private key to 'key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:BG
State or Province Name (full name) [Some-State]:Sofia
Locality Name (eg, city) []:Sofia
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Pc Freak
Organizational Unit Name (eg, section) []:Pc Freak
Common Name (eg, YOUR name) []:www.pc-freak.net
Email Address []:testing@pc-freak.net



The generated certificate Private Key file is placed in my-sitename.key
, actual certificate is located in my-sitename.crt -days option tells for how long period certificate will be valid. Regenerating certificate every year (360 days) is good security practice but it is pretty annoying to see your certificate has expered thus for private self signed SSL certificate it is more confortable to generate cert for 10 years time.
To use my-sitename.key and my-sitename.crt copy them to /etc/ssl/crt/

cp -rpf my-sitename.crt /etc/ssl/crt/
cp -rpf my-sitename.key /etc/ssl/crt/

Next what's left is to configure Apache to use new generated certs. Quickest way is to add it inside virtual host. Adding to Apache also depends on Linux distribution where SSL has to be added but in general, what should work is something like:

SSLEngine on
SSLCertificateFile /etc/ssl/crt/my-sitename.crt
SSLCertificateKeyFile /etc/ssl/crt/my-sitename.key

Note that if SSL has to be enabled for specific Virtual Host you will have to place above Apache directives into the Vhost. Though certifiate will only be trusted by your authority RSA 2048 bit encryption in transfer data between your Webserver and Browser should guarantee you extra security of data, not that it is impossible for data to be still sniffed by a skilled hacker but makes data securily transferred in probably 99% of cases 🙂

How to fix “sslserver: fatal: unable to load certificate” Qmail error on GNU / Linux

Friday, October 14th, 2011

After setupping a brand new Qmail installation following the QmailRocks Thibs Qmail Debian install guide , I’ve come across unexpected re-occuring error message in /var/log/qmail/qmail-smtpdssl/ , here is the message:

@400000004e9807b10d8bdb7c command-line: exec sslserver -e -vR -l my-mailserver-domain.com -c 30 -u 89 -g 89
-x /etc/tcp.smtp.cdb 0 465 rblsmtpd -r zen.spamhaus.org -r dnsbl.njabl.org -r dnsbl.sorbs.net -r bl.spamcop.net qmail-smtpd
my-mailserver-domain.com /home/vpopmail/bin/vchkpw /bin/true 2>&1
@400000004e9807b10dae2ca4 sslserver: fatal: unable to load certificate

I was completely puzzled initially by the error as the sertificate file /var/qmail/control/servercert.pem was an existing and properly self generated one. Besides that qmail daemontools init script /service/qmail-smtpd/run was loading the file just fine, where the same file failed to get loaded when sslserver command with the cert argument was invoked via /service/qmail-smtpdssl/run

It took me quite a while to thoroughfully investigate on what’s wrong with the new qmail install. Thanksfully after almost an hour of puzzling I found it out and I was feeling as a complete moron to find that the all issues was caused by incorrect permissions of the /var/qmail/control/servercert.pem file.
Here are the incorrect permissions the file possessed:

linux:~# ls -al /var/qmail/control/servercert.pem
-rw------- 1 qmaild qmail 2311 2011-10-12 13:21 /var/qmail/control/servercert.pem

To fix up the error I had to allow all users to have reading permissions over servercert.pem , e.g.:

linux:~# chmod a+r /var/qmail/control/servercert.pem

After adding all users readable bit on servercert.pem the file permissions are like so:

linux:~# ls -al /var/qmail/control/servercert.pem
-rw-r--r-- 1 qmaild qmail 2311 2011-10-12 13:21 /var/qmail/control/servercert.pem

Consequently I did a qmail restart to make sure the new readable servercert.pem will get loaded from the respective init script:

linux:~# qmailctl restart
* Stopping qmail-smtpdssl.
* Stopping qmail-smtpd.
* Sending qmail-send SIGTERM and restarting.
* Restarting qmail-smtpd.
* Restarting qmail-smtpdssl.

Now the annoying sslserver: fatal: unable to load certificate message is no more and all works fine, Hooray! 😉

How to renew self signed QMAIL toaster and QMAIL rocks expired SSL pem certificate

Friday, September 2nd, 2011

qmail_toaster_logo-fix-qmail-rocks-expired-ssl-pem-certificate

One of the QMAIL server installs, I have installed very long time ago. I've been notified by clients, that the certificate of the mail server has expired and therefore I had to quickly renew the certificate.

This qmail installation, SSL certificates were located in /var/qmail/control under the names servercert.key and cervercert.pem

Renewing the certificates with a new self signed ones is pretty straight forward, to renew them I had to issue the following commands:

1. Generate servercert encoded key with 1024 bit encoding

debian:~# cd /var/qmail/control
debian:/var/qmail/control# openssl genrsa -des3 -out servercert.key.enc 1024
Generating RSA private key, 1024 bit long modulus
...........++++++
.........++++++
e is 65537 (0x10001)
Enter pass phrase for servercert.key.enc:
Verifying - Enter pass phrase for servercert.key.enc:

In the Enter pass phrase for servercert.key.enc I typed twice my encoded key password, any password is good, here though using a stronger one is better.

2. Generate the servercert.key file

debian:/var/qmail/control# openssl rsa -in servercert.key.enc -out servercert.key
Enter pass phrase for servercert.key.enc:
writing RSA key

3. Generate the certificate request

debian:/var/qmail/control# openssl req -new -key servercert.key -out servercert.csr
debian:/var/qmail/control# openssl rsa -in servercert.key.enc -out servercert.key
Enter pass phrase for servercert.key.enc:writing RSA key
root@soccerfame:/var/qmail/control# openssl req -new -key servercert.key -out servercert.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:UK
State or Province Name (full name) [Some-State]:London
Locality Name (eg, city) []:London
Organization Name (eg, company) [Internet Widgits Pty Ltd]:My Company
Organizational Unit Name (eg, section) []:My Org
Common Name (eg, YOUR name) []:
Email Address []:admin@adminmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

In the above prompts its necessery to fill in the company name and location, as each of the prompts clearly states.

4. Sign the just generated certificate request

debian:/var/qmail/control# openssl x509 -req -days 9999 -in servercert.csr -signkey servercert.key -out servercert.crt

Notice the option -days 9999 this option instructs the newly generated self signed certificate to be valid for 9999 days which is quite a long time, the reason why the previous generated self signed certificate expired was that it was built for only 365 days

5. Fix the newly generated servercert.pem permissions debian:~# cd /var/qmail/control
debian:/var/qmail/control# chmod 640 servercert.pem
debian:/var/qmail/control# chown vpopmail:vchkpw servercert.pem
debian:/var/qmail/control# cp -f servercert.pem clientcert.pem
debian:/var/qmail/control# chown root:qmail clientcert.pem
debian:/var/qmail/control# chmod 640 clientcert.pem

Finally to load the new certificate, restart of qmail is required:

6. Restart qmail server

debian:/var/qmail/control# qmailctl restart
Restarting qmail:
* Stopping qmail-smtpd.
* Sending qmail-send SIGTERM and restarting.
* Restarting qmail-smtpd.

Test the newly installed certificate

To test the newly installed SSL certificate use the following commands:

debian:~# openssl s_client -crlf -connect localhost:465 -quiet
depth=0 /C=UK/ST=London/L=London/O=My Org/OU=My Company/emailAddress=admin@adminmail.com
verify error:num=18:self signed certificate
verify return:1
...
debian:~# openssl s_client -starttls smtp -crlf -connect localhost:25 -quiet
depth=0 /C=UK/ST=London/L=London/O=My Org/OU=My Company/emailAddress=admin@adminmail.com
verify error:num=18:self signed certificate
verify return:1
250 AUTH LOGIN PLAIN CRAM-MD5
...

If an error is returned like 32943:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:607: this means that SSL variable in the qmail-smtpdssl/run script is set to 0.

To solve this error, change SSL=0 to SSL=1 in /var/qmail/supervise/qmail-smtpdssl/run and do qmailctl restart

The error verify return:1 displayed is perfectly fine and it's more of a warning than an error as it just reports the certificate is self signed.