Databases setup for ContentBox using Amazon RDS PostgreSQL

I've been working most of the weekend on getting ContentBox working with a PostgreSQL database. I'm using Amazon RDS on this project, which is new to me. I'm using SQLPro Studio over an Amazon VPN connection (IPsec between pfSesnse and Amazon VPC -- more on that in another post). While trying to get Hibernate ORM to create the tables properly, I've wanted to drop the database and recreate it. Unfortunately, when trying to do that, I got this error message:

DROP DATABASE cbblog;
ERROR: database "cbblog" is being accessed by other users
DETAIL: There are 3 other sessions using the database.

Sup, bro?! It's just me, and I shut Tomcat down, so who's "using the database"? Using this statement, while connected using the simpiler PostgreSQL client psql, I can list them out:

SELECT pid, usename AS username, application_name AS appname, datname AS dbname, client_addr
FROM pg_stat_activity
WHERE NOT pid = pg_backend_pid();

That's helpful. It was me running SQLPro Studio; it makes a bunch of connections when rendering the object tree. If I just close the app, the connections go away. If it wasn't something local, I could aggressively kick them off like this:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'cbblog';

With the blocking user connections disappeared, I can prep a clean database for ContentBox to use:

DROP DATABASE cbblog;
-- Make sure it's really gone
SELECT datname AS dbname
FROM pg_database
WHERE datistemplate = false;
-- Get rid of the role for a fresh start
DROP ROLE cbblog;
--- Setup the new user and its database
CREATE ROLE cbblog WITH LOGIN PASSWORD 'foobar' NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT cbblog TO postgres;
CREATE DATABASE cbblog WITH OWNER = cbblog ENCODING = 'UTF-8';

No, that's not the actual password. Hopefully I can get ContentBox to play nice with PostgreSQL so I can migrate my crusty old blog. It'll be nice to get off Open BlueDragon and Tomcat 6. ;)

In retrospect, this is a silly post. Of course my SQL client is going to cause the database it's using to be in use. But maybe the diagnostic tricks will be helpful to the next sucker. I immediately assumed this was an Amazon RDS problem. Turns out, it was a me problem.

Manual Time Machine Cleanup

I recycled an old Time Machine disk (a beautiful G-Technology aluminum enclosure with a beefy replacement HDD inserted) that had backups from another machine. It didn't have enough room left for my new MacBook Pro. I thought I'd be clever and fix it up:

sudo rm -rf /Volumes/Time\ Machine/Backups.backupdb/old-mac/2015-*

Lo and behold, i got permission denied. Turns out, the proper way to clean up a Time Machine volume is to use tmutil. Like so:

sudo tmutil delete /Volumes/Time\ Machine/Backups.backupdb/old-mac/2015-*

Splendid.

New Mac Setup

My old MacBook Pro died. I needed to get a replacement and get back to work, like, immediately. I picked up a new MacBook Pro. It didn't take too much effort to get backup up and running, but I did make some different choices on initial setup:

  • Turned on FileVault: I hadn't encrypted my home directory before, or anything else. With the new laptop, I turned on whole-disk encryption.
  • Matched Username: I had previously used joseph as my username on my laptop and jlamoree on my iMac workstation. This discrepency caused me to maintain two separate project build properties (based on running Ant reading ${user.name}.properties). The matching username should make development a little easier.
  • Disabled Chatty Cathy: launchctl unload -w /System/Library/LaunchAgents/com.apple.gamed.plist
  • Switched 1Password Formats: I took this opportunity to switch from Agile Keychain to the newer OPVault format.
  • Left VMware Fusion Behind: My team uses Vagrant with VirtualBox for local development. This works very well. I had intended to try using the Vagrant plugin for VMware Fusion -- even purchased a license for it -- but I found that it would be easier to use the same environment my teammates use, rather than craft special version just for my personal setup. Also, the upgrade purchase costs were getting me down.

Here are the applications I installed, in roughly the order in which I installed them:

Within Sublime Text, I first installed Package Control, of course. Then these packages:

Some useful, I think, preferences for Sublime Text:

"draw_white_space": "all",
"highlight_line": true,
"show_full_path": true,
"trim_trailing_white_space_on_save": true

Finally, with the Sublime Text tips, put a link to the CLI binary in /usr/local/bin:

sudo ln -s /Applications/Sublime\ Text.app/Contents/SharedSupport/bin/subl /usr/local/bin/sublime

Kinda following on with the Sublime Text steup, I've been looking for a good way to preview content written in Markdown. I gave a few apps/packages a try, but I think I've settled on Marked being my previewing utility of choice. In fact, I'm using it right now.

I had previously used Caffeine to prevent my computer from sleeping (which is very disruptive to open SSH connections and mounted network volumes). I tried Amphetamine this time, since it's being kept current and seems pretty popular. It has many nice features over my old solution.

DNS for Private Networks through pfSense

I bought a new router for my home office. It's a Celeron J1900 2.0 GHz, 4 x 1 Gbps Intel NICs, 4GB DDR3 RAM, 8GB mSATA SSD sold by Protectli and preloaded with pfSense 2.3. It's a solid product. You can pick one up at Amazon. This replaced an older single-board computer running pfSense one point something. My motivation was to get something I could use to establish a site-to-site IPSec VPN between my home office network and Amazon VPC. More on that later.

The trouble I had after the initial setup of the new router was in DNS response queries for things that have private network addresses (RFC 1918). I'll give you a practical example so you don't think it's crazy to have a public DNS server returning addresses for private networks: one of my Vagrant configurations has a lot of private addresses that I share with my engineering team. We use a public DNS server to hold our development IP addresses. This keeps them all consistent and nobody needs to hack on their /etc/hosts (or gods forbid, C:\WINDOWS\SYSTEM32\drivers\etc\hosts) file. Anyway, responses for these internal addresses weren't arriving when my workstation made a request for them using the pfSense Unbound configuration. They mention this in the documentation. The solution is simple:

I'll write about setting up the VPN to AWS EC2 soon. Let me know if that seems interesting.

Lucee Database Passwords

Often times I need to dynamically create a datasource upon the deploy of an application within Lucee. It's easy to setup the datasource in Application.cfc, as documented. However, you'll need to know the encrypted form of the database user's password. That can be a challenge to generate within an unattended script. But with CommandBox, our fucking troubles are over, Dude.

Open up the CommandBox REPL and execute the following to encrypt your database user's high-quality password using Lucee's secret key (sdfsdfs):

CFSCRIPT-REPL: be = createObject("java", "lucee.runtime.crypt.BlowfishEasy").init("sdfsdfs"); CFSCRIPT-REPL: be.encryptString("Password1!");

The sequence of hex characters can be dropped into your Application.cfc. Like so:

component {   this.name = "FooBar";   this.datasource = {     class: "com.microsoft.jdbc.sqlserver.SQLServerDriver",     connectionString: "jdbc:sqlserver://10.0.0.0;databasename=fooBar",     username: "sa",     password: "encrypted:4b613047d2594b9b905be02f11575afb5e88f425739cc9e2a1f3f167530b03c9"   }; }

If you ever forget your database password, you can recover it simply:

CFSCRIPT-REPL: be = createObject("java", "lucee.runtime.crypt.BlowfishEasy").init("sdfsdfs"); CFSCRIPT-REPL: be.decryptString("4b613047d2594b9b905be02f11575afb5e88f425739cc9e2a1f3f167530b03c9");