How to Configure Squid with Mysql DB authentication

1. Download squid source from http://www.squid-cache.org/Versions/v3/3.0/squid-3.0.STABLE24.tar.gz
   
2. Copy the downloaded file to /tmp
   
3. Tar –zxvf squid-3.0.STABLE24.tar.gz
   
4. Cd squid-3.0.STABLE24
   
5. ./configure --enable-basic-auth-helpers=DB
6. make
7. make install
   
8. Download Current Mysql5.1 Source RPM from http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-5.1.44-1.glibc23.src.rpm/from/ftp://mirror.anl.gov/pub/mysql/
   
9. Copy MySQL-5.1.44-1.glibc23.src.rpm /tmp
   
10. Rpm –ivh MySQL-5.1.44-1.glibc23.src.rpm
    
11. Cd /usr/src/redhat/SPEC
    
12. Rpmbuid –bb mysql-5.1.44.spec
    
13. After compilation the RPM files will be in /usr/src/redhat/`uname –m`/RPMS
    
14. Cd /usr/src/redhat/`uname –m`/RPMS
    
15. Rpm –ivh MySQL-server-5.1.44-1.glibc23.ia64.rpm
    
16. The service will start automatically after installation
    

    Here we are using Default Mysql database engine MyISAM (Non transactional Engine)
    

    In a large application Database we will use Engine INNODB (Transactional engine).It has many configurable options to get mysql to work efficiently with maximum performance. The default database directory will be /var/lib/mysql.
    

    
    

17. Rpm –ivh MySQL-client-5.1.44-1.glibc23.ia64.rpm
    
18. The mysql service can be stop/start by running #service mysql stop/start
    
19. Login to Mysql as root
    
20. #mysql –u root
    
21. You must have to set a password for mysql root user
    
22. Mysql>set password=PASSWORD('your password');
    
23. Mysql>/q
    
24. Test your authentication
    
25. #mysql -u root
    

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    

26. #mysql –u root –p
    
27. Enter your password, then you will get mysql prompt.
    
28. The we have to create one Database and table for squid as follows

mysql>create database squid;

mysql>grant select on squid.* to squiduser@localhost identified by 'squid';

mysql>use squid;

mysql> CREATE TABLE `passwd` (

          `user` varchar(32) NOT NULL default '',

          `password` varchar(35) NOT NULL default '',

          `enabled` tinyint(1) NOT NULL default '1',

          `fullname` varchar(60) default NULL,

          `comment` varchar(60) default NULL,

          PRIMARY KEY  (`user`)

            );

mysql>; insert into passwd values('testuser','test',1,'Test User','for testing purpose');
mysql>\q

#/usr/local/squid/libexec/squid_db_auth –-user  squiduser -–password squid -–plaintext –-persist

testuser test

OK

1. Enter Test user name and password ,separated with space and press enter ,if it shown OK ,your authentication will work .If you got "ERR unknown login", you missed something
   
2. Now your database is ready and we are going to configure squid
   
3. Go to /usr/local/squid/etc/
   
4. Cp squid.conf squid.conf.org
   
5. Search for auth_param and add these lines
   

   auth_param basic program /usr/local/squid/libexec/squid_db_auth --user squiduser --password squid --plaintext --persist
   

   auth_param basic children 5
   

   auth_param basic realm Web-Proxy
   

   auth_param basic credentialsttl 1 minute
   

   auth_param basic casesensitive off
   

1. Now you have to create an ACL and Rule to authenticate
   
2. Here we will use two separate user ACL groups
   

A) Master users with all allowed sites as MSTUSR

B) Normal users with minimum access NRMUSR

1. Two ACL for IP based authentication
   

   a) Allowed IPs as ALLOWIP
   

   b) Denied IPs DENYIP
   

2. Two ACL for Allowed and Denied sites
   

   a)Allowed sites as ALLOWSITE
   

   b)Denied sites as BLOCKSITES
   

3. The ACLs in squid.conf as follows
   

acl localnet src 10.0.0.0/8 #Your

acl localnet src 172.16.0.0/12 #Possible

acl localnet src 192.168.0.0/16 #Internal Network

acl ALLOWIP src "/usr/ansil/proxy/policy/allowip"

acl DENYIP src "/usr/ansil/proxy/policy/denyip"

acl ACLAUTH proxy_auth REQUIRED

acl MSTUSR proxy_auth "/usr/ansil/proxy/policy/mstusr"

