Quantcast

about simultaneous when using mysql and freeradius

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

about simultaneous when using mysql and freeradius

spacelee

i want to limit user's behavior, such as a username can login only once at the same time...

1、modifiy default and inner-tunnel in

#  Session database, used for checking Simultaneous-Use. Either the radutmp 
#  or rlm_sql module can handle this.
#  The rlm_sql module is *much* faster
session {
        #radutmp

        #  See "Simultaneous Use Checking Querie" in sql.conf
        sql

}
2、modify dialup.conf in etc/raddb/sql/mysql

# Uncomment simul_count_query to enable simultaneous use checking
 
simul_count_query = "SELECT COUNT(*) \
                            FROM ${acct_table1} \
                            WHERE username = '%{SQL-User-Name}' \
                             AND acctstoptime IS NULL"
   
3、add a entry to the table radgorucheck 
INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` , `op` , `Value` ) 
VALUES ( 
NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
);

4. update the user "test"  to the group "user"


i finally tried successfully, but if , i say if the radius server was down suddenly or the user logout improperly(such as he poweroff the computer directly). then the record will still be record in the table as he was still online ...so we need to modify the record, but how to ? can freeradius judge it by itself, or how to judge the user if offline ?

--
Spacelee



--
Spacelee

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

Marinko Tarlać
I already wrote about this problem on this list. Please search before
asking...



Spacelee wrote:

>
> i want to limit user's behavior, such as a username can login only
> once at the same time...
>
> 1、modifiy default and inner-tunnel in
>
> #  Session database, used for checking Simultaneous-Use. Either the
> radutmp
> #  or rlm_sql module can handle this.
> #  The rlm_sql module is *much* faster
> session {
>         #radutmp
>
>         #  See "Simultaneous Use Checking Querie" in sql.conf
>         sql
>
> }
> 2、modify dialup.conf in etc/raddb/sql/mysql
>
> # Uncomment simul_count_query to enable simultaneous use checking
>  
> simul_count_query = "SELECT COUNT(*) \
>                             FROM ${acct_table1} \
>                             WHERE username = '%{SQL-User-Name}' \
>                              AND acctstoptime IS NULL"
>    
> 3、add a entry to the table radgorucheck
> INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` , `op`
> , `Value` )
> VALUES (
> NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
> );
>
> 4. update the user "test"  to the group "user"
>
>
> i finally tried successfully, but if , i say if the radius server was
> down suddenly or the user logout improperly(such as he poweroff the
> computer directly). then the record will still be record in the table
> as he was still online ...so we need to modify the record, but how to
> ? can freeradius judge it by itself, or how to judge the user if offline ?
>
> --
> Spacelee
>
>
>
> --
> Spacelee
> ------------------------------------------------------------------------
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

Anton-47
In reply to this post by spacelee
It should be done by NAS. For example PPPoE and PPTP have lcp packets, If no response for some time from
client to NAS then NAS decides that session is down and sends acct-stop packet to radius server. Radius
server sets the corresponding record to SQL session table.

Or there is another method: depending on NAS type radiusd can connect to NAS and check activity of user
session directly on NAS - this is more reliable source of information. See radcheck.pl.
 

On Tue, 1 Jun 2010 16:24:54 +0700
Spacelee <[hidden email]> wrote:

