During my last CareerBuilder Hackathon, I worked finished a project that will incredibly help our growing tech teams. If you ever worked in a consulting company, you know how painful it is to wait to gather server credentials and set up them on your machine to be fully operational.

My project is very simple but damn useful: it generates several type of configuration directly from our platform API. You need to have SSH access to servers or connect to database servers? No problems, you provide your SSH user/key path and I take care of generating the file for you according to your tech user profile.

Using the AWS PHP SDK, I'm able to generate:

  • Unix SSH config file
  • Putty .reg file
  • Sequel Pro favorites file
  • Transmit favorites file (soon!)

Then, from 2-3 commands, you have your machine ready to connect to all the servers you will work with. Useful right?

Ironing out .plist files

I used to use the defaults command to hack into my OSX but when it comes to do more complex operations, the defaults command isn't the right tool.

PlistBuddy allows to create dictionnaries, remove, append nodes, etc. Even with this tool, it's not very easy to handle plist trees but it's designed to.

If installed, the plistbuddy command is probaly not available in your $PATH and requires to add the path of target file at the end of each command which could add a lot of noise in your commands if the file is deeply hidden in the OS. That's why, it's better to use variables for the current scope:

  • plistbuddy=/usr/libexec/plistbuddy
  • file=/path/to/my/filename.plist

Example:

#!/bin/bash
plistbuddy=/usr/libexec/plistbuddy
file=/path/to/my/filename.plist

$plistbuddy -c "[command]" $file

Building a Sequel Pro favorite file

For instance, in the Sequel Pro favorites: you can have as many nested favorite folders as you want. This nested structure is for sure reflected into the favorite plist file.

The after-install favorite file (empty in fact) looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
    <dict>
        <key>Favorites Root</key>
        <dict>
            <key>Children</key>
            <array/>
            <key>IsExpanded</key>
            <true/>
            <key>Name</key>
            <string>FAVORITES</string>
        </dict>
    </dict>
</plist>

You can find it there:

/Users/$USER/Library/Application Support/Sequel Pro/Data/Favorites.plist

For the purpose of this article, let's say I want to add a new favorite folder that contains access to my local VM database servers like shown in this screenshot (forget the blurred ones):

sequel-pro-result

Folder creation

Consider the following shell script snippet:

$plistbuddy -c "add :tmp:Name string Local" $file
$plistbuddy -c "add :tmp:IsExpanded string YES" $file
$plistbuddy -c "add :tmp:Children array" $file

Here I create a dictionary called tmp at the root of the file that contains:

  • a string entry with Name as key and Local as value
  • a string entry with IsExpanded as key and YES as value
  • an empty array entry called Children

The tool allows to skip the explicit dictionary declaration by directly adding entries in it. If I wanted to only create the empty dictionary, I'd have typed this:

$bin -c "add :tmp dict" $file

So, let's take a look at the plist file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Favorites Root</key>
    <dict>
        <key>Children</key>
        <array/>
        <key>IsExpanded</key>
        <true/>
        <key>Name</key>
        <string>FAVORITES</string>
    </dict>
    <key>tmp</key>
    <dict>
        <key>Children</key>
        <array/>
        <key>IsExpanded</key>
        <string>YES</string>
        <key>Name</key>
        <string>Local</string>
    </dict>
</dict>
</plist>

If you load this favorite plist file in Sequel Pro, you'll have nothing shown into the FAVORITES section since the Local dictionary in laying at the plist file root instead of being in the Favorite Root/Children array.

I will move it later. First I need to fill the Local folder with the server favorites. But why?
After a few tries, I found out that it was very hard to add items way down a tree than building a sub-tree then appending it at the right place once it's done. Trying to iterate over Favorite Root/Children array keys didn't work out...

Server favorite creation

For the same reason as above, a server favorite tree won't be created directly into the Local/Children array but at the root of the file:

random=$(od -t uI -N 4 /dev/urandom | awk '{print $2}')

