Monday, October 19, 2015

Simple and secure MySQL database management using SSH

I manage a number of MySQL databases on a daily basis.  While I feel more at home on the command-line and am used to manipulating MySQL databases using the standard mysql CLI tool- others may be more comfortable with using their GUI based tools.

I get many requests to open up the database for remote access.    For a test or development environment, this is OK - but I usually deny this request for production servers unless the connection being made will be secured in some way.  MySQL databases are not typically setup securely (it can be, with SSL) and the connection is sent over the wire in the clear; this means an attacker can assemble the packets and playback what was being transferred.  Most of the time, the connection to the MySQL database is made locally by applications running on the same server so this is of little concern.

But there is a simple and convenient solution to allowing access in insecure environments.  On Linux and Mac systems, you can do the following:

ssh -L localport:localhost:remoteport user@remotehost.com

The -L flag sets up local port forwarding from the local port to the remote port of the remote server.

Then configure your MySQL client to connect to the database locally on port 3306.  It works because port 3306 on the localhost is set up to forward to 3306 on the remote host. 

For example,  I will do "ssh -L 3306:localhost:3306 user@server.com" to setup a local port forward to the default MySQL port on my computer to the remote server's port 3306. Once the connection is established, it looks like any other SSH connection.

Of course, a requirement for this to work is that port 3306 on my local machine must not be occupied by any other running service.  If you are already running MySQL on your local machine on port 3306, it will fail because port 3306 is likely occupied.  If this is the case, you simply have to change the "localport" to something else.

Once my port forwarding session is established, I will use my "favorite" MySQL tool and connect to localhost on port 3306 using the credentials of the database I am connecting to.

Wednesday, October 29, 2014

Mac Ports - llvm-3.5 failed to install

Recently, I've upgraded to OSX Yosemite (great update for OSX, by the way) and issued a mac ports update (port selfupdate && port upgrade outdated) and ran into an issue with upgrading llvm-3.5.  Here's what it looked like:

sudo port clean llvm-3.5 && sudo port install llvm-3.5
--->  Cleaning llvm-3.5
--->  Computing dependencies for llvm-3.5
--->  Fetching archive for llvm-3.5
--->  Attempting to fetch llvm-3.5-3.5-r216817_0+assertions.darwin_14.x86_64.tbz2 from http://mse.uk.packages.macports.org/sites/packages.macports.org/llvm-3.5
--->  Attempting to fetch llvm-3.5-3.5-r216817_0+assertions.darwin_14.x86_64.tbz2 from http://nue.de.packages.macports.org/macports/packages/llvm-3.5
--->  Attempting to fetch llvm-3.5-3.5-r216817_0+assertions.darwin_14.x86_64.tbz2 from http://packages.macports.org/llvm-3.5
--->  Fetching distfiles for llvm-3.5
Error: org.macports.fetch for port llvm-3.5 returned: Subversion check out failed
Please see the log file for port llvm-3.5 for details:
    /opt/local/var/macports/logs/_opt_local_var_macports_sources_rsync.macports.org_release_tarballs_ports_lang_llvm-3.5/llvm-3.5/main.log
To report a bug, follow the instructions in the guide:
    http://guide.macports.org/#project.tickets
Error: Processing of port llvm-3.5 failed

Here's how I managed to fix it:

cd $(port dir llvm-3.5)
sudo vim Portfile

On line 70 there is a config option starting with "svn.url".  The URL specifies http:// - you need to change it so it uses the https protocol.   It should look similar to this now:

svn.url              https://llvm.org/svn/llvm-project/llvm/branches/release_${llvm_version_no_dot}

After making that change, I re-ran port upgrade outdated and it was able to pull the file down.  It is unclear at this point why it fails to checkout the files using SVN over http. 

Tuesday, May 6, 2014

Running local PowerShell scripts

Although I work with Linux primarily, any good system administrator always knows a little bit about the Other Operating System(tm).  One of the most useful thing to come out of Windows in years is PowerShell and running PowerShell scripts.  In Windows 8 (and 7 to an extent), there is a security feature that prevents any PS scripts from running non-interactively- something called the Execution Policy. 

Running a script will probably result in something like this: 

. : File C:\Users\jadmin\PowerShell\some-random-script.ps1 cannot be loaded because running scripts is disabled on
this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:3
+ . .\some-random-script.ps1
+   ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [], PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess


Get-ExecutionPolicy -List should return an output that may resemble this:

            Scope                                             ExecutionPolicy
            -----                                             ---------------
    MachinePolicy                                                   Undefined
       UserPolicy                                                   Undefined
          Process                                                   Undefined
      CurrentUser                                                   Undefined
     LocalMachine                                                RemoteSigned

When it is Undefined, it defaults to Restricted- which means no scripts are allowed to run.  By default, all levels are set to Undefined.  Here you can see LocalMachine is set to RemoteSigned.  You can set it per-process or per-user, which is neat but out of the scope of this blog post.

In order to allow your machine to run locally created PowerShell scripts, you'll need to open a PowerShell window as an Administrator (as easy as right-click, Run As Administrator).   UAC will prompt you (you didn't disable UAC, right?).  Next, type "Set-ExecutionPolicy RemoteSigned".  Type Y and hit Enter (or just hit Enter as "Y" is the default). 

