Nate Silva

How to destroy your database in two clicks using Querious

Open your database in Querious. Then:

1. Click “Users.”
2. Say to yourself, “I wonder what the button with two squares on it does.” Please don’t click the button. Because the instant you do, all of your rights are revoked and there is no way to recover them.

I hope you have another admin login. Otherwise you’re screwed.

Yes, I did this on an Amazon RDS instance. Thank goodness it was a test database and I was just experimenting with Querious. I never imagined such a dangerous operation would happen without warning.

Why on Earth doesn’t Querious have a warning or an “Apply” button so the change doesn’t happen instantly?

See the equivalent screen in Sequel Pro, which has an “Apply” button, making it much safer.

(I suspect you could also screw up your database by un-checking some of the other boxes. Again, there is no “Apply” button in Querious, so you will instantly change your rights.)

How to build the pscyopg2 Python PostgreSQL interface on Mac OS X Snow Leopard

These instructions may work on earlier versions of OS X, but I’ve only tested on Snow Leopard.

A common problem for Python programmers using Mac OS X is how to install psycopg2, the standard Python interface for PostgreSQL.

If you Google for the instructions, you’ll get all kinds of advice, most of which involves using MacPorts or installing a complete server installation of PostgreSQL on your Mac.

I don’t want to do that.

I don’t want to run a database server on my local Mac. I want only the necessary PostgreSQL client libraries and I want to use the OS X native build system, not MacPorts.

It’s actually easy to do this. The instructions may look complicated, but it’s only because I’ve added a lot of explanation to them.

Step 1: Install the PostgreSQL client library

1. Download the PostgreSQL source code from http://www.postgresql.org/ftp/source/. Don’t download Mac OS X binaries, as that would be a complete server installation.
2. Un-tar the source code and cd into its directory. Then run:
   • CFLAGS="-arch i386 -arch x86_64 -arch ppc" ./configure --with-openssl
     • This CFLAGS setting will get you a universal binary. Without it you would only get a 64-bit Intel binary (assuming you have a 64-bit Intel Mac), or a 32-bit Intel binary (if you have a 32-bit Intel Mac). We also include PowerPC support, for the unlikely event that you have a program that runs under Rosetta that needs to use the PostgreSQL libraries.
3. We are going install only the client parts of PostgreSQL. These instructions are based on the PostgreSQL manual, section 15.5.:
   • sudo make -C src/bin install
   • sudo make -C src/include install
     • In my experience, the error about utils/fmgroids.h can be ignored.
   • sudo make -C src/interfaces install
   • sudo make -C doc install

Step 2: Install Psycopg2

1. Download and un-tar the Psycopg source code.
2. Edit the psycopg2 setup.cfg file. Add or update the following lines:
   • have_ssl=1
   • pg_config=/usr/local/pgsql/bin/pg_config
3. Build and install it:
   • python setup.py build
   • sudo python setup.py install
4. Test it:
   • $ python
   • >>> import psycopg2
   • >>> psycopg2.__version__
'2.0.14 (dt dec ext pq3)'

Looks good!

1 note

How to make an Intel wireless card connect to an Apple Airport base station in WPA2 mode

With some notebooks (including a Lenovo Thinkpad and a recent Gateway model) we’ve had problems getting Wi-Fi connected to our Airport Extreme base station.

In the past, we enabled both WPA and WPA2 on our base station and then manually set the laptops to use TKIP (WPA) mode (previous post describing how to do this). This worked until we decided to turn off WPA mode, supporting only WPA2, due to newly-discovered vulnerabilities in WPA/TKIP.

Once again, our notebooks with Intel 4965AGN and Intel 5100AGN wireless cards were unable to connect.

Then one of my awesome coworkers, Matt, discovered a setting that fixes everything: turn on an option called “FIPS compliance.” Here’s what to do:

1. Open Network and Sharing Center (NSC).
2. From the list at the left-hand side of NSC, choose Manage wireless networks.
3. Right-click on your network and choose Properties. (If your network is not in the list yet, you will have to click on “Add” and enter your network information and password before continuing.)
4. On the Security tab, set Security Type to WPA2-Personal and Encryption Type to AES.
5. Still on the Security tab, click the Advanced Settings button.
6. Check the box that says Enable Federal Information Processing Standards (FIPS) compliance for this network.
7. Click OK several times to close the dialog boxes.