$plistbuddy -c "add :server:database string luceo" $file
$plistbuddy -c "add :server:host string luceo-ocb.dev" $file
$plistbuddy -c "add :server:name string luceo-ocb.dev" $file
$plistbuddy -c "add :server:user string luceo" $file
$plistbuddy -c "add :server:id integer $random" $file
$plistbuddy -c "add :server:port string 3306" $file
$plistbuddy -c "add :server:socket string" $file
$plistbuddy -c "add :server:sshHost string" $file
$plistbuddy -c "add :server:sshKeyLocation string" $file
$plistbuddy -c "add :server:sshKeyLocationEnabled integer 0" $file
$plistbuddy -c "add :server:sshPort string" $file
$plistbuddy -c "add :server:sshUser string" $file
$plistbuddy -c "add :server:sslCACertFileLocation string" $file
$plistbuddy -c "add :server:sslCACertFileLocationEnabled integer 0" $file
$plistbuddy -c "add :server:sslCertificateFileLocation string" $file
$plistbuddy -c "add :server:sslCertificateFileLocationEnabled integer 0" $file
$plistbuddy -c "add :server:sslKeyFileLocation string" $file
$plistbuddy -c "add :server:sslKeyFileLocationEnabled integer 0" $file
$plistbuddy -c "add :server:type integer 0" $file
$plistbuddy -c "add :server:useSSL integer 0" $file

About the random number, I couldn't find what this number should be in the Sequel Pro plist file, I believe it's a sort of unique ID but no way to figure out how or what it's generated from; even after digging into Sequel Pro's source code.

The plist file now looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Favorites Root</key>
    <dict>
        <key>Children</key>
        <array/>
        <key>IsExpanded</key>
        <true/>
        <key>Name</key>
        <string>FAVORITES</string>
    </dict>
    <key>server</key>
    <dict>
        <key>database</key>
        <string>luceo</string>
        <key>host</key>
        <string>luceo-ocb.dev</string>
        <key>id</key>
        <integer>4040415438</integer>
        <key>name</key>
        <string>luceo-ocb.dev</string>
        <key>port</key>
        <string>3306</string>
        <key>socket</key>
        <string></string>
        <key>sshHost</key>
        <string></string>
        <key>sshKeyLocation</key>
        <string></string>
        <key>sshKeyLocationEnabled</key>
        <integer>0</integer>
        <key>sshPort</key>
        <string></string>
        <key>sshUser</key>
        <string></string>
        <key>sslCACertFileLocation</key>
        <string></string>
        <key>sslCACertFileLocationEnabled</key>
        <integer>0</integer>
        <key>sslCertificateFileLocation</key>
        <string></string>
        <key>sslCertificateFileLocationEnabled</key>
        <integer>0</integer>
        <key>sslKeyFileLocation</key>
        <string></string>
        <key>sslKeyFileLocationEnabled</key>
        <integer>0</integer>
        <key>type</key>
        <integer>0</integer>
        <key>useSSL</key>
        <integer>0</integer>
        <key>user</key>
        <string>luceo</string>
    </dict>
    <key>tmp</key>
    <dict>
        <key>Children</key>
        <array/>
        <key>IsExpanded</key>
        <string>YES</string>
        <key>Name</key>
        <string>Local</string>
    </dict>
</dict>
</plist>

The next step is to append the server tree to Local/Children array:

$plistbuddy -c "copy :server :tmp:Children:" $file
$plistbuddy -c "delete :server" $file

The above command is pretty obvious. Once you added the server tree to the Local/Children array, you repeat the same operation to add the other server favorite... Repeat this until you're done.

Once you filled your favorite folder, use the same mechanism to append it to Favorite Root/Children array:

$plistbuddy -c "copy :tmp :Favorites\ Root:Children:" $file
$plistbuddy -c "delete :tmp" $file