>
> i want to limit user's behavior, such as a username can login only once at the same time...
>
> 1、modifiy default and inner-tunnel in
>
> #  Session database, used for checking Simultaneous-Use. Either the radutmp
> #  or rlm_sql module can handle this.
> #  The rlm_sql module is *much* faster
> session {
>         #radutmp
>
>         #  See "Simultaneous Use Checking Querie" in sql.conf
>         sql
>
> }
> 2、modify dialup.conf in etc/raddb/sql/mysql
>
> # Uncomment simul_count_query to enable simultaneous use checking
>
> simul_count_query = "SELECT COUNT(*) \
>                             FROM ${acct_table1} \
>                             WHERE username = '%{SQL-User-Name}' \
>                              AND acctstoptime IS NULL"
>
> 3、add a entry to the table radgorucheck
> INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` , `op` , `Value` )
> VALUES (
> NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
> );
>
> 4. update the user "test"  to the group "user"
>
>
> i finally tried successfully, but if , i say if the radius server was down suddenly or the user logout
> improperly(such as he poweroff the computer directly). then the record will still be record in the table as
> he was still online ...so we need to modify the record, but how to ? can freeradius judge it by itself, or
> how to judge the user if offline ?
>
> --
> Spacelee
>
>
>
> --
> Spacelee


--
Anton [WARM-RIPE]
Stack ltd division head
tel. 8 (3822) 555-797


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

spacelee
sorry, does the radcheck.pl included in freeradius now? does you mean /usr/sbin/checkrad ?


and i search the keywords "Simultaneous mysql radius down" , but found no results i need.

2010/6/1 Anton <[hidden email]>
It should be done by NAS. For example PPPoE and PPTP have lcp packets, If no response for some time from
client to NAS then NAS decides that session is down and sends acct-stop packet to radius server. Radius
server sets the corresponding record to SQL session table.

Or there is another method: depending on NAS type radiusd can connect to NAS and check activity of user
session directly on NAS - this is more reliable source of information. See radcheck.pl.


On Tue, 1 Jun 2010 16:24:54 +0700
Spacelee <[hidden email]> wrote:

>
> i want to limit user's behavior, such as a username can login only once at the same time...
>
> 1、modifiy default and inner-tunnel in
>
> #  Session database, used for checking Simultaneous-Use. Either the radutmp
> #  or rlm_sql module can handle this.
> #  The rlm_sql module is *much* faster
> session {
>         #radutmp
>
>         #  See "Simultaneous Use Checking Querie" in sql.conf
>         sql
>
> }
> 2、modify dialup.conf in etc/raddb/sql/mysql
>
> # Uncomment simul_count_query to enable simultaneous use checking
>
> simul_count_query = "SELECT COUNT(*) \
>                             FROM ${acct_table1} \
>                             WHERE username = '%{SQL-User-Name}' \
>                              AND acctstoptime IS NULL"
>
> 3、add a entry to the table radgorucheck
> INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` , `op` , `Value` )
> VALUES (
> NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
> );
>
> 4. update the user "test"  to the group "user"
>
>
> i finally tried successfully, but if , i say if the radius server was down suddenly or the user logout
> improperly(such as he poweroff the computer directly). then the record will still be record in the table as
> he was still online ...so we need to modify the record, but how to ? can freeradius judge it by itself, or
> how to judge the user if offline ?
>
> --
> Spacelee
>
>
>
> --
> Spacelee


--
Anton [WARM-RIPE]
Stack ltd division head
tel. 8 (3822) 555-797


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html



--
Spacelee

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

Anton-47
Yes, this is /usr/sbin/checkrad. Sorry for mistake.

You should read this script ...

Radiusd can be down or unrichable or packet can be loss. If You have NAS one of this type You can specify
this type in clients.conf (nastype = cisco). After than radiusd should use /usr/sbin/checkrad to check
simultaneous logins.

But if You have a situation when NAS has opened working session and radiusd has no records about it in
session table You can get double login. AFAIK in this case radiusd will not do simultaneous check at all.
But it should not happen -- when radiusd goes down suddenly the records in SQL session table should be stay
like opened sessions. And after radiusd starts again it thinks that that sessions are active :-), in this case
radiusd will use checkrad if nastype is configured to check his SQL records.

Moreover if You will connect simultaneously very fast (faster then Your SQL can handle queries) and without
using checkrad then You will get simultaneous logins too. This is because there is no any transaction is SQL
schema or no other method to make single unique login attempt in a time for SQL can see them like separate
tries.


On Tue, 1 Jun 2010 17:21:26 +0700
Spacelee <[hidden email]> wrote:

