Avoid clear text passwords in MySQL logging.

What happens when you use the PASSWORD() function to insert a password hash into a table?

• The hash will be written to the table
• The password might be written in clear text to the binlog
• The password might be written in clear text to the general log
• The password might be written in clear text to the slow query log

The query

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));
Query OK, 1 row affected (0.00 sec)

The General log

130128 16:04:41     1 Query     INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))

The Slow query log

# Time: 130128 16:04:41
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.004887  Lock_time: 0.001043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385481;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));

The binlog:

# at 219
#130128 16:04:41 server id 1  end_log_pos 287  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
BEGIN
/*!*/;
# at 287
#130128 16:04:41 server id 1  end_log_pos 315  Intvar
SET INSERT_ID=1/*!*/;
# at 315
#130128 16:04:41 server id 1  end_log_pos 438  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))
/*!*/;
# at 438
#130128 16:04:41 server id 1  end_log_pos 465  Xid = 8
COMMIT/*!*/;

It's possible to do it in a more secure way by using variables.

The query

mysql [localhost] {msandbox} (test) > SET @pwd := PASSWORD('another_secret_password');
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(@pwd);
Query OK, 1 row affected (0.01 sec)

The General log

130128 16:05:18     1 Query     SET @pwd := PASSWORD('another_secret_password')
130128 16:05:30     1 Query     INSERT INTO testpwd(pwd) VALUES(@pwd)

The Slow query log

# Time: 130128 16:05:18
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.000251  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385518;
SET @pwd := PASSWORD('another_secret_password');
# Time: 130128 16:05:30
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.003031  Lock_time: 0.000288 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385530;
INSERT INTO testpwd(pwd) VALUES(@pwd);

The binlog

# at 465
#130128 16:05:30 server id 1  end_log_pos 533  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
BEGIN
/*!*/;
# at 533
#130128 16:05:30 server id 1  end_log_pos 561  Intvar
SET INSERT_ID=2/*!*/;
# at 561
#130128 16:05:30 server id 1  end_log_pos 638  User_var
SET @`pwd`:=_utf8 0x2A41424645453834453346463233423442454338323635383832433244383141414536363744363235 COLLATE `utf8_general_ci`/*!*/;
# at 638
#130128 16:05:30 server id 1  end_log_pos 738  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
INSERT INTO testpwd(pwd) VALUES(@pwd)
/*!*/;
# at 738
#130128 16:05:30 server id 1  end_log_pos 765  Xid = 10
COMMIT/*!*/;

An other trick is to set binlog_format to ROW:

# at 555
#130128 16:30:47 server id 1  end_log_pos 623   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1359387047/*!*/;
BEGIN
/*!*/;
# at 623
# at 672
#130128 16:30:47 server id 1  end_log_pos 672   Table_map: `test`.`testpwd` mapped to number 33
#130128 16:30:47 server id 1  end_log_pos 752   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
p5kGURMBAAAAMQAAAKACAAAAACEAAAAAAAEABHRlc3QAB3Rlc3Rwd2QAAggPAv8AAg==
p5kGURcBAAAAUAAAAPACAAAAACEAAAAAAAEAAv/8BQAAAAAAAAApKkNFNDdGODRBOThDOTI0ODdC
RjI5MUM1QzIyNTY3ODg0RjAxMjdGM0U=
'/*!*/;
# at 752
#130128 16:30:47 server id 1  end_log_pos 779   Xid = 19
COMMIT/*!*/;

And even with the "-v" option for mysqlbinlog the passwords won't show with row based binlogs:

### INSERT INTO `test`.`testpwd`
### SET
###   @1=5
###   @2='*CE47F84A98C92487BF291C5C22567884F0127F3E'

With 5.6 hiding passwords should get done automatically and for all the log types. Unfortunately I didn't get it to work yet. (Bug #68200)

To prevent logging of passwords you need to