As a result:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
	<key>Favorites Root</key>
	<dict>
		<key>Children</key>
		<array>
			<dict>
				<key>Children</key>
				<array>
                    <dict>
                        <key>database</key>
                        <string>luceo</string>
                        <key>host</key>
                        <string>luceo.dev</string>
                        <key>id</key>
                        <integer>7530498638</integer>
                        <key>name</key>
                        <string>luceo.dev</string>
                        <key>port</key>
                        <string>3306</string>
                        <key>socket</key>
                        <string></string>
                        <key>sshHost</key>
                        <string></string>
                        <key>sshKeyLocation</key>
                        <string></string>
                        <key>sshKeyLocationEnabled</key>
                        <integer>0</integer>
                        <key>sshPort</key>
                        <string></string>
                        <key>sshUser</key>
                        <string></string>
                        <key>sslCACertFileLocation</key>
                        <string></string>
                        <key>sslCACertFileLocationEnabled</key>
                        <integer>0</integer>
                        <key>sslCertificateFileLocation</key>
                        <string></string>
                        <key>sslCertificateFileLocationEnabled</key>
                        <integer>0</integer>
                        <key>sslKeyFileLocation</key>
                        <string></string>
                        <key>sslKeyFileLocationEnabled</key>
                        <integer>0</integer>
                        <key>type</key>
                        <integer>0</integer>
                        <key>useSSL</key>
                        <integer>0</integer>
                        <key>user</key>
                        <string>luceo</string>
                    </dict>
					<dict>
						<key>database</key>
						<string>luceo</string>
						<key>host</key>
						<string>luceo-ocb.dev</string>
						<key>id</key>
						<integer>4040415438</integer>
						<key>name</key>
						<string>luceo-ocb.dev</string>
						<key>port</key>
						<string>3306</string>
						<key>socket</key>
						<string></string>
						<key>sshHost</key>
						<string></string>
						<key>sshKeyLocation</key>
						<string></string>
						<key>sshKeyLocationEnabled</key>
						<integer>0</integer>
						<key>sshPort</key>
						<string></string>
						<key>sshUser</key>
						<string></string>
						<key>sslCACertFileLocation</key>
						<string></string>
						<key>sslCACertFileLocationEnabled</key>
						<integer>0</integer>
						<key>sslCertificateFileLocation</key>
						<string></string>
						<key>sslCertificateFileLocationEnabled</key>
						<integer>0</integer>
						<key>sslKeyFileLocation</key>
						<string></string>
						<key>sslKeyFileLocationEnabled</key>
						<integer>0</integer>
						<key>type</key>
						<integer>0</integer>
						<key>useSSL</key>
						<integer>0</integer>
						<key>user</key>
						<string>luceo</string>
					</dict>
				</array>
				<key>IsExpanded</key>
				<string>YES</string>
				<key>Name</key>
				<string>Local</string>
			</dict>
		</array>
		<key>IsExpanded</key>
		<true/>
		<key>Name</key>
		<string>FAVORITES</string>
	</dict>
</dict>
</plist>

Extra: the password too!

Maybe you noticed that there were no password in the plist file, which is good for security purposes. Passwords should only exist in the Keychain.

To add an item into the Keychain, I use the security add-generic-password command and subcommand. I found this trick on Stackoverflow:

$ security add-generic-password -U -T "/Applications/Sequel Pro.app" -s "Sequel Pro : %favorite_name% (%uid%)" -a %user%@%hostname%/ -w %password%

Replace the variables (surrounded by %) by your values. I didn't really figure out the -a (account) option of the subcommand because in some case, if you do SSH tunneling, the -a option value becomes -a @127.0.0.1/.
You might wanna play with it to guess what's best for your configuration needs.

Beware of the -s option value format. Apparently, this is strict enough. When I tried to remove the space before the colon, the Keychain entry wasn't recognized by Sequel Pro anymore!

Well, that's all folks. Now you can play around with plist files and do much more than flag switching using defaults.

Thanks for reading and don't hesitate to ask questions.


Joris Berthelot