Squid with MySQL – authentication

Squid is the widly used proxy server. We are using squid as proxy cahcing server with authnetication with PAM.. Now we have replace this squid with PAM authentication with squid with MySQL.

Installation and configuration -

* Download squid_mysql plugin from here…

1] go to directory where you have extracted squid_mysql plugin..

root@indiangnu.org:/home/arun# cd mysql_auth-0.8
root@indiangnu.org:/home/arun/mysql_auth-0.8#

2] Please change the src/mysql_auth.conf as shown below then run make command

root@indiangnu.org:/home/arun/mysql_auth-0.8# make
gcc -I/usr/include -L/usr/lib -c -o src/mysql_auth.o src/mysql_auth.c
gcc -I/usr/include -L/usr/lib -c -o src/confparser.o src/confparser.c
gcc -I/usr/include -L/usr/lib -c -o src/mypasswd.o src/mypasswd.c
gcc -o mysql_auth src/mysql_auth.c src/confparser.c -lmysqlclient -I/usr/include -L/usr/lib
gcc -o mypasswd src/mypasswd.c src/confparser.c -lmysqlclient -I/usr/include -L/usr/lib
root@indiangnu.org:/home/arun/mysql_auth-0.8#

3] Make sure that squid user ie proxy and group shadow is exist.. if they are not exist then check your squid
configuration and set proper permission

root@indiangnu.org:/home/arun/mysql_auth-0.8# install -o proxy -g shadow -m 755 mysql_auth /usr/lib/squid/mysql_auth

root@indiangnu.org:/home/arun/mysql_auth-0.8# install -o root -g root -m 700 mypasswd /usr/local/bin/squid-passwd

root@indiangnu.org:/home/arun/mysql_auth-0.8# make -p /usr/local/squid/etc/
root@indiangnu.org:/home/arun/mysql_auth-0.8#

root@indiangnu.org:/home/arun/mysql_auth-0.8# install -o proxy -g root -m 600 src/mysql_auth.conf /usr/local/squid/etc/mysql_auth.conf
root@indiangnu.org:/home/arun/mysql_auth-0.8# install -o proxy -g root -m 600 src/mysql_auth.conf /usr/local/squid/etc/mysql_auth.conf.default
root@indiangnu.org:/home/arun/mysql_auth-0.8#

4] please update squid.conf file and add this line to use authentication

###########################################
#This is used for MySQL authentication
auth_param basic program /usr/lib/squid/mysql_auth
#For squid with PAM authentication
#auth_param basic program /usr/lib/squid/pam_auth -1

auth_param basic children 5
auth_param basic realm Magnet Internet Authentication
auth_param basic credentialsttl 2 hours
auth_param basic casesensitive off
###########################################

* Add ACL in squid file at proper location

acl password proxy_auth REQUIRED
acl acl_name proxy_auth “/etc/proxy/user.list”
acl allow_sites dstdom_regex -i “/etc/proxy/allow.sites”
acl block_sites dstdom_regex -i “/etc/proxy/block.sites”

* add rule for access/privleges
http_access allow allow_sites
http_access deny block_sites !acl_name

* Configuration file for Squid with MySQL authentication

root@indiangnu.org:/home/arun/mysql_auth-0.8# cat src/mysql_auth.conf
#
# mysql_auth.conf – an mysql authenticator config file
# this is the default name. you can call this by other name,
# but set up it in mysql_auth-source/src/define.h.
#
# comment: first character in line is ‘#’
# empty line (EOL at first) allowed
#
# format of parameters and their values:
# parameter – SPACE(S) and/or TAB(S) – value
#
# IMPORTANT: see the mysql_auth-source/scripts/create_script
# this configuration file made by this script
#
# by Ervin Hegedus, 2002, 2003

# hostname
#
# where is the mysql server – the server hostname or IP address;
# first ‘hostname’ directive, and after space(s) or tab(s) its
# value
#
# default:
###################################

hostname 192.168.1.50

###################################
# user
#
# which user can connect to database
# default:

###################################

user mysql_user

###################################
# password
#
# user’s password for database, that store the accounts
# default:

###################################

password mysql_passwd

###################################
# database
#
# mysql database name, where accounts places are
# default:

###################################

database mysql_auth

###################################
# mysql socket
#
# if mysqld doesn’t use INET socket, you must to set this parameter
# where is the location of mysqld socket; if mysqld use INET socket,
# put NULL value
# default:

###################################

mysqld_socket /var/run/mysqld/mysqld.sock

###################################
# next three directives tells what will the select query,
# like this:
# SELECT * FROM table WHERE user_column LIKE “username” AND password_column LIKE “password”
# where username and password comes from client in HTTP header,
# and user_column and password_column is the columns name in table
# this is an easy way to tune this program to your existing database

# table
#
# the table name, where accounts exist in user-password pair
# default:

###################################

table squid_users

###################################
# user_column
#
# user column name in table
# if you already have a database, what contains user-password
# pair, you can set it here

###################################

user_column user_name

###################################
# password_column
#
# password column name in table
# like user column name above
###################################

password_column user_passwd

###################################
# encrypt_password_form
#
# passwords are stored in encrypted form,
# using mysql internal ‘password()’ function
# this mean, you just storing the passwords encrypted format,
# Squid and clients doesn’t use encrypt form!
# The value is case insensitive (YES/yes or not one of these).
# For backward compatibility, default is NO.
#
###################################

#encrypt_password_form NO
encrypt_password_form YES

###################################

root@indiangnu.org:/home/arun/mysql_auth-0.8#

* SQL dump file for Squid with MySQL.

root@indiangnu.org:/home/arun/mysql_auth-0.8# cat scripts/create_script

DROP DATABASE IF EXISTS mysql_auth;

USE mysql;
DELETE FROM user WHERE User LIKE ‘mysql_user’;
DELETE FROM db WHERE User LIKE ‘mysql_user’;
DELETE FROM tables_priv WHERE User LIKE ‘mysql_user’;

CREATE DATABASE mysql_auth;

USE mysql_auth;

CREATE TABLE squid_users
(user_name VARCHAR(16) NOT NULL PRIMARY KEY,
user_passwd VARCHAR(64) BINARY NOT NULL);

GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_auth.* TO ‘mysql_user@’localhost’ IDENTIFIED BY ‘mysql_passwd’;
GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_auth.* TO ‘mysql_user@’192.168.1.50′ IDENTIFIED BY ‘mysql_passwd’;

root@indiangnu.org:/home/arun/mysql_auth-0.8#

* How to create DB and import this file…

* login with root/admin user of MySQL

root@indiangnu.org:/home/arun/mysql_auth-0.8# mysql -u root -p <>

Enter password:

root@indiangnu.org:/home/arun/mysql_auth-0.8#

root@indiangnu.org:/home/arun/mysql_auth-0.8# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6805 to server version: 5.0.21-Debian_3ubuntu1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;

* check output and restart squid service