How to use bpython as your Django shell

$ DJANGO_SETTINGS_MODULE=settings bpython

Lenovo Thinkpad with Intel wireless won’t connect to an Airport Extreme base station

Update: This method is no longer recommended due to a recently-discovered vulnerability in WPA/TKIP. I found and blogged about a better solution here: How to make an Intel wireless card connect to an Apple Airport base station in WPA2 mode.

We had a problem with a Lenovo Thinkpad T500 notebook that has Intel wireless networking built-in.

It could not connect to our Airport Extreme wireless network that is secured using WPA/WPA2 encryption. When you try to connect, the Windows wireless status window alternates between “Connecting” and “Acquiring IP address” forever.

The solution is to change the authentication method from AES to TKIP. To do this:

1. Open Windows’ Network Connections window. One way to do this is to open Control Panel > Network and Internet Connections.
2. Right-click on the wireless network connection and choose “Properties.”
3. On the “Wireless Networks” tab, under “Preferred Networks,” locate your network and click “Properties.”
4. Change “Data Encryption” from AES to TKIP and click OK to close the dialogs.

PHP sessions timeout too soon, no matter how you set session.gc_maxlifetime

The scenario

You’re running Debian Linux or Ubuntu Linux. You want PHP sessions to last longer than the default 1440 seconds (24 minutes). So you do this:

ini_set('session.gc_maxlifetime', 10800);    # 3 hours

With this setting, sessions should remain active for at least three hours, as long as users don’t close their browser.¹

But no matter what you do, sessions keep getting deleted after 24–54 minutes. It seems PHP is ignoring the gc_maxlifetime setting.

Why this happens

Debian and Ubuntu Linux override PHP’s session behavior. If you look closely, you’ll see that session.gc_probability is set to 0, meaning PHP’s garbage collection will never run. Instead, there’s a Debian-specific cron job in /etc/cron.d/php5 that runs every 30 minutes!

The cron job does garbage collection based on the global session.gc_maxlifetime in php.ini. The session.gc_maxlifetime in your app is ignored.

The solution

While you could disable the cron job and/or modify php.ini, I’d prefer to fix the problem without modifying system defaults. A better solution is to create your own sessions directory, somewhere outside the normal one, and then locally enable PHP’s session garbage collection.

To do this, set session.gc_maxlifetime, session.gc_probability, session.gc_divisor, and session.save_path:

# Session lifetime of 3 hours
ini_set('session.gc_maxlifetime', 10800);

# Enable session garbage collection with a 1% chance of
# running on each session_start()
ini_set('session.gc_probability', 1);
ini_set('session.gc_divisor', 100);

# Our own session save path; it must be outside the
# default system save path so Debian's cron job doesn't
# try to clean it up. The web server daemon must have
# read/write permissions to this directory.
session_save_path(APP_PARENT_DIR . '/sessions');

# Start the session
session_start();

How to get iChat video chat working behind a SonicWALL firewall

We couldn’t get iChat’s video chat feature to work behind our SonicWALL PRO 3060 firewall. Whenever someone tried to connect, iChat would say it “did not receive a response.”

The solution was to enable the SonicWALL’s “consistent NAT” feature. To do this:

1. Log in to the SonicWALL administrative web page.
2. From the menu at the left side of the page, choose VoIP (which stands for voice-over-IP).
3. In VoIP > Settings check the box that says “Enable consistent NAT.”
4. Click Apply.

That was all we needed to do to enable a user on a Verizon 3G card to video conference with a user on our internal LAN.

Microsoft Internet Authentication Service (IAS) dies or won’t start

Problem

We use the Microsoft Internet Authentication Service (IAS) to provide RADIUS authentication for our wireless network.

From time to time we would notice that IAS had stopped working on one of our servers. In Event Viewer, we would see the following error:

“Service Control Manager, Event ID 7023: The Internet Authentication Service terminated with the following error: Only one usage of each socket address (protocol/network address/port) is normally permitted.”

You could also click on “Internet Authentication Service (Local)” and notice that the green start button was enabled, indicating that the service was not yet started.

You could start the service, but a few seconds later it would stop.

Cause

The problem was caused by the Microsoft DNS server hogging one or more of the ports RADIUS needs! Those would be UDP ports 1812, 1813, 1645 and 1646.

