[squid-users] squid authentication to remote sql server
snakeeyes
ahmed.zaeem at netstream.ps
Fri Feb 13 11:50:03 UTC 2015
Hi Amos , I had changed the table name but still no luck :
I connected to mysql server from remote other sql tool and it worked , I used squid/squid user/pwd in the tool and no problem
So im sure the issue is in squid side ,
I did as u told me , I changed the password table to ==>squid table
here is info on mysql server :
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| squid |
+--------------------+
3 rows in set (0.00 sec)
mysql> use squid;
Database changed
mysql> show tables
-> ;
+-----------------+
| Tables_in_squid |
+-----------------+
| squid |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from squid ;
+--------+----------+---------+-----------+---------------------+
| user | password | enabled | fullname | comment |
+--------+----------+---------+-----------+---------------------+
| Nikesh | test | 1 | Test User | for testing purpose |
+--------+----------+---------+-----------+---------------------+
1 row in set (0.00 sec)
mysql> show GRANTS FOR squid;
+------------------------------------------------------------------------------------------------------+
| Grants for squid@% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'squid'@'%' IDENTIFIED BY PASSWORD '*AFD42D37182BDB40880BEF624CC64B0F4A1E35B4' |
| GRANT SELECT ON `squid`.* TO 'squid'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant select on squid.* to "squid"@"%" identified by 'squid';
Query OK, 0 rows affected (0.00 sec)
==========================
here is tesing from the remote squid machine using the helper :
/lib/squid/basic_db_auth --dsn "DBI:mysql:database=squid:xx189.177" --user "squid" --password "squid" --table "squid" --usercol "user" --passwdcol "password"
ERR unknown login
ERR unknown login
ERR unknown login
Here is tesing with only same parameters :
/lib/squid/basic_db_auth --dsn "DBI:mysql:database=squid:xx189.177" --user "squid" --password "squid"
DBD::mysql::st execute failed: Table 'squid.passwd' doesn't exist at /lib/squid/basic_db_auth line 215, <> line 1.
DBD::mysql::st execute failed: MySQL server has gone away at /lib/squid/basic_db_auth line 218, <> line 1.
ERR database error
DBD::mysql::st execute failed: Table 'squid.passwd' doesn't exist at /lib/squid/basic_db_auth line 215, <> line 2.
DBD::mysql::st execute failed: MySQL server has gone away at /lib/squid/basic_db_auth line 218, <> line 2.
ERR database error
======================
So , as u told me , I will tun squid now with helper and see the debug mode :
Here is my squid.conf :
auth_param basic program /lib/squid/basic_db_auth --dsn "DBI:mysql:database=squid:xx.189.177" --user "squid" --password "squid" --table "squid" --usercol "user" --passwdcol "password"
auth_param basic children 5
auth_param basic realm Web-Proxy
auth_param basic credentialsttl 1 minute
auth_param basic casesensitive off
acl db-auth proxy_auth REQUIRED
http_access allow db-auth
here is squid in debug mode :
2015/02/12 22:46:13.403 kid1| client_side.cc(1546) keepaliveNextRequest: ConnnStateData(local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1), Context(local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1)
2015/02/12 22:46:13.403 kid1| clientStream.cc(202) clientStreamDetach: clientStreamDetach: Detaching node 0x2834678
2015/02/12 22:46:13.403 kid1| clientStream.cc(287) clientStreamFree: Freeing clientStreamNode 0x2834678
2015/02/12 22:46:13.403 kid1| client_side_request.cc(246) ~ClientHttpRequest: httpRequestFree: http://yahoo.com/
2015/02/12 22:46:13.403 kid1| SBuf.cc(139) assign: assigning SBuf479 from SBuf480
2015/02/12 22:46:13.403 kid1| Checklist.cc(68) preCheck: 0x7fff89df6c70 checking fast ACLs
2015/02/12 22:46:13.403 kid1| Acl.cc(138) matches: checking access_log daemon:/var/log/squid/access.log
2015/02/12 22:46:13.403 kid1| Acl.cc(138) matches: checking (access_log daemon:/var/log/squid/access.log line)
2015/02/12 22:46:13.404 kid1| Acl.cc(158) matches: checked: (access_log daemon:/var/log/squid/access.log line) = 1
2015/02/12 22:46:13.404 kid1| Acl.cc(158) matches: checked: access_log daemon:/var/log/squid/access.log = 1
2015/02/12 22:46:13.404 kid1| Checklist.cc(61) markFinished: 0x7fff89df6c70 answer ALLOWED for match
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(65) logfileNewBuffer: logfileNewBuffer: daemon:/var/log/squid/access.log: new buffer
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(172) logfile_mod_daemon_append: logfile_mod_daemon_append: daemon:/var/log/squid/access.log: appending 1 bytes
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(176) logfile_mod_daemon_append: logfile_mod_daemon_append: current buffer has 0 of 32768 bytes before append
2015/02/12 22:46:13.404 kid1| SBuf.cc(139) assign: assigning SBuf477 from SBuf2
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(172) logfile_mod_daemon_append: logfile_mod_daemon_append: daemon:/var/log/squid/access.log: appending 105 bytes
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(176) logfile_mod_daemon_append: logfile_mod_daemon_append: current buffer has 1 of 32768 bytes before append
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(172) logfile_mod_daemon_append: logfile_mod_daemon_append: daemon:/var/log/squid/access.log: appending 2 bytes
2015/02/12 22:46:13.404 kid1| ModDaemon.cc(176) logfile_mod_daemon_append: logfile_mod_daemon_append: current buffer has 106 of 32768 bytes before append
2015/02/12 22:46:13.404 kid1| ModEpoll.cc(116) SetSelect: FD 9, type=2, handler=1, client_data=0x282a1e8, timeout=0
2015/02/12 22:46:13.404 kid1| FilledChecklist.cc(66) ~ACLFilledChecklist: ACLFilledChecklist destroyed 0x7fff89df6c70
2015/02/12 22:46:13.404 kid1| Checklist.cc(195) ~ACLChecklist: ACLChecklist::~ACLChecklist: destroyed 0x7fff89df6c70
2015/02/12 22:46:13.404 kid1| store.cc(523) unlock: ClientHttpRequest::loggingEntry unlocking key 9025F4F15092391E81517BA23C94F29E e:=sXINV/0x28345e0*2
2015/02/12 22:46:13.405 kid1| clientStream.cc(247) clientStreamAbort: clientStreamAbort: Aborting stream with tail 0x2c4fd88
2015/02/12 22:46:13.405 kid1| clientStream.cc(202) clientStreamDetach: clientStreamDetach: Detaching node 0x2c4fd88
2015/02/12 22:46:13.405 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall Initiate::noteInitiatorAborted constructed, this=0x2c5e480 [call149]
2015/02/12 22:46:13.405 kid1| AsyncCall.cc(93) ScheduleCall: Initiator.cc(40) will call Initiate::noteInitiatorAborted() [call149]
2015/02/12 22:46:13.405 kid1| client_side_request.cc(115) ~ClientRequestContext: 0x2c52bf8 ClientRequestContext destructed
2015/02/12 22:46:13.405 kid1| UserRequest.cc(101) ~UserRequest: freeing request 0x2c535c0
2015/02/12 22:46:13.405 kid1| HttpHeader.cc(479) clean: cleaning hdr: 0x2bf5b98 owner: 2
2015/02/12 22:46:13.405 kid1| HttpRequest.cc(55) ~HttpRequest: destructed, this=0x2bf5b80
2015/02/12 22:46:13.405 kid1| HttpHeader.cc(479) clean: cleaning hdr: 0x2bf5b98 owner: 2
2015/02/12 22:46:13.405 kid1| AsyncJob.cc(40) ~AsyncJob: AsyncJob destructed, this=0x2834860 type=ClientHttpRequest [job4]
2015/02/12 22:46:13.405 kid1| clientStream.cc(223) clientStreamDetach: clientStreamDetach: Calling 1 with cbdata 0x2c523a0
2015/02/12 22:46:13.405 kid1| clientStream.cc(287) clientStreamFree: Freeing clientStreamNode 0x2c4fd88
2015/02/12 22:46:13.405 kid1| store_client.cc(663) storeUnregister: storeUnregister: called for '9025F4F15092391E81517BA23C94F29E'
2015/02/12 22:46:13.405 kid1| store.cc(955) checkCachable: StoreEntry::checkCachable: NO: not cachable
2015/02/12 22:46:13.405 kid1| store_dir.cc(821) memoryOut: keepInLocalMemory: 0
2015/02/12 22:46:13.405 kid1| store.cc(1967) trimMemory: e:=sXINV/0x28345e0*1 inmem_lo=4047
2015/02/12 22:46:13.406 kid1| store.cc(485) lock: storeUnregister locked key 9025F4F15092391E81517BA23C94F29E e:=sXINV/0x28345e0*2
2015/02/12 22:46:13.406 kid1| store_client.cc(757) storePendingNClients: storePendingNClients: returning 0
2015/02/12 22:46:13.406 kid1| store.cc(523) unlock: storeUnregister unlocking key 9025F4F15092391E81517BA23C94F29E e:=sXINV/0x28345e0*2
2015/02/12 22:46:13.406 kid1| store.cc(523) unlock: clientReplyContext::removeStoreReference unlocking key 9025F4F15092391E81517BA23C94F29E e:=sXINV/0x28345e0*1
2015/02/12 22:46:13.406 kid1| store_client.cc(757) storePendingNClients: storePendingNClients: returning 0
2015/02/12 22:46:13.406 kid1| store.cc(1239) release: releasing e:=sXINV/0x28345e0*0 9025F4F15092391E81517BA23C94F29E
2015/02/12 22:46:13.406 kid1| store.cc(404) destroyMemObject: destroyMemObject 0x2c53d20
2015/02/12 22:46:13.406 kid1| MemObject.cc(110) ~MemObject: del MemObject 0x2c53d20
2015/02/12 22:46:13.406 kid1| ctx: enter level 0: 'http://yahoo.com/'
2015/02/12 22:46:13.406 kid1| HttpHeader.cc(479) clean: cleaning hdr: 0x2c53ff8 owner: 3
2015/02/12 22:46:13.406 kid1| HttpHeader.cc(479) clean: cleaning hdr: 0x2c53ff8 owner: 3
2015/02/12 22:46:13.406 kid1| ctx: exit level 0
2015/02/12 22:46:13.406 kid1| store.cc(422) destroyStoreEntry: destroyStoreEntry: destroying 0x28345e8
2015/02/12 22:46:13.406 kid1| store.cc(404) destroyMemObject: destroyMemObject 0
2015/02/12 22:46:13.406 kid1| store.cc(377) ~StoreEntry: StoreEntry destructed, this=0x28345e0
2015/02/12 22:46:13.406 kid1| HttpHeader.cc(479) clean: cleaning hdr: 0x2bf4e08 owner: 3
2015/02/12 22:46:13.406 kid1| HttpHeader.cc(479) clean: cleaning hdr: 0x2bf4e08 owner: 3
2015/02/12 22:46:13.406 kid1| client_side.cc(3167) clientParseRequests: local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1: attempting to parse
2015/02/12 22:46:13.406 kid1| client_side.cc(1616) keepaliveNextRequest: local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1: calling conn->readNextRequest()
2015/02/12 22:46:13.406 kid1| client_side.cc(1503) readNextRequest: local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1 reading next req
2015/02/12 22:46:13.407 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall ConnStateData::requestTimeout constructed, this=0x2c5e9e0 [call150]
2015/02/12 22:46:13.407 kid1| comm.cc(553) commSetConnTimeout: local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1 timeout 120
2015/02/12 22:46:13.407 kid1| AsyncCallQueue.cc(57) fireNext: leaving clientWriteComplete(local=192.168.1.8:3128 remote=192.168.1.6:52221 FD 10 flags=1, data=0x2c50028)
2015/02/12 22:46:13.407 kid1| AsyncCallQueue.cc(55) fireNext: entering Initiate::noteInitiatorAborted()
2015/02/12 22:46:13.407 kid1| AsyncCall.cc(38) make: make call Initiate::noteInitiatorAborted [call149]
2015/02/12 22:46:13.407 kid1| AsyncCall.cc(56) cancel: will not call Initiate::noteInitiatorAborted [call149] because job gone
2015/02/12 22:46:13.407 kid1| AsyncCall.cc(48) make: will not call Initiate::noteInitiatorAborted [call149] because of job gone
2015/02/12 22:46:13.407 kid1| AsyncCallQueue.cc(57) fireNext: leaving Initiate::noteInitiatorAborted()
2015/02/12 22:46:13.407 kid1| ModEpoll.cc(116) SetSelect: FD 10, type=2, handler=0, client_data=0, timeout=0
2015/02/12 22:46:13.407 kid1| ModDaemon.cc(108) logfileHandleWrite: daemon:/var/log/squid/access.log: write returned 108
2015/02/12 22:46:13.407 kid1| ModEpoll.cc(116) SetSelect: FD 9, type=2, handler=0, client_data=0, timeout=0
2015/02/12 22:46:14.295 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall logfileFlush constructed, this=0x2834ec0 [call151]
2015/02/12 22:46:14.295 kid1| AsyncCall.cc(93) ScheduleCall: event.cc(237) will call logfileFlush(0x282a1e8*?) [call151]
2015/02/12 22:46:14.295 kid1| AsyncCallQueue.cc(55) fireNext: entering logfileFlush(0x282a1e8*?)
2015/02/12 22:46:14.295 kid1| AsyncCall.cc(38) make: make call logfileFlush [call151]
2015/02/12 22:46:14.296 kid1| event.cc(322) schedule: schedule: Adding 'logfileFlush', in 1.00 seconds
2015/02/12 22:46:14.296 kid1| AsyncCallQueue.cc(57) fireNext: leaving logfileFlush(0x282a1e8*?)
2015/02/12 22:46:14.296 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall MaintainSwapSpace constructed, this=0x2834ec0 [call152]
2015/02/12 22:46:14.296 kid1| AsyncCall.cc(93) ScheduleCall: event.cc(237) will call MaintainSwapSpace() [call152]
2015/02/12 22:46:14.296 kid1| AsyncCallQueue.cc(55) fireNext: entering MaintainSwapSpace()
2015/02/12 22:46:14.296 kid1| AsyncCall.cc(38) make: make call MaintainSwapSpace [call152]
2015/02/12 22:46:14.296 kid1| event.cc(322) schedule: schedule: Adding 'MaintainSwapSpace', in 1.00 seconds
2015/02/12 22:46:14.296 kid1| AsyncCallQueue.cc(57) fireNext: leaving MaintainSwapSpace()
2015/02/12 22:46:15.298 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall logfileFlush constructed, this=0x2834ec0 [call153]
2015/02/12 22:46:15.298 kid1| AsyncCall.cc(93) ScheduleCall: event.cc(237) will call logfileFlush(0x282a1e8*?) [call153]
2015/02/12 22:46:15.298 kid1| AsyncCallQueue.cc(55) fireNext: entering logfileFlush(0x282a1e8*?)
2015/02/12 22:46:15.298 kid1| AsyncCall.cc(38) make: make call logfileFlush [call153]
2015/02/12 22:46:15.298 kid1| event.cc(322) schedule: schedule: Adding 'logfileFlush', in 1.00 seconds
2015/02/12 22:46:15.298 kid1| AsyncCallQueue.cc(57) fireNext: leaving logfileFlush(0x282a1e8*?)
2015/02/12 22:46:15.298 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall MaintainSwapSpace constructed, this=0x2834ec0 [call154]
2015/02/12 22:46:15.298 kid1| AsyncCall.cc(93) ScheduleCall: event.cc(237) will call MaintainSwapSpace() [call154]
2015/02/12 22:46:15.298 kid1| AsyncCallQueue.cc(55) fireNext: entering MaintainSwapSpace()
2015/02/12 22:46:15.299 kid1| AsyncCall.cc(38) make: make call MaintainSwapSpace [call154]
2015/02/12 22:46:15.299 kid1| event.cc(322) schedule: schedule: Adding 'MaintainSwapSpace', in 1.00 seconds
2015/02/12 22:46:15.299 kid1| AsyncCallQueue.cc(57) fireNext: leaving MaintainSwapSpace()
2015/02/12 22:46:16.301 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall logfileFlush constructed, this=0x2834ec0 [call155]
2015/02/12 22:46:16.301 kid1| AsyncCall.cc(93) ScheduleCall: event.cc(237) will call logfileFlush(0x282a1e8*?) [call155]
2015/02/12 22:46:16.301 kid1| AsyncCallQueue.cc(55) fireNext: entering logfileFlush(0x282a1e8*?)
2015/02/12 22:46:16.301 kid1| AsyncCall.cc(38) make: make call logfileFlush [call155]
2015/02/12 22:46:16.301 kid1| event.cc(322) schedule: schedule: Adding 'logfileFlush', in 1.00 seconds
2015/02/12 22:46:16.301 kid1| AsyncCallQueue.cc(57) fireNext: leaving logfileFlush(0x282a1e8*?)
2015/02/12 22:46:16.301 kid1| AsyncCall.cc(26) AsyncCall: The AsyncCall MaintainSwapSpace constructed, this=0x2834ec0 [call156]
2015/02/12 22:46:16.301 kid1| AsyncCall.cc(93) ScheduleCall: event.cc(237) will call MaintainSwapSpace() [call156]
2015/02/12 22:46:16.301 kid1| AsyncCallQueue.cc(55) fireNext: entering MaintainSwapSpace()
2015/02/12 22:46:16.301 kid1| AsyncCall.cc(38) make: make call MaintainSwapSpace [call156]
2015/02/12 22:46:16.302 kid1| event.cc(322) schedule: schedule: Adding 'MaintainSwapSpace', in 1.00 seconds
2015/02/12 22:46:16.302 kid1| AsyncCallQueue.cc(57) fireNext: leaving MaintainSwapSpace()
Agia I put the user/pwd in y browser with ni luck , each time it refuse my connection .
Here is access.log :
1423799039.114 1072 192.168.1.6 TCP_DENIED/407 4197 CONNECT developer.mozilla.org:443 nikesh HIER_NONE/- text/html
1423799152.251 117 192.168.1.6 TCP_DENIED/407 4097 GET http://yahoo.com/ - HIER_NONE/- text/html
1423799173.404 1143 192.168.1.6 TCP_DENIED/407 4200 GET http://yahoo.com/ nikesh HIER_NONE/- text/html
1423799270.459 1556 192.168.1.6 TCP_DENIED/407 4194 GET http://yahoo.com/ nikesh HIER_NONE/- text/html
Any help ??
Should shoud I try more ?
-----Original Message-----
From: Amos Jeffries [mailto:squid3 at treenet.co.nz]
Sent: Tuesday, February 10, 2015 1:58 PM
To: snakeeyes; squid-users at lists.squid-cache.org
Subject: Re: [squid-users] squid authentication to remote sql server
On 11/02/2015 8:40 p.m., snakeeyes wrote:
> Hi amos
> I hadded squi/squid in the table
>
> mysql> show tables
> -> ;
> +-----------------+
> | Tables_in_squid |
> +-----------------+
> | passwd |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql> select * from passwd;
> +--------+----------+---------+-----------+---------------------+
> | user | password | enabled | fullname | comment |
> +--------+----------+---------+-----------+---------------------+
> | Nikesh | test | 1 | Test User | for testing purpose |
> | squid | squid | 1 | Test User | for testing purpose |
> +--------+----------+---------+-----------+---------------------+
> 2 rows in set (0.00 sec)
>
> mysql>
>
>
>
> still has same error ?
>
>
>
> how can u help me ?
> login with md5 or wt ??
>
Try renaming the "password" column.
- rename it in the DB, change the squid.conf helper parameter, then reload squid config.
Perhapse show us what you are entering on the command line test for username and password?
Perhapse try using the --debug option? to get the helper to record in cache log (or on screen for the manul tests).
Amos
More information about the squid-users
mailing list