mysql_auth v0.8

Happy Birth'day, dear my son! :)


Squid HTTP proxy MySQL authentication module
Ervin Hegedus
Wed Dec 13 23:00:00 CET 2004

Contents

Introduction

This is an authentication module for the Squid proxy server to authenticate users on an mysql database. The database is a single table, and the table is a very simple user-password pair, with plain or encrypted passwords.
There is a utility, called by mypasswd. With this you can add a new user to your pasword database, update it or delete from. It uses the configuration file, what mysql_auth.

Usage is simple. It accepts a username and password on standard input. It will return OK if the username/password is valid, or ERR if there was some problem. Check syslog messages for reported problems.

mypasswd is also a very simple program: simply type mypasswd with no arguments, and it gives you help about itself. Note, these are the examples about it: 

shell> mypasswd user password
add a new user if it doesn't exist, or update its password. This form of mypasswd also makes it: 
shell> mypasswd user
Enter NEW password:
Re-enter NEW password:
Password record ADDED succesfully.
And you can delete a user from database, like this: 
shell> mypasswd -d user
mysql_auth is released under the GNU General Public License and is available from http://people.arxnet.hu/airween/mysql_auth/.

Mysql_auth tested with MySQL 3.23, 4.0.XX, on Linux and FreeBSD os's.

Installation

Make any changes to the source code you need. For example, set up correctly place of configuration file. See that in define.h.

Review the Makefile, and modify based on target platform or site requirements. Setting up the mysql.h header and libmysqlclients.a places. Setting up your squid user and group accounts, and squid root directory.
Setting up correctly the permissions of mysql_auth.conf (mode 600)!
This is very important! This file included a mysql account, with select, update, insert and delete privileges!
Also very important: if you use mysql.log, be very carefull! It contains all transactions! For example: 

	52 Query       UPDATE data SET password = password ("1234") WHERE user LIKE 'airween'

After when you edit what you want, type 'make', then 'make install', then 'make clean'.

'Make install' will put 'mysql_auth' into /usr/local/squid/libexec default, and 'mysql_auth.conf' to /usr/local/squid/etc.

Create database

For store user and password pairs, you need a database, like mysql. It is very simple, one-table database. For create it, you need to run create_script from path/to/your/source/scripts directory.

You can create it like this: 

shell> cd path/to/mysql_auth-source/scripts
shell> mysql -u your_user_name -p < create_script
Enter password: <type your password>
To add a user: 
shell> mysql -u your_user_name -p mysql_auth
Enter password:
Welcome message...

mysql> insert into data (user, password) values ('joeav', 'joespass');
Query OK, 1 row affected (0.00 sec)

mysql>
or if you want to store your passwords in encrypted format: 
shell> mysql -u your_user_name -p mysql_auth
Enter password:
Welcome message...

mysql> insert into data (user, password) values ('joeav', password("joespass"));
Query OK, 1 row affected (0.00 sec)

mysql>
Or try mypasswd: 
shell> mypasswd joeav joespass
That's it!

Note: if you set up in mysql_auth.conf the encrypt_password_form to 'yes', mypasswd also uses encrypt form.

Hopefully nobody has problems compiling mysql_auth, and create_script.

Issues when compiling

The Makefile uses the GCC compiler, and assumes that it is in the current PATH. Mysql_auth is known to compile properly on Debian Linux 3.1 without problems. Other operating systems are untested, but use a recent copy of the GNU C compiler.

Problem occur, when you can't setting up mysql header and/or library correctly.
Other, if you use MySQL 4.0 or above, may be you need to use in CFLAGS -lz at last.

Configuration file

Mysql_auth uses a configuration file. It reads just every startup, not every query. The file is /usr/local/squid/etc/mysql_auth.conf. If this path needs to be changed, see define.h. 

  #define CONFIG_FILE   "/usr/local/squid/etc/mysql_auth.conf"

An example configuration file is provided. It looks like 

hostname	localhost
# FQDN hostname or IP address.
# if you use it on localhost, and don't use tcp-network to
# connect it (use a UNIX socket), you have to set up the
# mysqld_socket parameter - see below

user		squid
# mysql user name - mysql administrator sets up.

password	squid
# password of mysql user - mysql administrator sets up.

database	mysql_auth
# mysql database on mysql server - a unique name.

mysqld_socket	/tmp/mysqld.sock
# absolute path of mysql daemon socket
# need it, if you use mysqld through UNIX socket, not INET socket
# see skip-networking option in my.cnf, the main configuration file
# of mysql

table		data
# table name in mysql database - contents two couloms, user and password.
# The password store in plain or encrypt format, and of course, case sensitive.

user_column     user
# column name where username exists.

password_column password
# password name where passwords exists.

encrypt_password_form	no
# if you want to store your passwords in encrypted form
# this is case insensitive; 'YES' and 'yes' is same

These values are defaults, when you don't set other, mysql_auth wants to work with these.

All comments start with '#'. Empty line allowed.

When a user provides a username/password, mysql_auth querys from mysql server. It stops after a user has been successfully authenticated. Make sure the server can be reached and is active, or else mysql_auth will return all with errors!

Squid.conf changes

Refer to Squid documentation for the required changes to squid.conf. You will need to set the following lines to enable authentication for your access list - 

  acl <yourACL> proxy_auth REQUIRED
  http_access allow password
  http_access allow <yourACL>
  http_access deny all

You will also need to review the following directives. The number of mysql_auth children spawned is set with authenticate_children. The number of children needed is site-dependent, so some experimentation may be required to find the best number. 

  auth_param basic realm Squid proxy server
  auth_param basic program /usr/local/squid/libexec/mysql_auth
  auth_param basic credentialsttl 5
  auth_param basic children 5

Testing

I strongly urge that mysql_auth is tested prior to being used in a production environment. It may behave differently on different platforms. To test it, run it from the command line. Enter username and password pairs separated by a space.

It should behave in the following way - 

 - Press ENTER to get an OK or ERR message.
 - Make sure pressing CTRL-C aborts the program.
 - Test that entering no details does not result in an OK or ERR message.
 - Test that entering an invalid username and password results in
   an ERR message.
 - Test that entering an valid username and password results in an OK message.

Usernames cannot have whitespace in them, passwords can.

If you have any problem until you test it, see your syslog for details.

Contact details

To contact the maintainer of this package, email Ervin Hegedus at airween a.t arxnet d.o.t hu.

Also I have a nice mailing list: https://lists.fsn.hu/mailman/listinfo/mysql-auth

Good Luck! :)