> sorry, does the radcheck.pl included in freeradius now? does you mean /usr/sbin/checkrad ?
>
>
> and i search the keywords "Simultaneous mysql radius down" , but found no results i need.
>
> 2010/6/1 Anton <[hidden email]<mailto:[hidden email]>>
> It should be done by NAS. For example PPPoE and PPTP have lcp packets, If no response for some time from
> client to NAS then NAS decides that session is down and sends acct-stop packet to radius server. Radius
> server sets the corresponding record to SQL session table.
>
> Or there is another method: depending on NAS type radiusd can connect to NAS and check activity of user
> session directly on NAS - this is more reliable source of information. See radcheck.pl<http://radcheck.pl>.
>
>
> On Tue, 1 Jun 2010 16:24:54 +0700
> Spacelee <[hidden email]<mailto:[hidden email]>> wrote:
>
> >
> > i want to limit user's behavior, such as a username can login only once at the same time...
> >
> > 1、modifiy default and inner-tunnel in
> >
> > #  Session database, used for checking Simultaneous-Use. Either the radutmp
> > #  or rlm_sql module can handle this.
> > #  The rlm_sql module is *much* faster
> > session {
> >         #radutmp
> >
> >         #  See "Simultaneous Use Checking Querie" in sql.conf
> >         sql
> >
> > }
> > 2、modify dialup.conf in etc/raddb/sql/mysql
> >
> > # Uncomment simul_count_query to enable simultaneous use checking
> >
> > simul_count_query = "SELECT COUNT(*) \
> >                             FROM ${acct_table1} \
> >                             WHERE username = '%{SQL-User-Name}' \
> >                              AND acctstoptime IS NULL"
> >
> > 3、add a entry to the table radgorucheck
> > INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` , `op` , `Value` )
> > VALUES (
> > NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
> > );
> >
> > 4. update the user "test"  to the group "user"
> >
> >
> > i finally tried successfully, but if , i say if the radius server was down suddenly or the user logout
> > improperly(such as he poweroff the computer directly). then the record will still be record in the table
> > as he was still online ...so we need to modify the record, but how to ? can freeradius judge it by
> > itself, or how to judge the user if offline ?
> >
> > --
> > Spacelee
> >
> >
> >
> > --
> > Spacelee
>
>
> --
> Anton [WARM-RIPE]
> Stack ltd division head
> tel. 8 (3822) 555-797
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>
>
> --
> Spacelee


--
Anton [WARM-RIPE]
Stack ltd division head
tel. 8 (3822) 555-797


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

Marinko Tarlać
In reply to this post by spacelee
Here we go again

1. add datetime column at the end of radacct table (name it for example
last_update_column)
2. edit queries in dialup.conf (for 2.x FR) or sql.conf (for 1.x FR) so
every update will update this column to (for example update radacct set
........, last_update_column = NOW() ).

Now you will have the correct time when was the last update for specific
session.

3. write php, perl, shell or any other script which you can start with
cron and check if there is any active sessions (acctstoptime = null or
acctstoptime = 0) which last update was X minutes ago.

For example, X can be 11 minutes and you can set up your NAS to send
updates every 5 minutes. This means that you can have at least 2 updates
before you close session with cron script.

This is not the only way to solve this problem but it works with all FR
version, and it doesn't depend on your NAS(es). Also, this isn't ideal
solution because if your NAS goes off and on again before you received
stop packets, all users will need to wait X minutes before they can
connect again. (there are no updates so last_update_column will be
unchanged and the cron script will close all sessions after X minutes)


Spacelee wrote:

> sorry, does the radcheck.pl <http://radcheck.pl> included in
> freeradius now? does you mean /usr/sbin/checkrad ?
>
>
> and i search the keywords "Simultaneous mysql radius down" , but found
> no results i need.
>
> 2010/6/1 Anton <[hidden email] <mailto:[hidden email]>>
>
>     It should be done by NAS. For example PPPoE and PPTP have lcp
>     packets, If no response for some time from
>     client to NAS then NAS decides that session is down and sends
>     acct-stop packet to radius server. Radius
>     server sets the corresponding record to SQL session table.
>
>     Or there is another method: depending on NAS type radiusd can
>     connect to NAS and check activity of user
>     session directly on NAS - this is more reliable source of
>     information. See radcheck.pl <http://radcheck.pl>.
>
>
>     On Tue, 1 Jun 2010 16:24:54 +0700
>     Spacelee <[hidden email] <mailto:[hidden email]>> wrote:
>
>     >
>     > i want to limit user's behavior, such as a username can login
>     only once at the same time...
>     >
>     > 1、modifiy default and inner-tunnel in
>     >
>     > #  Session database, used for checking Simultaneous-Use. Either
>     the radutmp
>     > #  or rlm_sql module can handle this.
>     > #  The rlm_sql module is *much* faster
>     > session {
>     >         #radutmp
>     >
>     >         #  See "Simultaneous Use Checking Querie" in sql.conf
>     >         sql
>     >
>     > }
>     > 2、modify dialup.conf in etc/raddb/sql/mysql
>     >
>     > # Uncomment simul_count_query to enable simultaneous use checking
>     >
>     > simul_count_query = "SELECT COUNT(*) \
>     >                             FROM ${acct_table1} \
>     >                             WHERE username = '%{SQL-User-Name}' \
>     >                              AND acctstoptime IS NULL"
>     >
>     > 3、add a entry to the table radgorucheck
>     > INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` ,
>     `op` , `Value` )
>     > VALUES (
>     > NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
>     > );
>     >
>     > 4. update the user "test"  to the group "user"
>     >
>     >
>     > i finally tried successfully, but if , i say if the radius
>     server was down suddenly or the user logout
>     > improperly(such as he poweroff the computer directly). then the
>     record will still be record in the table as
>     > he was still online ...so we need to modify the record, but how
>     to ? can freeradius judge it by itself, or
>     > how to judge the user if offline ?
>     >
>     > --
>     > Spacelee
>     >
>     >
>     >
>     > --
>     > Spacelee
>
>
>     --
>     Anton [WARM-RIPE]
>     Stack ltd division head
>     tel. 8 (3822) 555-797
>
>
>     -
>     List info/subscribe/unsubscribe? See
>     http://www.freeradius.org/list/users.html
>
>
>
>
> --
> Spacelee
> ------------------------------------------------------------------------
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

