Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to figure out how to make Squid proxy log to MySQL. I know ACL order is pretty important but I'm not sure if I understand exactly what ACLs are or do, it's difficult to explain, but hopefully you'll see where I'm going with this as you read!

I have created the lines to make Squid interact with a helper in squid.conf as follows:

external_acl_type mysql_log %LOGIN %SRC %PROTO %URI php /etc/squid3/custom/mysql_lg.php
acl ex_log external mysql_log
http_access allow ex_log

The external ACL helper (mysql_lg.php) is a PHP script and is as follows:

error_reporting(0);

if (! defined(STDIN)) {
    define("STDIN", fopen("php://stdin", "r"));
}

$res = mysql_connect('localhost', 'squid', 'testsquidpw');
$dbres = mysql_select_db('squid', $res);

while (!feof(STDIN)) {
    $line = trim(fgets(STDIN));
    $fields = explode(' ', $line);
    $user = rawurldecode($fields[0]);
    $cli_ip = rawurldecode($fields[1]);
    $protocol = rawurldecode($fields[2]);
    $uri = rawurldecode($fields[3]);

    $q = "INSERT INTO logs (id, user, cli_ip, protocol, url) VALUES ('', '".$user."', '".$cli_ip."', '".$protocol."', '".$uri."');";
    mysql_query($q) or die (mysql_error());

    if ($fault) {
            fwrite(STDOUT, "ERR\n");
    };

    fwrite(STDOUT, "OK\n");
}

The configuration I have right now looks like this:

## Authentication Handler
auth_param ntlm program /usr/bin/ntlm_auth --helper-protocol=squid-2.5-ntlmssp
auth_param ntlm children 30

auth_param negotiate program /usr/bin/ntlm_auth --helper-protocol=squid-2.5-basic
auth_param negotiate children 5

# Allow squid to update log
external_acl_type mysql_log %LOGIN %SRC %PROTO %URI php /etc/squid3/custom/mysql_lg.php
acl ex_log external mysql_log
http_access allow ex_log

acl localnet src 172.16.45.0/24
acl AuthorizedUsers proxy_auth REQUIRED

acl SSL_ports port 443
acl Safe_ports port 80          # http
acl Safe_ports port 21          # ftp
acl Safe_ports port 443         # https

acl CONNECT method CONNECT

acl blockeddomain url_regex "/etc/squid3/bl.acl"

http_access deny blockeddomain
deny_info ERR_BAD_GENERAL blockeddomain

# Deny requests to certain unsafe ports
http_access deny !Safe_ports

# Deny CONNECT to other than secure SSL ports
http_access deny CONNECT !SSL_ports

# Allow the internal network access to this proxy
http_access allow localnet

# Allow authorized users access to this proxy
http_access allow AuthorizedUsers

# FINAL RULE - Deny all other access to this proxy
http_access deny all

From testing, the closer to the bottom I place the logging lines the less it logs. Oftentimes, it even places empty rows in to the MySQL table. The file-based logs in /var/log/squid3/access.log are correct but many of the rows in the access logs are missing from the MySQL logs. I can't help but think it's down to the order I'm putting lines in because I want to log everything to MySQL, unauthenticated requests, blocked requests, which category blocked a specific request.

The reason I want this in MySQL is because I'm trying to have everything managed via a custom web-based frontend and want to avoid using any shell commands and access to system log files if I can help it. The end result is to make it as easy as possible to maintain without keeping staff waiting on the phone whilst I add a new rule and reload the server!

Hopefully someone can help me out here because this is very much a learning experience for me and I'm pretty stumped.

Many thanks in advance for any help!

share|improve this question
add comment

2 Answers

the correct php code for you is:

#!/usr/bin/php

<?php

error_reporting(0);

if (! defined(STDIN)) {
    define("STDIN", fopen("php://stdin", "r"));
}

$res = mysql_connect('127.0.0.1', 'root', 'kbpbdxgh9j');
$dbres = mysql_select_db('intranet', $res);

while (!feof(STDIN)) {
    $line = trim(fgets(STDIN));
    $fields = explode(' ', $line);
    $user = rawurldecode($fields[0]);
    $cli_ip = rawurldecode($fields[1]);
    $protocol = rawurldecode($fields[2]);
    $uri = rawurldecode($fields[3]);

    if ($user!="") {

        $q = "INSERT INTO log (usuario, ip, protocolo, url) VALUES ('".$user."', '".$cli_ip."', '".$protocol."', '".$uri."');";
        mysql_query($q) or die (mysql_error());

    if ($fault) {
                fwrite(STDOUT, "ERR\n");
        };

    }

    fwrite(STDOUT, "OK\n");
}
share|improve this answer
add comment

I would suggest following these instructions on how to enable logging of all ACL requests/allows/denies. From these you should be able to figure out which ACL rules are being applied correctly/incorrectly.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.