To identify this problem I had used a free program called NirSoft CurrPorts. I could have used Windows’ built-in netstat command, but this is one of those times when a GUI is nice — especially because you can sort by port number.

Why was DNS using those ports? It was because of the recent DNS security update (the one that fixes the Kaminsky port randomization bug). It was Microsoft security update 953230 (MS08-037).

This problem is described in KB56188.

Solution

The solution is described in KB812873.

The short version, for Server 2003, is:

1. In the Registry Editor, go to HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\
2. If there is a value called ReservedPorts, open it. Otherwise, create it as a “Multi-String Value”.
3. Add the following ports to the list: 1812-1813 and 1645-1646

My thoughts on Google App Engine

I recently tried Google App Engine.

Pros and cons

Pros

• The canned environment is really nice. It’s great to focus on development instead of setting up infrastructure.
• Love Python. Like the Django-based templating engine.
• Bandwidth allocation is reasonable for small- or medium-sized apps.
• The data store is great!
  • Because it’s non-relational there’s no ORM layer to get in the way. Once you understand it, it’s intuitive.
  • Reading from the datastore is nice and fast. (But not writing — see next point.)

Cons

• The data store sucks!
  • There’s no way to import/export data.
    • So you have to write your own.
  • Writing to the datastore is extremely slow, so if you write your own import routine, you need to break up your imports so they don’t time out. It took a couple hours to manually load around 2,000 items.
  • The Bulk Data Upload utility is essentially useless, because it can’t handle Unicode and it times out when importing large amounts of data. If I wasn’t importing large amounts of data, I wouldn’t need a bulk upload utility, no?
• During beta, the quotas are so restrictive that it’s only useful for trivial applications at this time.
  • You can’t retrieve more than 1,000 rows from a query (and doing so would probably time-out your request).
  • Apps are limited to 500 MB total storage and 1,000 files.
  • Developers can only create three apps and you can’t delete or rename an app, so use your three wishes wisely.

Other

• If you’re using Google Apps For Your Domain to authenticate, you need to separately set the app up in your domain, which is poorly documented. It seems obvious, but it was confusing when I tried it.

Moving apps to GAE

I considered moving three in-house apps to GAE, but none were feasible.

Build query tool

The first is a build query tool. It lists which components are present in our software builds, organized by category.

Builds have anywhere from 10 to 1,500 components. Our users sometimes pull up the larger builds. The list loads quickly because it’s plain text.

With GAE there’s no way to retrieve it (1,000 row limit) and even if there were, it would probably time out. We could get around this by introducing a search feature, and showing just subsets of the data. But we would lose the ability to browse over the entire contents of a build at once.

And then there’s the nightmare of updating the datastore. Let’s say we add a new build that incorporates a few hundred components. How are we going to update the datastore in an automated fashion? It’s certainly possible with HTTP POSTs but it would take ages because of timeout issues and how slow it is to write to the datastore.

File upload utility

The second is an upload utility. Files are uploaded and then “processed,” which involves storing them into an Amazon S3 bucket and writing a database record.

This was out of the question due to the 10 MB file size limit. And the 500 MB overall limit might be an issue — we occasionally get giant files up to ~750 MB. (The 1,000 maximum file count is fine; once files are processed they would be deleted from the GAE application space.)

Finally, getting a responsive progress bar during an upload is a tricky problem that generally requires some help from the server-side. I don’t know how to do it with GAE, or if it’s even possible. Of course it’s possible to upload without a progress bar, but users need the reassurance of a progress bar when dealing with huge files.

Historical data utility

The third is a historical data utility. It contains old (going back to 1995!) customer records, notes and invoices. These come from an obsolete accounting system that has been decommissioned, so the web UI is the only way our users can get to it. While not frequently used, it is sometimes handy to pull up old data and chart trends.

Application wise, this should work with App Engine. The row, data size and other limitations don’t come into play.

Unfortunately, there are a few hundred thousand rows in the database. It’s around 125 MB so it would not hit quotas but uploading that data could take weeks.

Summary

Bottom line: I would love to use Google App Engine, especially for in-house apps where we can authenticate against our Google Apps domain. But it’s too limited right now — at least for the types of apps I tried. I’m still on the lookout for other apps that might be a better fit.