spacelee
my nas client is pptp and ppp, how can i set pptp to send updates every 5 minutes?

On Tue, Jun 1, 2010 at 7:13 PM, Marinko Tarlac <[hidden email]> wrote:
Here we go again

1. add datetime column at the end of radacct table (name it for example last_update_column)
2. edit queries in dialup.conf (for 2.x FR) or sql.conf (for 1.x FR) so every update will update this column to (for example update radacct set ........, last_update_column = NOW() ).

Now you will have the correct time when was the last update for specific session.

3. write php, perl, shell or any other script which you can start with cron and check if there is any active sessions (acctstoptime = null or acctstoptime = 0) which last update was X minutes ago.

For example, X can be 11 minutes and you can set up your NAS to send updates every 5 minutes. This means that you can have at least 2 updates before you close session with cron script.
This is not the only way to solve this problem but it works with all FR version, and it doesn't depend on your NAS(es). Also, this isn't ideal solution because if your NAS goes off and on again before you received stop packets, all users will need to wait X minutes before they can connect again. (there are no updates so last_update_column will be unchanged and the cron script will close all sessions after X minutes)


Spacelee wrote:
sorry, does the radcheck.pl <http://radcheck.pl> included in freeradius now? does you mean /usr/sbin/checkrad ?



and i search the keywords "Simultaneous mysql radius down" , but found no results i need.

2010/6/1 Anton <[hidden email] <mailto:[hidden email]>>


   It should be done by NAS. For example PPPoE and PPTP have lcp
   packets, If no response for some time from
   client to NAS then NAS decides that session is down and sends
   acct-stop packet to radius server. Radius
   server sets the corresponding record to SQL session table.

   Or there is another method: depending on NAS type radiusd can
   connect to NAS and check activity of user
   session directly on NAS - this is more reliable source of
   information. See radcheck.pl <http://radcheck.pl>.



   On Tue, 1 Jun 2010 16:24:54 +0700
   Spacelee <[hidden email] <mailto:[hidden email]>> wrote:

   >
   > i want to limit user's behavior, such as a username can login
   only once at the same time...
   >
   > 1、modifiy default and inner-tunnel in
   >
   > #  Session database, used for checking Simultaneous-Use. Either
   the radutmp
   > #  or rlm_sql module can handle this.
   > #  The rlm_sql module is *much* faster
   > session {
   >         #radutmp
   >
   >         #  See "Simultaneous Use Checking Querie" in sql.conf
   >         sql
   >
   > }
   > 2、modify dialup.conf in etc/raddb/sql/mysql
   >
   > # Uncomment simul_count_query to enable simultaneous use checking
   >
   > simul_count_query = "SELECT COUNT(*) \
   >                             FROM ${acct_table1} \
   >                             WHERE username = '%{SQL-User-Name}' \
   >                              AND acctstoptime IS NULL"
   >
   > 3、add a entry to the table radgorucheck
   > INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` ,
   `op` , `Value` )
   > VALUES (
   > NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’
   > );
   >
   > 4. update the user "test"  to the group "user"
   >
   >
   > i finally tried successfully, but if , i say if the radius
   server was down suddenly or the user logout
   > improperly(such as he poweroff the computer directly). then the
   record will still be record in the table as
   > he was still online ...so we need to modify the record, but how
   to ? can freeradius judge it by itself, or
   > how to judge the user if offline ?
   >
   > --
   > Spacelee
   >
   >
   >
   > --
   > Spacelee


   --
   Anton [WARM-RIPE]
   Stack ltd division head
   tel. 8 (3822) 555-797


   -
   List info/subscribe/unsubscribe? See
   http://www.freeradius.org/list/users.html




--
Spacelee
------------------------------------------------------------------------


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html



--
Spacelee

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: about simultaneous when using mysql and freeradius

Alan DeKok-2
Spacelee wrote:
> my nas client is pptp and ppp, how can i set pptp to send updates every
> 5 minutes?

  See the PPTP documentation.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Loading...