Murphy walks among us. You know Murphy, the famous "optimist" who helps make every bad situations even worse? Well, after a disaster has occurred is not the time to figure out how and where you need to recover your data. Sure, we practice (ok, hopefully we practice!) recovering our database(s) from tape or disk. And during our testing we restore it into a test database or, if we’re lucky enough, into a test server. Great, but how does the application connect to the recovered database if we’ve run into a massive hardware (server) failure?

Before I describe how Domain Name Services (DNS) can help, take a look at a typical database environment like I've shown in Figure 1. This diagram shows each of our databases, named dbServer1, dbServer2, and dbServer3 with 3 database on each one. This particular design gives the Database Administrator (DBA) a lot of flexibility to move databases around for system maintenance, upgrades and recovery. It can also provide a level of security by isolating "sensitive" data from less sensitive data under normal circumstances (think account data from regular user databases that all employees have access to comply with Sarbanes-Oxley requirements)., Of course, if a disaster were to occur, many processes and procedures change until the situation is rectified (the disaster is over and the original database server(s) are brought back on-line.

Database Servers 1

So now, I'll describe how DNS can be used as a tool to help point applications to the correct database server.

As a refresher, DNS responds to requests for IP Addresses based upon a server name. Most people also realize that the reverse is also true; we can send an IP Address to the DNS server and get back the Authoritative Name of the device assigned to the address. In DNS, this is the A-record and is there is always one, and only one, specific name for an IP Address.

That’s nice, but it doesn’t really help us. Wouldn't it be nice if there was a way to have more than one name assigned to a single IP Address? There is and it’s called the Canonical Name Alias, often referred to as the CNAME record by DNS Administrators. And you can have an unlimited number of CNAME entries for each IP Address. (NOTE: Remember that DNS is a special kind of data file and while it can be “unlimited,” having too many records or entries could cause performance problems on your DNS server or network.)

How does that help with the recovery of a database? One part of the answer involves application configuration when the application is installed – whether it's a fat client on a desktop or a web service. Usually it requests the name of the server where the database is stored. For many people doing the install, they enter the physical name of the database server (the information associated with the A-Record in DNS). However when that database server fails, the application will also fail. And even though the data can be recovered to another database from the backups and log files, the connection information has to be re-entered to point to the new server, whether it requires an install or just a change in the application's configuration. Even more worrisome is for a fat-client this could require touching hundreds or thousands of desktop systems to update their configuration. Even with tools to push updates, this could be a lengthy and error-prone process. Worse, this downtime could lead to significantly lost revenue or revenue opportunities.

Now, you’re probably thinking, "I know where this is going. I can just drop my A-record for DBServer2 and recreate it as a CNAME-record to point to DBServer1." And that will work, provided all the databases on DBServer2 are restored to DBServer1. However, it is very rare that DBServer1 has sufficient free resources to recover all the databases from DBServer2. Instead, you end up recovering some database on DBServer1 and some on DBServer3. (See Figure 2)

Failure of DB Server 2

At this point, you wonder "How do you recover some databases to DBServer1 and others to DBServer3?" One solution is to create a CNAME alias for each database stored on each server. While this is more work to manage and track, it provides the most flexibility when you need to move databases from server to server.

While this helps with recovery, it also helps in environments where replication is involved since you already have the database copied to a secondary (or stand-by) server. Updating the CNAME alias allows all the clients to point to the secondary (now primary) database server.

While this has only been focused on recovery, a side-benefit of using this technique also provides an opportunity to migrate databases from one server to another. If you're running into performance issues or need to do a database upgrade or maintenance, you can easily move the database with minimal impacts to client systems.

The final step is to force all the client systems that connect to the database to have their local DNS cache cleared out. This will allow client systems to connect to figure out the IP Address for the new server name. Under Microsoft Windows the command "ipconfig /flushdns" will clear the local DNS cache and find the new IP Address by doing a look-up of the IP Address on the DNS server. Under Linux the command to restart the "nscd" daemon (Name Services Caching Daemon) will clear the cache ("/etc/init.d/nscd restart"). Both should take no more than a couple of seconds to complete.

