Saturday, September 17, 2011

Certificate Signing-Request Cookbook

After reading up on the openssl documentation for creating ssl certs, I've put together some quick recipes for generating certificate-signing requests (CSR) and self-signed certificates from custom config files (avoiding the laborious interactive prompts and the extra intermediate steps):

CSR for a Single-Domain Certificate

If you just want to create a certificate-signing request for a single domain, create a new directory (call it 'single-csr'), create a new openssl config file, add the following to the config file:

$ mkdir single-csr && cd single-csr $ echo ' [ req ] default_bits = 2048 default_keyfile = secret.key distinguished_name = req_distinguished_name encrypt_key = no prompt = no [ req_distinguished_name ] C = US ST = Washington L = Seattle O = My Company, Inc OU = Research & Development CN = www.example.com ' > openssl.cnf

If you're not using an existing secret key, you can simply generate the CSR like this:

$ openssl req -new -config openssl.cnf -out request.csr

This will generate a new CSR as request.csr in the current directory, along with a new (2048-bit RSA) secret key as secret.key. The secret key is the secret component of the certificate, so you'll want to hang on to it and keep it safe (when you configure your server, you'll point the server's configuration to both the public certificate and this secret key).

If instead you already have a secret key, specify the path to the key when you generate the CSR:

$ openssl req -new -config openssl.cnf \ -key /path/to/existing-secret.key -out request.csr