• Disable the slow query log
• Disable the general log
• Use variables or disable the binlog
• Or wait on the 5.6 GA version

Installing Multicorn on RHEL6

The Multicorn project makes it possible to write Foreign Data Wrappers for PostgreSQL in Python.

To install Multicorn on RHEL6 the following is needed:

• PostgreSQL 9.2
• Python 2.7
• make, GCC, etc.

Installing PostgreSQL 9.2 is easy as it's available in the PostgreSQL Yum repository.

Unfortunately Python 2.7 is not included in RHEL6. And replacing the 'system' python is a bad idea.

The solution is to do an 'altinstall' of Python. The "--shared" and ucs4 options are required. The altinstall will install a python binary with the name python2.7 instead of just python. This allows you to have multiple python versions on 1 system.

wget http://www.python.org/ftp/python/2.7.3/Python-2.7.3.tgz
tar zxf Python-2.7.3.tgz
cd Python-2.7.3
./configure --shared --enable-unicode=ucs4
make
make altinstall

This will result in a /usr/local/bin/python2.7 which doesn't work. This is due to the fact that the libraries are installed /usr/local/lib, which is not in the library path. This can be solved by modifying the library path.
echo "/usr/local/lib/" > /etc/ld.so.conf.d/usr-local-lib.conf
ldconfig

Installing PostgreSQL 9.2 is easy:
yum install postgresql92-server postgresql92-devel

Installing with PGXN will not work as this commit is not yet included. So we'll have to clone the git repo.
git clone git://github.com/Kozea/Multicorn.git
cd Multicorn

sed -i 's/^PYEXEC = python$/PYEXEC = python2.7/' Makefile
export PATH="$PATH:/usr/pgsql-9.2/bin"
make
sudo make install

The step to set the PATH is not in the Multicorn documentation, but it's needed as pg_config is not in our path. 

The sed line is needed to force the use of python2.7 instead of python (which is 2.6).

And then we can load the extension in the database.
postgres=# CREATE EXTENSION multicorn;
CREATE EXTENSION