In short, using a CNAME alias makes managing recovery easier should a critical failure occur. While providing a solution to easily recover a database to a different server, it provides a secondary benefit of abstracting out the physical server name to assist with database engine upgrades or moving databases between servers for performance reasons. And it also reduces the other things Murphy can cause to grown wrong while you're trying to recover your databases.

The message of the day (motd) file is used by the System Administrators to display information to users when the log in to a system using a command line tool (for instance ssh). On older systems, it was a single text file located in /etc/motd. On Ubuntu, after around v10.10, it was changed to a series of scripts that are executed instead of simply displaying a text file. There are many ways it can be invoked (or cause it to be displayed).

One place is from inside /etc/pam.d/sshd where there is a line for the motd:

`` session optional pam_motd.so # [1]11

Commenting this out will disable the motd from being displayed. Another place the information can be stored is in the profile file located in /etc/profile.

It is also in the sshd configuration file (/etc/ssh/sshd_config) so don't forget to update that and then restart the sshd service (/etc/init.d/sshd restart) for any changes to you make to the config file.

I had a problem when I copied a Linux virtual machine from one host to another and made some changes (specifically which networks it could see on the host). Specifically the ethernet devices changed and the udev process couldn't find the old devices, resulting in the network interfaces wouldn't come up.

Logging onto a console will identify that eth0 (as an example), is gone and you have no network interfaces. Examinging the dmesg output with the command dmesg | grep eth will show something similar to the following:

udev: renamed network interface eth0 to eth2
udev: renamed network interface eth1 to eth3

The solution is to edit the file /etc/udev/rules.d/z25_persistent-net.rules and change the configuration information for the relevant NICs in that file so that they are named with the correct interfaces so that /etc/network/interfaces can load the correct network (IP) configuration information. I hope this helps someone else as it was challenging for me to solve this problem. (By the way, this may also occur if you replace a Network Interface Card (NIC) in your system!)

For computers to talk on a network, they need a unique address. The most common type of address is an Internet Protocol, or IP, address. The address consists of a network portion and a device (computer) portion. I use the term "device" only because it could be something other than a computer, like a printer, tablet or smartphone. It isn’t critical to understanding IP Addresses that there is a separate network portion and a separate device portion, but it does become important when you to want to understand how data moves across your personal network and across the internet.

