[squid-users] squid and mysql to autenticate groups

[Amos Jeffries]

On 15/07/2016 5:14 a.m., Ottavia Neruda wrote:
> hello,
> I'have 2 tables in mysql.
> First table is Group1 and other is Group2.
> I'd like that user in Group1 are enabled to surfing in Sites1 list of 
> sites and Group2 in Sites2 list of sites.

<snip/paste>
>
> Why it does'nt work?

Because authorization (auth-z) is different from authentication
(auth-n). Simply naming an ACL a "group" check, does not make it one.

* Auth-n is a simple check to verify that _the client is who it claims
to be_.
 It is all about identity. Not access.

* Auth-z is a check to see whether the user account the client claims to
be is _allowed to access_ the requested URL resource.
 Its all about access, not identity.

. N
> In squid.conf I did:
> 
> auth_param basic program  /usr/lib/squid/basic_db_auth --dsn 
> "DBI:mysql:database=squid" --user "utente_lettore" --password "password" 
> --table "Group1" --usercol "user" --passwdcol "passwor$

Tells Squid how to perform auth-n using the Basic authentication
protocol and a MySQL database.
The user accounts are stored in a tables called "Group1".

> 
> auth_param basic children 15
> auth_param basic realm proxy2
> auth_param basic credentialsttl 1 minute
> auth_param basic casesensitive off
> 
> acl db-Group1 proxy_auth REQUIRED
> 
> auth_param basic program  /usr/lib/squid/basic_db_auth --dsn 
> "DBI:mysql:database=squid" --user "utente_lettore" --password "password" 
> --table "Group2" --usercol "user" --passwdcol "passwor$
> 

Tells Squid how to perform auth-n using the Basic authentication
protocol and a MySQL database.
The user accounts are stored in a tables called "Group2".

This *replaces* the previous Basic authentication configuration.

Only users in the "Group2" table can be authenticated (auth-n), all
others are un-authenticated.


> auth_param basic children 15
> auth_param basic realm proxy2
> auth_param basic credentialsttl 1 minute
> auth_param basic casesensitive off

So do these settings, but they replace previous config with the same
values. So no noticable harm from that. Just a waste of space in squid.conf.

> 
> acl db-Group2 proxy_auth REQUIRED
> 

You now have two ACLs called db-Group1 and db-Group2 - for which the way
to authenticate is offering Basic authentication to the client, and
looking the credentials it replies with up in the "Group2" table in your
MySQL database.
 These ACLs both do the exact same thing so are redundant.

> 
> acl Sites1 dstdomain "/etc/squid/webconsentiti.txt"
> acl Sites2 dstdomain "/etc/squid/webconsentiti2.txt"
> 
> 
> http_access allow db-Group1 Sites1
> http_access allow db-Group2 Sites2

Authenticated (auth-n) users are allowed to access domains listed in
Sites1 or in Sites2.

>  
> http_access deny all

All other traffic is denied.



What you need to do is to have a table of users, where their username
and password can be verified (auth-n / authenticted). The basic_db_auth
helper looks there to do the authentication.

NP: I recommend against having a column called 'password'. That can
cause trouble with the MSQL built-in function called password in some
queries. It's caused me some headaches in the past.

And a second table listing the groups each user belongs to. And an
external_acl_type helper that looks up that table and tells Squid if a
user is in group1 or group2. You can copy and update the basic_db_auth
script to do external_acl_type checking intead of authentication. I've
called the example one below /etc/squid/db_group, it receives "username
groupname" from Squid.


 # how to authenticate
 auth_param basic program  /usr/lib/squid/basic_db_auth \
  --dsn "DBI:mysql:database=squid" --user "..." --password "..." \
  --table "accounts" --usercol "user" --passwdcol "passwd"

 acl login proxy_auth REQUIRED

 # check what groups a user belongs to
 external_acl_type group %LOGIN /etc/squid/db_group \
  --dsn "DBI:mysql:database=squid" --user "..." --passsword "..." \
  --table "groups" --usercol "user" --passwdcol "group"

 acl group1 external group Group1
 acl group2 external group Group2

 # basic security controls and DoS prevention
 http_access deny !Safe_ports
 http_access deny CONNECT !SSL_ports

 # require authentication for any access
 http_access deny !login

 # allow groups only to their listed domains
 http_access allow group1 sites1
 http_access allow group2 sites2

 http_access deny all

Amos