acl ALLOWSITE url_regex "/usr/ansil/proxy/policy/allowsite"

acl NRMUSR proxy_auth "/usr/ansil/proxy/policy/normaluser"

acl BLOCKSITES url_regex -i "/usr/ansil/proxy/policy/blocksites"

1. Now we have to assign rules for according to our ACLs
   

http_access deny BLOCKSITES

http_access allow ACLAUTH MSTUSR

http_access allow ALLOWIP

http_access deny DENYIP

http_access allow ACLAUTH ALLOWSITE ICMSUSER

1. Now save the configuration file.
   
2. Restart squid service ,for that you need to create one script for squid
   
3. Create a script as follows and put it on /etc/ini.d/ with name as squid
   
4. #chmod 755 /etc/init.d/squid
5. chkconfig --add squid
   

#!/bin/bash

PATH=/usr/bin:/sbin:/bin:/usr/sbin

export PATH

. /etc/rc.d/init.d/functions

. /etc/sysconfig/network

if [ -f /etc/sysconfig/squid ]; then

. /etc/sysconfig/squid

fi

SQUID_OPTS=${SQUID_OPTS:-"-D"}

SQUID_PIDFILE_TIMEOUT=${SQUID_PIDFILE_TIMEOUT:-20}

SQUID_SHUTDOWN_TIMEOUT=${SQUID_SHUTDOWN_TIMEOUT:-100}

[ -f /usr/sbin/squid ] && SQUID=squid

if [ "$1" == "status" ]; then

[ -z "$SQUID" ] && exit 4

else

[ -z "$SQUID" ] && exit 1

fi

prog="$SQUID"

CACHE_SWAP=`sed -e 's/#.*//g' /usr/local/squid/etc/squid.conf | \

grep cache_dir | awk '{ print $3 }'`

[ -z "$CACHE_SWAP" ] && CACHE_SWAP=/var/spool/squid

RETVAL=0

probe() {

# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 1

# check if the squid conf file is present

[ -f /usr/local/squid/etc/squid.conf ] || exit 6

}

start() {

probe

$SQUID -k parse

RETVAL=$?

if [ $RETVAL -ne 0 ]; then

echo -n $"Starting $prog: "

echo_failure

echo

return 1

fi

for adir in $CACHE_SWAP; do

if [ ! -d $adir/00 ]; then

echo -n "init_cache_dir $adir... "

$SQUID -z -F -D >> /var/log/squid/squid.out 2>&1

fi

done

echo -n $"Starting $prog: "

$SQUID $SQUID_OPTS >> /var/log/squid/squid.out 2>&1

RETVAL=$?

if [ $RETVAL -eq 0 ]; then

timeout=0;

while : ; do

[ ! -f /var/run/squid.pid ] || break

if [ $timeout -ge $SQUID_PIDFILE_TIMEOUT ]; then

RETVAL=1

break

fi

sleep 1 && echo -n "."

timeout=$((timeout+1))

done

fi

[ $RETVAL -eq 0 ] && touch /var/lock/subsys/$SQUID

[ $RETVAL -eq 0 ] && echo_success

[ $RETVAL -ne 0 ] && echo_failure

echo

return $RETVAL

}

stop() {

echo -n $"Stopping $prog: "

$SQUID -k check >> /var/log/squid/squid.out 2>&1

RETVAL=$?

if [ $RETVAL -eq 0 ] ; then

$SQUID -k shutdown &

rm -f /var/lock/subsys/$SQUID

timeout=0

while : ; do

[ -f /var/run/squid.pid ] || break

if [ $timeout -ge $SQUID_SHUTDOWN_TIMEOUT ]; then

echo

return 1

fi

sleep 2 && echo -n "."

timeout=$((timeout+2))

done

echo_success

echo

else

echo_failure

echo

fi

return $RETVAL

}

reload() {

$SQUID $SQUID_OPTS -k reconfigure

}

restart() {

stop

start

}

condrestart() {

[ -e /var/lock/subsys/squid ] && restart || :

}

rhstatus() {

status $SQUID && $SQUID -k check

}

case "$1" in

start)

start

;;

stop)

stop

;;

reload)

reload

;;

restart)

restart

;;

condrestart)

condrestart

;;

status)

rhstatus

;;

probe)

probe

return 0

;;

*)

echo $"Usage: $0 {start|stop|status|reload|restart|condrestart|probe}"

exit 2

esac

exit $