Currently, the most common format of IP address is Internet Protocol version 4 (IPv4). It consists of four groupings of 8 bits (each grouping is called an octet) separated by periods. While it is four groupings of bits, it is more commonly represented as four numbers between 0 and 255 separated by periods. Since I have a permanent IP Address on the internet, my address is 72.1.133.246. Since you’re reading this on my website, you may know it more commonly as www.tbruce.com (the name associated with the IP Address, and viewable as http://www.tbruce.com in your web browser’s window).

There are two types of IP Addresses: Static IP addresses and Dynamic IP Addresses. Static IP Addresses are permanently assigned to you by your Internet Service Provider (ISP). The only reason you need a static IP Address is if you want to provide a service (like email or manage a web server) on the Internet. This ensures people can always find you since your IP Address will always be the same. Most people, however, have a dynamic IP Address given to them when the turn on their computer, router or dial into their Internet Service Provider. The ISP keeps track of a list of addresses and gives it out to users (devices) when they connect to the internet.

Many users who have a broad-band connection (through a cable modem, DSL, or FIOS/Fiber Optic Service) think they have a static IP address because it’s the same every time they connect. However, it’s just a coincidence and they could get a different IP address at any time.

I’d like to add one last comment about IP Addresses before I explain the significance of the bits. There are two different types of IP Addresses: routable (addresses that are directly accessible on the internet) and non-routable (or what I’m going to call “private”) IP Addresses. (NOTE: This is not the same as a Virtual Private Network or VPN, which is a separate topic). Most homes and businesses have private IP addresses assigned to their employee’s or home computers. Then there is a device called a router that translates the “private” IP Address to a public IP Address and allows users to do things like browse the web and send email, among other activities. These devices are called “routers” because they route, or move traffic, between the two networks (the public and private or between two private networks). These devices may also be described as wireless routers or firewall-routers and are sold by companies like Cisco (Linksys), Netgear, and Buffalo, to name just a few manufacturers.

These private IP Addresses usually start with 10.x.x.x, 172.16.x.x (to 172.31.x.x), or 192.168.x.x (where “x” is some value between 0 and 255). These addresses fall into a range of defined IP addresses for a private (or a non-routable) network. (NOTE: I do NOT use the term secure. The use of a “private” network does not in any way mean your communications are secure from unauthorized users!) This allows communications between your devices (on your private network) and limits the data that “leaks” onto the Internet. In effect, it reduces the amount of “unnecessary” data on the Internet. (NOTE: This does not prevent viruses or other bad software from sending unauthorized data off your computer or network!) If you want more information on network addressing, please see http://www.ietf.org/rfc/rfc1918.txt

This document explains how Internet Protocol (IP) Addresses are unique on a particular network and allows computer or devices to talk to on another. It also provides a basic explanation of how private networks reduce the data traffic on the internet and connect local devices together.

Originally written: May 15, 2008, Finished Editing: October 28, 2008

The ability to manage a vmware instance from the command line can be critical when the GUI or webpage (since VMWare has a built-in web server so you can manage some of your virtual machines) won't work. In this case the following two commands helped me on Windows XP:

C:\> cd "c:\Program Files\VMware\Vmware Server"
C:\Program Files\VMware\Vmware Server> vmrun.exe -h "https://localhost:8333/sdk" -P 8333 -T server -u TJ -p PASSWORD stop "[standard] Ubuntu8/Ubuntu.vmx"

This allowed me to user my username (TJ) to shutdown the system using my local (domain) password.

Additional information on the command line switches is also available at http://itknowledgeexchange.techtarget.com/virtualization-pro/starting-a-vmware-server-virtual-machine-with-vmrun

Additional information can be found in the documents at http://www.vmware.com/pdf/vix160_vmrun_command.pdf or at http://www.vmware.com/products/beta/ws/vmrunCommand.pdf

There are always a variety of ways to get the version of Microsoft SQL Server (MSSQL). The easiest way to get the version is to use MSSQL Enterprise Manager (or SQL Server Studio) and view the version number next to the server name.

There is another way by examing the value of the @@version variable. Specifically you can execute the following SQL statements:

SELECT @@version
SELECT LTRIM(RIGHT(LEFT(@@VERSION,38),9))

The following code will as well, and in a slightly different format:

SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

This will return the data in a slightly different format so you can identify the information related to which type of SQL Server is installed.

But many people will wonder why you'd even want to execute the SQL since it would take longer. My thought is that if you wrapped this up in a CMD file (using OSQL to execute it against a list of servers) you can generate a list of the versions of MSSQL that exist in your environment. This makes it easier for audit questions or if you just need to validate what versions you are using.

Drupal is a piece of software that helps you create and manage content (information) on a website. This falls into a category of software called Content Management Systems (CMS). In reality, it is more of a "framework" to provide specific functionality related to the "pages" you create and manage, to include a consistent look and feel (a theme) as well as additional tools to control and manage the content, or information.

The main website for Drupal is available at http://www.drupal.org. On that website they describe Drupal as "Community Oriented Plumbing," which does fit in with the orientation of a CMS. Part of the power of any CMS is the ability to easily allow anyone (or some small group of people) to create, manage and/or publish content on the World Wide Web (there is a pretty dynamic security model in Drupal to enforce managing content).

Probably a little more than you wanted, but here's the info on Drupal. Some of these links are for me to share with others as well, and this email just is my "historical documentation" in case I have to find it again. <grin>

Here are a few sites that use (or used) Drupal:

http://www.crypticshade.com/info/bio.html

http://www.sudden-thoughts.com/

http://www.tacticaltech.org/

http://www.kpsu.org/ (PSU - Portland, OR site)

http://www.foreignexchange.tv/ (Foreign Exchange with Farheed Zakara)

http://www.elementalevents.com/

http://www.spreadfirefox.com/ (Pro Firefox Advocacy web site)

http://evolt.org/evolt_3.0

http://www.theonion.com/ (Fake news web site)

http://www.linuxgazette.com/ (Linux News website)

http://www.howtoforge.com/ (Linux News website)

http://linuxjournal.com/ (Linux News website)

http://kerneltrap.org/

http://www.bib.kau.se/ (Karlstad University library)

http://jeroencoumans.nl/journal/cool-drupal-sites (Personal website)

Some links to Drupal related sites include:

http://drupal.org

http://groups.drupal.org

http://api.drupal.org/

http://dev.drupal.org

Other Drupal Info:

http://drupal.org/cases

http://drupal.org/node/508

http://drupal.org/community

http://top.drupalsites.net/

http://www.drupalsites.net/

Many people new to Postgres are confused by the security model. They understand the SQL commands that use GRANT, CONNECT and REVOKE to alter permissions (the SQL DSL, or Data Security Language component of SQL) because that is common across various SQL Database Engines. However, Postgres has another layer of security that you need to successfully navigate first.

== pg_hba.conf ==

Postgres uses a configuration file, called the pg_hba.conf file, that identifies how each user (or users) can connect to each database in the postgres engine. It identifies what system (or systems) the user(s_ can connect from what systems and also specifies the authentication method they use for that database. In wriitng (and revising this), it sounds complex, or maybe it's just my style of writing. But the below description should make it clearer.

Basically there are four fields that are checked:

• Username
• Database
• User connection source
• Authentication mechanism

The sample file below (with explanation) may help understand the different methods and sources:

local     all         postgres         ident
local     music_db    music            md5
local     all         dbadump          trust
#local     all         all              ident sameuser
local     all         all              ident

This means that the user _postgres_ can connect locally (over a Unix Socket) to any database, and you will trust that the Operating System has already identified the user (the ident method).

The user _music_ can connect locally to the database music_db using a password encrypted in postgres (the password is encrypted using md5).

The user _dbadump_ can connect locally to any database and is trusted into that database. This is a potential security issue as ANY user on the local system can claim to be the user _dbadump_ and connect to any database. (You'd have to rely on SQL DSL and GRANT permissions to restrict their access at this point!)

The next line is a comment. So it is ignored by engine, but you can use it to document your configuration file if you so wish.

The last line says that any other system user (Linux or Windows) can connect locally to any database (again, their permissions should be controlled through DSL inside each database).

One thing to note (and is specified in the Postgres documentation) is the permissions should be going from MOST restrictive (the tightest definition) to the LEAST restrictive (the potential for more people to match the "rule"). The reason is that the first rule that matches (whether they successfully log in or not!) determines which rule APPLIES (and no further checking occurs!).

Now that I've shown what happens when you connect locally (over a Socket), we'll talk about REMOTE (from another computer) connections.

# IPv4 local connections:
host      all         all         127.0.0.1/8           md5

Any user that connects to any database using localhost (127.0.0.1 to 127.255.255.255) will use an encrypted password stored in Postgres.

A word of warning on this one - I've used the network of 127.*.*.* instead of just 127.0.0.1/32 because some Linux distributions define the localhost (or even the hostname) with something else in the 127.*.*.* network. If you choose to use a CIDR Address of 127.0.0.1/32 or 127.0.0.1/24 (anything between 127.0.0.1 and 127.0.0.255) you could exclude some addresses where they would connect with a different rule.

host      music_db    music       192.168.1.53/32       md5
host      music_db    music       192.168.1.55/32       md5
host      all         timothy     192.168.1.1/24        md5
host      all         dbauser     192.168.1.50/32       trust
hostssl   all         admin       1.1.1.1/0             md5

The user _music_ can connect to the database music_db from either 192.168.1.53 or 192.168.1.55 (and they can authenticate or prove who they are by providing a password that matches their password stored in postgres and encrypted using the md5 algorithm).

The user _timothy_ can connect to ANY database from any computer with an IP Address between 192.168.1.1 and 192.168.1.255. (NOTE: This is only on the local network. If you connect over the Internet, which I will show later, you will have a different EXTERNAL IP Address that the database engine will see.)

The user _dbauser_ can connect to any database from the IP Address of 192.168.1.50 ONLY. But any user that claims to be _dbauser_ on that system will be explicityly trusted into the engine.

The user _admin_ can connect from ANY IP Address in the world and will be able to prove who they are by providing a password that matches up with what is already encrypted and stored inside Postgres. The only difference between this and previous "host" entries is that the connection is encrypted using SSL (Secure Sockets Layer). There is additional overhead associated with using encryption, but especially across the Internet, you really should encrypt the whole session (especially the authentication piece so you don't send a password across the Internet in plain text).

The last line is similar, but it authenticates against a LDAP souce rather than having the password stored in postgres.

== Roles ==

Once the user has a connection to the server/database, a second mechanism is used to identify what objects they can access, basically where they have permissions to view or update data. This is the Data Security Language (DSL) portion of SQL. This is where you allow a user to CONNECT to a database or read data (SELECT permission) or update data (UPDATE or DELETE permissions). These permissions are well explained in the documentation for Postgres or any other SQL Database engine.

After an upgrade, I was having trouble viewing some pages due to a number of errors. They all revolved around missing tables for tinymce.

I was able to solve this by creating two tables: tinymce_role and tinymce_settings.

The SQL to generate these two tables is:

CREATE TABLE `tinymce_role` (
    `name` varchar(128) NOT NULL default '',
    `rid` tinyint(3) unsigned NOT NULL default '0',
    UNIQUE KEY `name` (`name`,`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tinymce_settings` (
    `name` varchar(128) NOT NULL default '',
    `settings` text NOT NULL,
    PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

After running this, I was able to successfully run the update script to correct other missing dependencies.

UPDATE (04 March 2015): I never annotated what version of Drupal this was with. If my memory is correct, this is with something in the 5.x range of versions. Furhter, this was in MySQL, prior to my migration from MySQL to PostgreSQL.

Adding a Squirrelmail plugin is relatively simple. This sample installs the "change_sqlpass" plugin (which requires the "compat" (or compatibility) plugin. First copy the necessary plugins to the server you are installing them onto. A command like:

$ scp plugin timbruce@server:~/download/squirrelmail

Depending on how it's installed, you need to assume the correct user to configure the plugin.

$ sudo su -

On Ubuntu, you need to change to the following directory:

# cd /usr/share/squirrelmail/plugins

Then execute the following command:

# tar -xzvf compatibility-2.0.9-1.0.tar.gz
# tar -xzvf change_sqlpass-3.3-1.2.tar.gz

Detemine which version of SquirrelMail you are running (looking at the login page will help). I'm using Ubuntu version 1.4.10a, so I patch using v1.4. Now I can install compatibility using the following command:

# cd /usr/share/squirrelmail/plugins/compatibility/
# patch -p0 < patches/compatibility_patch-1.4.10.diff

After this, configure SquirrelMail.

# cd ../config
# ./conf.pl

Go to #8 (Plugins) and install change_sqlpass. (NOTE: I also need to update the plugins/change_sqlpass/config.php file with the appropriate parameters to allow it to update the database.)

Enjoy