Squid - Autenticação e Log em banco de dados MySQL

De artista.frustrado

criar banco de dados MySQL com as seguintes tabelas:

Tabela para logs:

DROP TABLE IF EXISTS `access_log`;
CREATE TABLE IF NOT EXISTS `access_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_since_epoch` decimal(15,3) DEFAULT NULL,
  `response_time` int(11) DEFAULT NULL,
  `client_src_ip_addr` char(15) DEFAULT NULL,
  `squid_request_status` varchar(50) DEFAULT NULL,
  `http_status_code` varchar(10) DEFAULT NULL,
  `reply_size` int(11) DEFAULT NULL,
  `request_method` varchar(20) DEFAULT NULL,
  `request_url` varchar(1000) DEFAULT NULL,
  `username` varchar(40) DEFAULT NULL,
  `squid_hier_status` varchar(20) DEFAULT NULL,
  `server_ip_addr` char(15) DEFAULT NULL,
  `mime_type` varchar(50) DEFAULT NULL,
  `date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Tabela para usuários:

DROP TABLE IF EXISTS `usuario`;
CREATE TABLE IF NOT EXISTS `usuario` (
  `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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Instalar libpam-mysql

apt-get install libpam-mysql

Criar arquivo /etc/pam.d/squid com o seguinte conteúdo:

auth required pam_mysql.so user=<usuario_banco> passwd=<senha_banco> db=<nome_banco> host=localhost table=usuario  usercolumn=user passwdcolumn=password crypt=0

squid.conf

acl all src all
acl manager proto cache_object
acl localhost src 127.0.0.1/32
acl to_localhost dst 127.0.0.0/8 0.0.0.0/32
acl localnet src 10.0.0.0/8     # RFC1918 possible internal network
acl localnet src 172.16.0.0/12  # RFC1918 possible internal network
acl localnet src 192.168.0.0/16 # RFC1918 possible internal network
acl SSL_ports port 443          # https
acl SSL_ports port 563          # snews
acl SSL_ports port 873          # rsync
acl Safe_ports port 80          # http
acl Safe_ports port 21          # ftp
acl Safe_ports port 443         # https
acl Safe_ports port 70          # gopher
acl Safe_ports port 210         # wais
acl Safe_ports port 1025-65535  # unregistered ports
acl Safe_ports port 280         # http-mgmt
acl Safe_ports port 488         # gss-http
acl Safe_ports port 591         # filemaker
acl Safe_ports port 777         # multiling http
acl Safe_ports port 631         # cups
acl Safe_ports port 873         # rsync
acl Safe_ports port 901         # SWAT
acl purge method PURGE
acl CONNECT method CONNECT
http_access allow manager localhost
http_access deny manager
http_access allow purge localhost
http_access deny purge
http_access deny !Safe_ports
http_access deny CONNECT !SSL_ports
auth_param basic program /usr/lib/squid/pam_auth -o
auth_param basic children 10
auth_param basic realm Polvo - Area restrita
auth_param basic credentialsttl 2 hours
acl password-pam proxy_auth REQUIRED
http_access allow password-pam
http_access deny all
icp_access allow localnet
icp_access deny all
http_port 3128
hierarchy_stoplist cgi-bin ?
access_log /var/log/squid/access.log squid
refresh_pattern ^ftp:           1440    20%     10080
refresh_pattern ^gopher:        1440    0%      1440
refresh_pattern -i (/cgi-bin/|\?) 0     0%      0
refresh_pattern (Release|Packages(.gz)*)$       0       20%     2880
refresh_pattern .               0       20%     4320
acl shoutcast rep_header X-HTTP09-First-Line ^ICY.[0-9]
upgrade_http0.9 deny shoutcast
acl apache rep_header Server ^Apache
broken_vary_encoding allow apache
extension_methods REPORT MERGE MKACTIVITY CHECKOUT
hosts_file /etc/hosts
coredump_dir /var/spool/squid
logformat squid_mysql  %ts.%03tu %6tr %>a %Ss %03Hs %<st %rm %ru %un %Sh %<A %mt
access_log daemon:/127.0.0.1/squid_log/access_log/root/h2o0n0e5y squid_mysql
logfile_daemon /etc/squid3/docs/Squid-mysql-log_trunk/logfile-daemon_mysql.pl