Now you should be able to run locally created scripts.  

In many online resources, there are people advising you to set your ExecutionPolicy to Unrestricted.  This is a mistake, especially if only for locally created PowerShell scripts. Unrestricted allows anything to run as a script, including those from remote sources such as Outlook,  Internet Explorer, and so on.  If you happen to forget to set it back, you'll be at risk. 

Wednesday, December 18, 2013

Easiest method for creating OSX Mavericks USB Installer

Here is the easiest method to create your OSX Mavericks installer:

sudo "/Applications/Install OS X Mavericks.app/Contents/Resources/createinstallmedia" --volume "/Volumes/Untitled" --applicationpath "/Applications/Install OS X Mavericks.app" --nointeraction

If that's what you were looking for, then you really don't need to read any further.  If you're a newcomer and need a little more information, read on:

Before the above command will work for you, you need to plug in your USB stick and format it using Disk Utility.  You'll need a minimum of 8GB, or it will not work.   Partition it and format it using Mac OS Extended (Journaled).   Then run the above command, adjusting the path after --volume to the path to your mounted USB stick (NOT /Volumes/Macintosh HD or whatever your system drive is!).

The second thing you need to do is to download the app through the Mac App Store.  You can do this even if you have already installed OSX Mavericks;  once the installer has downloaded and launched the welcome screen, hit ⌘Q to quit from the actual installer (Or Menu -> Quit).  The files will still be at /Applications.

Once you're done (it took me around 20 minutes for the creation process to complete), plug it into another system and reboot, holding Option to bring up the disk selection.  Select the disk and you should be off to installing OSX Mavericks from USB.

Tuesday, December 17, 2013

Do you have mouse lag issues in Mac OSX?

There has been reports of strange mouse lag with Apple OSX;  it's far more noticeable if you're coming from Windows for the first time. The way the mouse responds and moves across the screen on OSX looks and feels different than on Windows or Linux.  It seems from system to system, and even from mouse to mouse.  I've had more success with wired Logitech mice than anything, and of course, the trackpad doesn't seem to suffer from much lag at all.

When I moved to OSX almost exclusively about 3 years ago, I was driven mad by the mouse lag.  I almost switched back!  Over time, I have gotten more or less used to it.

There were various work-arounds, like BetterTouchTool and MagicPrefs but I've never found them to be sufficient.  They were great for adding functionality and gestures to the Magic Mouse/Trackpad, though.  The responsiveness has gotten better over the last few OSX releases (particularly Mountain Lion and Mavericks) in my opinion.

 For you newcomers, there is fortunately a new solution that I've been testing for a few weeks now:  SmoothMouse

It installs as a System Preferences item.  You can set the acceleration to be more Mac-like or Windows-like, and even the ability to disable mouse acceleration altogether. One of the things I've noticed is a lower overall lag in mouse responsiveness, especially while moving windows around.  I highly recommend it for people coming from the Windows universe, as it will make your mouse more or less feel like it does in Windows.

Monday, December 16, 2013

.htaccess file for LDAP and IP restriction

I get asked to setup "secure" directories on a daily basis, for various individuals.  It ranges from restricting access by IP address to specific usernames and sometimes a combination of things.  Here is my "skeleton" access snippet that I use so I don't have to memorize it or keep hitting Google for it.

Order deny,allow
Deny from all
#AuthName "Authentication"
#AuthType Basic
#AuthBasicProvider ldap
#AuthLDAPBindDN "cn=binduser,cn=Users,dc=institute,dc=com"
#AuthLDAPBindPassword "changeme"
#AuthLDAPURL "ldaps://ldap:686/cn=Users,dc=institute,dc=com"
#Require ldap-attribute someattribute=somevalue
#Require valid-user
Allow from 192.168.1.0/24
Satisfy Any

I save this in a text file and I copy/paste it whenever I need to.  (OSX terminal shortcut:  `cat filename | pbcopy`)  In an httpd.conf file, it needs to be enclosed by a <Directory "/path/to/secure"> </Directory>.  Uncomment or comment out the sections you need.

If you want the authentication to be secure, you'll need to redirect the non-HTTPS page to an HTTPS page, then include the directive on the ssl.conf (or whichever vhost you've setup for SSL connections :443).  Otherwise, anything entered in the password prompt will go across the wire in the clear.


Wednesday, November 6, 2013

PhpStorm7 with Github Enterprise SSL issue

If when trying to setup a remote Github repository with PhpStorm7, in Preferences (in the Version Control section), you'll run into this error: 

"Can't login: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target"

The error is a little cryptic, but it has to do with Java and the certificate keystore that it uses for CA certificates.  A quick solution for Mac OSX is to import the server certificate into your keystore.  The utility to use in this case is keytool (which should come included).   The full command line is:

sudo keytool -import -alias "github" -file server_certificate.cer -keystore /Library/Java/Home/lib/security/cacerts

Restart PhpStorm7 and try again.  You should see the following.
If you don't have the actual certificate file, you can obtain it by going to your Github Enteprise instance via your browser and download it.  For example, in Safari, click on HTTPS in the address bar.
Then on "Show Certificate".
Finally, drag and drop the image of a certificate to your desktop or Finder.  Import as described above with the keytool.