May 16, 2010

Login without password using SSH/OpenSSH

Accessing a hosting account is a pain if you always have to remember the username and passwords. Also, if you want to update something with a script, it helps to be able to just log in directly. Using public keys, this is pretty easy :-).

To enable the remote login, you must create a pair of keys, one of them you'll place on the remote system. If you don't have a keypair generated, you need to create one. If you do have one handy, you can keep using it. By default, they will be in:
  • ~/.ssh/identity and ~/.ssh/identity.pub (any older DSA key).
  • ~/.ssh/id_rsa and ~/.ssh/id_rsa.pub (a newer RSA key).

Generating keys

If you don't have any of these (or no ~/.ssh/) you need to generate one. You can create an RSA key like this:

myuser@localmachine:~$ ssh-keygen -t rsa

You should see something like the following:


Generating public/private rsa key pair.
Enter file in which to save the key (/home/myuser/.ssh/id_rsa):
Created directory '/home/myuser/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/myuser/.ssh/id_rsa.
Your public key has been saved in /home/myuser/.ssh/id_rsa.pub.
The key fingerprint is:
xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx myuser@localmachine
The key's randomart image is:
+--[ RSA 2048]----+(...)+-----------------+


By default, it'll create a pair of files without a passphrase. That way you can use them in scripts without having to store or enter the password.

Copying the keys to the remote machine

Once you have the keys, you can append them to the remote server's authorized key list. Assuming you have the files in the default location, you can do this:

myuser@localmachine:~$ ssh-copy-id -i ~/.ssh/id_rsa.pub username@remotemachine


To complete this, you'll have to enter the remote user's password to log in. It'll copy the file for you, creating a directory and changing permissions if needed. The contents will be appended to  ~/.ssh/authorized_keys2 on the remote machine. You should see something like this (depending on whether or not you've logged in there before):


The authenticity of host 'remotemachine (xxx.xxx.xxx.xxx)' can't be established.
RSA key fingerprint is xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'remotemachine,xxx.xxx.xxx.xxx' (RSA) to the list of known hosts.
username@remotemachine's password: 
Now try logging into the machine, with "ssh 'username@remotemachine'", and check in:
  .ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.


Once that's done, you can log in remotely without entering a password, eg:

myuser@localmachine:~$ ssh username@remotemachine uptime
 01:05:12 up 31 days, 11:21,  0 users,  load average: 0.00, 0.00, 0.00

Troubleshooting

Some of the common problems:

  • The remote server doesn't allow public key authentication.
    You'll probably have to update the SSH configuration. Edit the file  /etc/sshd/sshd_config and uncomment or add the following lines:

    RSAAuthentication yes
    PubkeyAuthentication yes

    Now restart the SSH server:

    /etc/init.d/ssh restart

  • Incorrect file permissions
    The .ssh file must not be writable by other users on the remote machine. If it is, you'll probably see the following in /var/log/auth: 

    Apr 13 01:13:07 localhost sshd[18461]: Authentication refused: 
     bad ownership or modes for directory /home/localmachine/.ssh

    To fid that, log in to the remote machine normally and run:

    cd ~
    chmod 700 .ssh

  • Unsupported  key type.
    Older machines may not support RSA key files. In that case, update the machine, or create and use DSA keys: 

    ssh-keygen

    Copy the file using ssh-copy-id like before. And then, upgrade as soon as possible!!

Use at your own risk, obviously :-).

MySQL strings and upper case

MySQL strings are compared case insensitively. A field with a value of "something" is equal to "SOMETHING", making it difficult to compare upper and lower case strings. A simple workaround is to use a hashing function to compare them. MD5 is overkill for this, but it works :P

Finding all records with an upper cased field:
SELECT IdField, SomeField 
  FROM DatabaseTable 
  WHERE MD5(SomeField)=MD5(UCASE(SomeField)) 

Converting all field values to pseudo-proper case (first character caps, rest lower case):
UPDATE DatabaseTable 
  SET SomeField = CONCAT(UCASE(MID(SomeField,1,1)), LCASE(MID(SomeField,2))) 
  WHERE MD5(SomeField)=MD5(UCASE(SomeField))

May 13, 2010

Disabling the phpMyAdmin timeout after 1440 seconds

The timeout is cookie-based and can be disabled by storing the MySQL authentication information in the config.inc.php file. To do this, just add the following to the file:


$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'your-database-user-name';
$cfg['Servers'][$i]['password'] = 'your-database-password';

Note: doing this will remove the login prompt, so only do this if your phpMyAdmin installation is locked down with a server-based authentication!

May 12, 2010

106784

I went to http://www.random.org/ and created a 6-digit random number.

106784

And here we are.