And then we can use the examples from the documentation:
postgres=# CREATE SERVER csv_srv foreign data wrapper multicorn options (
postgres(#     wrapper 'multicorn.csvfdw.CsvFdw'
postgres(# );
CREATE SERVER
postgres=# create foreign table csvtest (
postgres(#        year numeric,
postgres(#        make character varying,
postgres(#        model character varying,
postgres(#        length numeric
postgres(# ) server csv_srv options (
postgres(#        filename '/tmp/test.csv',
postgres(#        skip_header '1',
postgres(#        delimiter ',');
CREATE FOREIGN TABLE
postgres=# select * from csvtest;
ERROR:  Error in python: """

DETAIL:  [Errno 2] No such file or directory: '/tmp/test.csv'
postgres=# \! echo -en 'Year,Make,Model,Length\n2013,Volkswagen,Golf,3\n2013,Volkswagen,Passat,3' > /tmp/test.csv
postgres=# select * from csvtest;
 year |    make    | model  | length
------+------------+--------+--------
 2013 | Volkswagen | Golf   |      3
 2013 | Volkswagen | Passat |      3
(2 rows)

How to install PGXN on RHEL6

Installing PGXN on RHEL 6.3 is not as easy as it might sound.

First you need to install the PostgreSQL yum repo:
rpm -ivh http://yum.postgresql.org/9.2/redhat/rhel-6.3-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

Then you need to install pgxnclient:
yum install pgxnclient

The pgxn client has 2 dependencies which are not listed in the package:

• setuptools
• simplejson 2.1

To satisfy the first dependency we need to install python-setuptools
yum install python-setuptools

The second one is not that easy as the simplejson version in RHEL6.3 is 2.0, which is too old. We can use PIP to install a newer version:
yum remove python-simplejson
yum install python-pip python-devel
python-pip install simplejson

And now the pgxn command will work.

MySQL version history (updated)

I've created a graph about the MySQL version history.

It's mysql-graph-history on github.

Please let me know if this is correct or if I'm forgetting some versions.

Untrusted downloads and MySQL

When the MySQL version from your distribution isn't good enough you need to download the latest Oracle MySQL. There are more possibilities like Percona Server and MariaDB, but that's not what this post is about.

The dowload site for MySQL is https://www.mysql.com/downloads/mysql/ and contains to a mirror.php script which ask you if you like to login with a Oracle Web Account and then chooses a mirror for you. You don't have to login. Then you will be redirected to the chosen mirror. In my case this is https://cdn.mysql.com

Firefox will give you a "This Connection is Untrusted" dialog. If you click on "Technical details" it will show the following error:

cdn.mysql.com uses an invalid security certificate.

The certificate is only valid for the following names:
  a248.e.akamai.net , *.akamaihd.net , *.akamaihd-staging.net 

(Error code: ssl_error_bad_cert_domain)

The Qualys SSL Labs confirm the mismatch between the site name and the certificate name. The results can be found here.

There is a good reason this didn't show up when Oracle did testing: I'm using the HTTPS-Everywhere add-on from EFF. So I can't really blame them.

What about just disabling the HTTPS-Everywhere add-on for this site or just adding an exception? That's also not a real solution.

The download page is available both with and without SSL. There are MD5 sum's on the website to verify the integrity of the downloaded files.

To verify if there isn't tampered with the downloaded files you need to verify the MD5 sums. This must be done with the MD5 sum's on the HTTPS site as that guarantees that there isn't tampered with the MD5 sums.

MD5 sums are no longer considered attack proof. It's possible to get collisions (2 different files with the same md5sum).

Verifying the MD5 sums for all files is quite a bit of work.

Some things Oracle could improve in the security of the download service:
1. Make sure that cdn.mysql.com has a valid SSL certificate
2. Always redirect users to the HTTPS download site.
3. Make the md5sums available in 1 file (which can be checked with md5sum and/or fciv.exe, both tools need different file formats)
4. Also make sha1sums available (which can be used by sha1sum and fciv.exe)
5. Sign the checksum files and/or the files themselves with PGP/GPG
6. Fix RFE Bug #60833 and provide YUM and APT repositories (which already supply everything for automatic verification using GPG) Repositories also allow easier updates to new version (e.g. in case of security).

Also I do think that most people are not careful enough with downloading files securely. This is almost like just clicking on every e-mail attachment you get.

There is also good news: www.mysql.com gets a really really high score in the Qualys SSL Labs: The results are here.

How to install MySQL succesfully on a Raspberry Pi

For those starting to learn MySQL and want to install it on a Raspberry Pi: there is a little gotcha you should be aware of.

Disclaimer: Only run command if you know what it does. Always make sure you have backups of your important data.

First you need to put the official Raspbian image on your SD card and then boot the Raspberry Pi.

Then if you try to install mysql with "sudo apt-get install mysql-server-5.5" this will fail. The reason for this is that the filesystem on the SD card will become full. This can be seen by running the "df -h" command.

The SD card is probably 4GB. The filesystem will be around 1.9GB. This was done to make it fit on 2GB cards. To stretch the filesystem to complete 4GB you need to run "sudo raspi-config" and choose the "expand_rootfs" option. Then you need to reboot.

Now "df -h" should tell you that the filesystem has much more free space.

But your MySQL installation failed. So you run "sudo apt-get purge mysql-server-5.5" and remove the mysql-server-5.5 package and all related files.

If you would try to reinstall MySQL with "sudo apt-get install mysql-server-5.5" it would fail again. This is due to the data in /var/lib/mysql which belongs to the failed installation. You should remove that data "rm -rf /var/lib/mysql" and then install mysql again "sudo apt-get install mysql-server-5.5".

Now you should have a basic MySQL installation. You could use the "mysql_secure_installation" command to secure your installation.