A CSR is just a plain text file with some base64-encoded (binary) data in it. You can decode it to check out what you just created with the following command (which will output the CSR's contents to the terminal); do this to make sure everything looks kosher:

$ openssl req -text -noout -in request.csr

The output should look like this:

Certificate Request: Data: Version: 0 (0x0) Subject: C=US, ST=Washington, L=Seattle, O=My Company, Inc, OU=Research & Development, CN=www.example.com Subject Public Key Info: Public Key Algorithm: rsaEncryption RSA Public Key: (2048 bit) Modulus (2048 bit): 00:cc:13:7b:91:ee:9f:36:25:4a:d8:ad:ae:20:51: 1e:b1:3d:8e:9e:21:88:2c:78:b9:50:ee:ae:fc:60: 73:6c:b4:42:ad:fc:7c:c8:b2:78:33:84:74:87:9d: 23:07:6e:9b:14:5c:7c:9c:c6:75:05:b7:c7:cf:88: 1f:14:66:30:19:97:fc:f1:1d:6f:ee:16:3c:46:b7: ed:35:ce:a6:49:18:5f:2b:ea:89:69:a3:f1:99:fe: a0:95:9d:a5:d6:e8:a0:f5:38:07:c0:6d:98:2d:0b: 04:f6:a5:32:56:e4:12:ab:ee:34:6b:07:71:06:6f: 58:b6:e7:0d:26:75:6d:06:22:c2:d4:bb:1a:43:9d: a6:09:c1:0c:fe:cb:ad:40:c4:3e:62:2c:49:5d:20: 79:0b:c6:93:27:d2:e1:b9:bd:3b:2e:e4:88:71:c4: 5e:a1:ce:45:14:2c:15:99:a3:ea:fe:77:ea:14:e5: 71:8b:c0:01:57:f5:61:4e:a8:19:92:6d:23:6b:78: 02:fc:54:7f:2a:3c:95:6f:37:b2:63:09:6f:13:9d: 47:47:4f:39:7b:79:f6:60:83:c3:2f:e7:db:1b:58: 6b:1d:3d:d6:c4:be:6a:1a:0c:e1:08:a0:4b:30:aa: 27:a4:e0:4c:eb:ba:2a:64:96:75:fe:c0:01:0d:4c: d5:b3 Exponent: 65537 (0x10001) Attributes: a0:00 Signature Algorithm: sha1WithRSAEncryption 92:9f:6e:15:66:12:90:0f:62:6c:f6:ca:79:4b:04:88:35:0c: 10:7b:f5:5c:6d:b7:f5:19:a3:3b:5c:eb:b9:fa:d3:63:95:a0: 1b:7f:69:9a:ad:4d:23:03:7d:fc:83:2c:dd:76:6d:7f:a5:da: 8a:53:34:82:eb:10:12:8c:22:2f:7b:cd:94:3a:8a:7d:fd:33: f5:ca:21:23:37:96:cf:00:64:93:82:ac:41:95:01:74:dd:ed: 83:68:ec:4b:29:87:19:63:fe:72:bf:44:91:ef:ac:a1:50:d9: 63:06:e6:5b:00:42:61:ca:3b:86:01:f9:2e:21:3c:58:4f:a7: d4:97:3d:89:5a:0b:11:c6:0d:49:95:ee:20:80:31:eb:5b:1a: 3c:ef:66:88:5b:12:23:9f:6d:67:ed:eb:18:83:0a:69:e1:82: 2a:46:41:24:48:12:64:42:90:99:7c:8b:bd:6c:65:33:d4:2f: f8:c4:99:b8:95:f7:d6:c1:c0:fc:d7:d4:fd:b7:27:3d:4a:ab: 14:82:4c:17:25:b0:ec:3e:9d:97:ac:8e:f0:1f:e4:92:de:28: a2:36:59:cf:71:fc:81:ed:0a:2a:ba:16:63:35:03:65:17:a2: 7f:13:ac:2a:54:39:ec:f0:1b:9a:7e:c5:3b:d1:74:c5:df:9e: 2f:a9:3e:58

Once you've submitted the request to a certificate authority, you don't need to keep around the request.csr file.

CSR for a Wildcard Certificate

If you want to create a certificate-signing request for a wildcard domain (ie *.example.com), follow the same exact steps as above, except in your config file, use the wildcard domain for the CN value:

$ mkdir wildcard-csr && cd wildcard-csr $ echo ' [ req ] default_bits = 2048 default_keyfile = secret.key distinguished_name = req_distinguished_name encrypt_key = no prompt = no [ req_distinguished_name ] C = US ST = Washington L = Seattle O = My Company, Inc OU = Research & Development CN = *.example.com ' > openssl.cnf $ openssl req -new -config openssl.cnf -out request.csr $ openssl req -text -noout -in request.csr

CSR for a UC Certificate

The steps are the same as above for a UC Certificate (aka Unified-Communications Certificate or UCC, which allows you to cover multiple domains with the same cert), except you need to add v3_ext and alt_names sections to the config file. Choose one of your domain names as the "primary" domain, and include it both as the CN value in the req_distinguished_name section, and as the first domain in the alt_names section. (In theory, in a UC cert it doesn't matter at all what you specify in the CN field — it doesn't even have to be a domain name — but in practice it's best if you specify one of the domains from the alternate-names section as the CN; many tools will identify the CSR by what you put in the CN field, and treat it like the cert's primary domain.)

$ mkdir ucc-csr && cd ucc-csr $ echo ' [ req ] default_bits = 2048 default_keyfile = secret.key distinguished_name = req_distinguished_name encrypt_key = no prompt = no req_extensions = v3_ext [ req_distinguished_name ] C = US ST = Washington L = Seattle O = My Company, Inc OU = Research & Development CN = www.example.com [ v3_ext ] subjectAltName = @alt_names [ alt_names ] DNS.1 = www.example.com DNS.2 = www.example.org DNS.3 = static.example.com DNS.4 = www.another-example.com ' > openssl.cnf $ openssl req -new -config openssl.cnf -out request.csr $ openssl req -text -noout -in request.csr

In the output of the resulting request.csr, you should see a X509v3 Subject Alternative Name: section, listing all your configured domains:

Certificate Request: Data: Version: 0 (0x0) Subject: C=US, ST=Washington, L=Seattle, O=My Company, Inc, OU=Research & Development, CN=www.example.com Subject Public Key Info: Public Key Algorithm: rsaEncryption RSA Public Key: (2048 bit) Modulus (2048 bit): 00:cc:13:7b:91:ee:9f:36:25:4a:d8:ad:ae:20:51: 1e:b1:3d:8e:9e:21:88:2c:78:b9:50:ee:ae:fc:60: 73:6c:b4:42:ad:fc:7c:c8:b2:78:33:84:74:87:9d: 23:07:6e:9b:14:5c:7c:9c:c6:75:05:b7:c7:cf:88: 1f:14:66:30:19:97:fc:f1:1d:6f:ee:16:3c:46:b7: ed:35:ce:a6:49:18:5f:2b:ea:89:69:a3:f1:99:fe: a0:95:9d:a5:d6:e8:a0:f5:38:07:c0:6d:98:2d:0b: 04:f6:a5:32:56:e4:12:ab:ee:34:6b:07:71:06:6f: 58:b6:e7:0d:26:75:6d:06:22:c2:d4:bb:1a:43:9d: a6:09:c1:0c:fe:cb:ad:40:c4:3e:62:2c:49:5d:20: 79:0b:c6:93:27:d2:e1:b9:bd:3b:2e:e4:88:71:c4: 5e:a1:ce:45:14:2c:15:99:a3:ea:fe:77:ea:14:e5: 71:8b:c0:01:57:f5:61:4e:a8:19:92:6d:23:6b:78: 02:fc:54:7f:2a:3c:95:6f:37:b2:63:09:6f:13:9d: 47:47:4f:39:7b:79:f6:60:83:c3:2f:e7:db:1b:58: 6b:1d:3d:d6:c4:be:6a:1a:0c:e1:08:a0:4b:30:aa: 27:a4:e0:4c:eb:ba:2a:64:96:75:fe:c0:01:0d:4c: d5:b3 Exponent: 65537 (0x10001) Attributes: Requested Extensions: X509v3 Subject Alternative Name: DNS:www.example.com, DNS:www.example.org, DNS:static.example.com, DNS:www.another-example.com Signature Algorithm: sha1WithRSAEncryption 92:9f:6e:15:66:12:90:0f:62:6c:f6:ca:79:4b:04:88:35:0c: 10:7b:f5:5c:6d:b7:f5:19:a3:3b:5c:eb:b9:fa:d3:63:95:a0: 1b:7f:69:9a:ad:4d:23:03:7d:fc:83:2c:dd:76:6d:7f:a5:da: 8a:53:34:82:eb:10:12:8c:22:2f:7b:cd:94:3a:8a:7d:fd:33: f5:ca:21:23:37:96:cf:00:64:93:82:ac:41:95:01:74:dd:ed: 83:68:ec:4b:29:87:19:63:fe:72:bf:44:91:ef:ac:a1:50:d9: 63:06:e6:5b:00:42:61:ca:3b:86:01:f9:2e:21:3c:58:4f:a7: d4:97:3d:89:5a:0b:11:c6:0d:49:95:ee:20:80:31:eb:5b:1a: 3c:ef:66:88:5b:12:23:9f:6d:67:ed:eb:18:83:0a:69:e1:82: 2a:46:41:24:48:12:64:42:90:99:7c:8b:bd:6c:65:33:d4:2f: f8:c4:99:b8:95:f7:d6:c1:c0:fc:d7:d4:fd:b7:27:3d:4a:ab: 14:82:4c:17:25:b0:ec:3e:9d:97:ac:8e:f0:1f:e4:92:de:28: a2:36:59:cf:71:fc:81:ed:0a:2a:ba:16:63:35:03:65:17:a2: 7f:13:ac:2a:54:39:ec:f0:1b:9a:7e:c5:3b:d1:74:c5:df:9e: 2f:a9:3e:58

Self-Signed Certificate (for a Single/Wildcard Domain)

To generate a self-signed certificate, you don't need a create a certificate request at all (contrary to other how-tos you might find); you can just create the certificate directly in a single step. You start with the same config as above:

$ mkdir mycert && cd mycert $ echo ' [ req ] default_bits = 2048 default_keyfile = secret.key distinguished_name = req_distinguished_name encrypt_key = no prompt = no [ req_distinguished_name ] C = US ST = Washington L = Seattle O = My Company, Inc OU = Research & Development CN = www.example.com ' > openssl.cnf

While you use the same openssl req command to generate the self-signed cert, you add the -x509 option to generate the certificate file directly (instead of just generating a request). You probably also will want to add the -days option, which specifies for how long the cert is good. The default is just 30 days; let's make it 10 years instead:

$ openssl req -new -x509 -days 3653 \ -config openssl.cnf -out self-signed.crt

This will generate the new cert as self-signed.crt in the current directory, along with a new (2048-bit RSA) secret key for it as secret.key. When you configure a server with this cert, you'll point it at both the (public) cert and the secret key. For example, you'd configure an apache vhost like this:

SSLEngine on SSLCertificateFile /path/to/self-signed.crt SSLCertificateKeyFile /path/to/secret.key

But before you try to use the cert, it's best to double check it, this time with the openssl x509 command:

$ openssl x509 -text -noout -in self-signed.crt

The output should look like this:

Certificate: Data: Version: 1 (0x0) Serial Number: ab:99:22:16:8c:cc:32:cd Signature Algorithm: sha1WithRSAEncryption Issuer: C=US, ST=Washington, L=Seattle, O=My Company, Inc, OU=Research & Development, CN=www.example.com Validity: Not Before: Sep 15 03:39:59 2011 GMT Not After: Sep 15 03:39:59 2021 GMT Subject: C=US, ST=Washington, L=Seattle, O=My Company, Inc, OU=Research & Development, CN=www.example.com Subject Public Key Info: Public Key Algorithm: rsaEncryption RSA Public Key: (2048 bit) Modulus (2048 bit): 00:cc:13:7b:91:ee:9f:36:25:4a:d8:ad:ae:20:51: 1e:b1:3d:8e:9e:21:88:2c:78:b9:50:ee:ae:fc:60: 73:6c:b4:42:ad:fc:7c:c8:b2:78:33:84:74:87:9d: 23:07:6e:9b:14:5c:7c:9c:c6:75:05:b7:c7:cf:88: 1f:14:66:30:19:97:fc:f1:1d:6f:ee:16:3c:46:b7: ed:35:ce:a6:49:18:5f:2b:ea:89:69:a3:f1:99:fe: a0:95:9d:a5:d6:e8:a0:f5:38:07:c0:6d:98:2d:0b: 04:f6:a5:32:56:e4:12:ab:ee:34:6b:07:71:06:6f: 58:b6:e7:0d:26:75:6d:06:22:c2:d4:bb:1a:43:9d: a6:09:c1:0c:fe:cb:ad:40:c4:3e:62:2c:49:5d:20: 79:0b:c6:93:27:d2:e1:b9:bd:3b:2e:e4:88:71:c4: 5e:a1:ce:45:14:2c:15:99:a3:ea:fe:77:ea:14:e5: 71:8b:c0:01:57:f5:61:4e:a8:19:92:6d:23:6b:78: 02:fc:54:7f:2a:3c:95:6f:37:b2:63:09:6f:13:9d: 47:47:4f:39:7b:79:f6:60:83:c3:2f:e7:db:1b:58: 6b:1d:3d:d6:c4:be:6a:1a:0c:e1:08:a0:4b:30:aa: 27:a4:e0:4c:eb:ba:2a:64:96:75:fe:c0:01:0d:4c: d5:b3 Exponent: 65537 (0x10001) Signature Algorithm: sha1WithRSAEncryption 92:9f:6e:15:66:12:90:0f:62:6c:f6:ca:79:4b:04:88:35:0c: 10:7b:f5:5c:6d:b7:f5:19:a3:3b:5c:eb:b9:fa:d3:63:95:a0: 1b:7f:69:9a:ad:4d:23:03:7d:fc:83:2c:dd:76:6d:7f:a5:da: 8a:53:34:82:eb:10:12:8c:22:2f:7b:cd:94:3a:8a:7d:fd:33: f5:ca:21:23:37:96:cf:00:64:93:82:ac:41:95:01:74:dd:ed: 83:68:ec:4b:29:87:19:63:fe:72:bf:44:91:ef:ac:a1:50:d9: 63:06:e6:5b:00:42:61:ca:3b:86:01:f9:2e:21:3c:58:4f:a7: d4:97:3d:89:5a:0b:11:c6:0d:49:95:ee:20:80:31:eb:5b:1a: 3c:ef:66:88:5b:12:23:9f:6d:67:ed:eb:18:83:0a:69:e1:82: 2a:46:41:24:48:12:64:42:90:99:7c:8b:bd:6c:65:33:d4:2f: f8:c4:99:b8:95:f7:d6:c1:c0:fc:d7:d4:fd:b7:27:3d:4a:ab: 14:82:4c:17:25:b0:ec:3e:9d:97:ac:8e:f0:1f:e4:92:de:28: a2:36:59:cf:71:fc:81:ed:0a:2a:ba:16:63:35:03:65:17:a2: 7f:13:ac:2a:54:39:ec:f0:1b:9a:7e:c5:3b:d1:74:c5:df:9e: 2f:a9:3e:58

On ubuntu/debian, you'll probably want to move self-signed.crt to the /etc/ssl/certs directory (the first place you should check when you forgot where you put it 10 years ago):

$ chmod 444 self-signed.crt $ sudo chown root:root self-signed.crt $ sudo mv self-signed.crt /etc/ssl/certs/www.example.com.crt

And secret.key to the /etc/ssl/private directory:

$ chmod 400 secret.key $ sudo chown root:root secret.key $ sudo mv secret.key /etc/ssl/private/www.example.com.key

Self-Signed UC Certificate

Generating a self-signed UCC is just a matter of using the openssl.cnf file for a UC CSR with the above steps — but with one little tweak to the config file. Rename the req_extensions field to x509_extensions:

$ mkdir uc-cert && cd uc-cert $ echo ' [ req ] default_bits = 2048 default_keyfile = secret.key distinguished_name = req_distinguished_name encrypt_key = no prompt = no req_extensions = v3_ext x509_extensions = v3_ext [ req_distinguished_name ] C = US ST = Washington L = Seattle O = My Company, Inc OU = Research & Development CN = www.example.com [ v3_ext ] subjectAltName = @alt_names [ alt_names ] DNS.1 = www.example.com DNS.2 = www.example.org DNS.3 = static.example.com DNS.4 = www.another-example.com ' > openssl.cnf $ openssl req -new -x509 -days 3653 \ -config openssl.cnf -out self-signed.crt $ openssl x509 -text -noout -in self-signed.crt

In the output of that last command (showing the text of self-signed.crt), you should see a X509v3 Subject Alternative Name: section, listing all your configured domains:

Certificate: Data: Version: 3 (0x2) Serial Number: ab:99:22:16:8c:cc:32:cd Signature Algorithm: sha1WithRSAEncryption Issuer: C=US, ST=Washington, L=Seattle, O=My Company, Inc, OU=Research & Development, CN=www.example.com Validity: Not Before: Sep 15 03:39:59 2011 GMT Not After: Sep 15 03:39:59 2021 GMT Subject: C=US, ST=Washington, L=Seattle, O=My Company, Inc, OU=Research & Development, CN=www.example.com Subject Public Key Info: Public Key Algorithm: rsaEncryption RSA Public Key: (2048 bit) Modulus (2048 bit): 00:cc:13:7b:91:ee:9f:36:25:4a:d8:ad:ae:20:51: 1e:b1:3d:8e:9e:21:88:2c:78:b9:50:ee:ae:fc:60: 73:6c:b4:42:ad:fc:7c:c8:b2:78:33:84:74:87:9d: 23:07:6e:9b:14:5c:7c:9c:c6:75:05:b7:c7:cf:88: 1f:14:66:30:19:97:fc:f1:1d:6f:ee:16:3c:46:b7: ed:35:ce:a6:49:18:5f:2b:ea:89:69:a3:f1:99:fe: a0:95:9d:a5:d6:e8:a0:f5:38:07:c0:6d:98:2d:0b: 04:f6:a5:32:56:e4:12:ab:ee:34:6b:07:71:06:6f: 58:b6:e7:0d:26:75:6d:06:22:c2:d4:bb:1a:43:9d: a6:09:c1:0c:fe:cb:ad:40:c4:3e:62:2c:49:5d:20: 79:0b:c6:93:27:d2:e1:b9:bd:3b:2e:e4:88:71:c4: 5e:a1:ce:45:14:2c:15:99:a3:ea:fe:77:ea:14:e5: 71:8b:c0:01:57:f5:61:4e:a8:19:92:6d:23:6b:78: 02:fc:54:7f:2a:3c:95:6f:37:b2:63:09:6f:13:9d: 47:47:4f:39:7b:79:f6:60:83:c3:2f:e7:db:1b:58: 6b:1d:3d:d6:c4:be:6a:1a:0c:e1:08:a0:4b:30:aa: 27:a4:e0:4c:eb:ba:2a:64:96:75:fe:c0:01:0d:4c: d5:b3 Exponent: 65537 (0x10001) Requested Extensions: X509v3 Subject Alternative Name: DNS:www.example.com, DNS:www.example.org, DNS:static.example.com, DNS:www.another-example.com Signature Algorithm: sha1WithRSAEncryption 92:9f:6e:15:66:12:90:0f:62:6c:f6:ca:79:4b:04:88:35:0c: 10:7b:f5:5c:6d:b7:f5:19:a3:3b:5c:eb:b9:fa:d3:63:95:a0: 1b:7f:69:9a:ad:4d:23:03:7d:fc:83:2c:dd:76:6d:7f:a5:da: 8a:53:34:82:eb:10:12:8c:22:2f:7b:cd:94:3a:8a:7d:fd:33: f5:ca:21:23:37:96:cf:00:64:93:82:ac:41:95:01:74:dd:ed: 83:68:ec:4b:29:87:19:63:fe:72:bf:44:91:ef:ac:a1:50:d9: 63:06:e6:5b:00:42:61:ca:3b:86:01:f9:2e:21:3c:58:4f:a7: d4:97:3d:89:5a:0b:11:c6:0d:49:95:ee:20:80:31:eb:5b:1a: 3c:ef:66:88:5b:12:23:9f:6d:67:ed:eb:18:83:0a:69:e1:82: 2a:46:41:24:48:12:64:42:90:99:7c:8b:bd:6c:65:33:d4:2f: f8:c4:99:b8:95:f7:d6:c1:c0:fc:d7:d4:fd:b7:27:3d:4a:ab: 14:82:4c:17:25:b0:ec:3e:9d:97:ac:8e:f0:1f:e4:92:de:28: a2:36:59:cf:71:fc:81:ed:0a:2a:ba:16:63:35:03:65:17:a2: 7f:13:ac:2a:54:39:ec:f0:1b:9a:7e:c5:3b:d1:74:c5:df:9e: 2f:a9:3e:58

Sunday, September 11, 2011

Grails Foreign ID Generator

I haven't found a complete example on the web of using a foreign id generator in grails, so here's one: Say you have two domains, Primary and Secondary, with a one-to-one relationship (Primary hasOne Secondary and Secondary belongsTo Primary). Primary and Secondary basically represent the same entity, but Secondary has a bunch of data about the entity you rarely use. You map Primary to the DB table named primary, and Secondary to the DB table secondary; and since you've got a one-to-one relationship between Primary and Secondary, you just want the same column in the secondary table to be used as both its primary key and its foreign key to the primary table.

So you define Primary and Secondary like this:

class Primary { int oftUsedInfo int moreOftUsedInfo static hasOne = [ secondary: Secondary ] static mapping = { secondary cascade: 'all-delete-orphan' } } class Secondary { String littleUsedInfo String moreLittleUsedInfo static belongsTo = [ primary: Primary ] static mapping = { id column: 'primary_id', generator: 'foreign', params: [ property: 'primary' ] primary insertable: false, updateable: false } }

With that mapping, hibernate will create tables for you like the following (when using the MySQL InnoDB dialect):

CREATE TABLE `primary` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `version` BIGINT(20) NOT NULL, `oft_used_info` INT(11) NOT NULL, `more_oft_used_info` INT(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `secondary` ( `primary_id` BIGINT(20) NOT NULL, `version` BIGINT(20) NOT NULL, `little_used_info` VARCHAR(255) NOT NULL, `more_little_used_info` VARCHAR(255) NOT NULL, PRIMARY KEY (`primary_id`), KEY `FK12344567ABCDEF` (`primary_id`), CONSTRAINT `FK12344567ABCDEF` FOREIGN KEY (`primary_id`) REFERENCES `primary` (`id`) ) ENGINE=InnoDB;

Instead of the secondary table having its own separate AUTO_INCREMENT id column, it just re-uses the primary_id column (referencing the primary table) as its primary key.

When you create a new Primary and Secondary instances programmatically, you'd do it like this:

new Primary( oftUsedInfo: 1, moreOftUsedInfo: 2, secondary: new Secondary( littleUsedInfo: 'foo', moreLittleUsedInfo: 'bar', ), ).save()

Or, if you want to do it property by property:

def primary = new Primary() primary.oftUsedInfo = 1 primary.moreOftUsedInfo = 2 primary.secondary = new Secondary() primary.secondary.littleUsedInfo = 'foo' primary.secondary.moreLittleUsedInfo = 'bar' primary.save()

And when you delete, you only have to delete the Primary domain (because of the all-delete-orphan cascade setting):

Primary.findAllByOftUsedInfo(1).each { it.delete() }

One more thing to note: using the assigned generator like this seems to generate the same database schema:

class Secondary { String littleUsedInfo String moreLittleUsedInfo static belongsTo = [ primary: Primary ] static mapping = { id column: 'primary_id', generator: 'assigned' primary insertable: false, updateable: false } }

Not sure if the behavior is exactly the same, however.

Tuesday, September 6, 2011

MySQL SSL Implementation Incompatibilities

So I just spent half a day trying to figure out why connecting over SSL to a redhat mysql server from a redhat mysql client works, but not from an ubuntu mysql client. Apparently, redhat is configured to use the OpenSSL implementation by default, whereas most other distributions (and Windows) use yaSSL by default — and OpenSSL and yaSSL aren't completely interoperable.

I initially had tried connecting just by specifying the CA certificate:

mysql --ssl-ca=my-ca-cert.pem -h myhost -u myuser -p

This is the minimum you (should) need to connect when you grant permissions for a mysql user with REQUIRE SSL (and the minimum needed for a worthwhile SSL connection — the CA cert allows the client to verify that you're connecting to the authentic mysql server). This worked fine connecting to a redhat box from a redhat box, but failed with the following inscrutable error-message when connecting to a redhat box from an ubuntu box:

ERROR 2026 (HY000): SSL connection error

This error can mean a whole lot of different things, but none of the common problems (like specifying the wrong certificate or using the same CN for both the CA cert and the client/server certs) turned out to be mine. After digging around a bunch, I found mysql bug 40141, where someone else had discovered connecting over SSL from a non-redhat box to a redhat box wasn't working, and had isolated it to a yaSSL-to-OpenSSL incompatibility. Following that trail, I came across mysql bug 29841, which documents the yaSSL-to-OpenSSL issue pretty clearly, as well as a message on the openssl-users mailing list confirming some of the low-level details:

Apparently, when initiating the SSL connection, the OpenSSL-server implementation expects the client always to send a client certificate; if there's no client certificate to send, it expects a blank cert. The yaSSL client, however, doesn't send any client certificate (even a blank one) when there's none to send.

So I ended up working around the problem by creating a client certificate (which ordinarily you'd use as an alternative to password authentication), and specifying it when connecting:

mysql --ssl-ca=my-ca-cert.pem --ssl-cert=my-client-cert.pem --ssl-key=my-client-key.pem -h myhost -u myuser -p

I didn't change the permissions on the mysql user to REQUIRE X509 (so from OpenSSL clients I can still connect without the client cert) — but specifying the cert does allow the connection to be made, and I haven't noticed any other interoperability issues between OpenSSL and yaSSL (so far).