sqlippool + MySQL

Peter Nixon listuser at peternixon.net
Tue Oct 17 08:53:53 CEST 2006


Hi Jan and Roberto

We ARE doing serious work on sqlippool but it is all with Postgresql. As Jan 
says someone with a little MySQL knowledge shoudn't have problems making 
those queries work with MySQL. Once you have them working please send them to 
my so I can include them in cvs.

Cheers

Peter


On Tue 17 Oct 2006 00:58, Jan Mulders wrote:
> Someone needs to do some serious work on sqlippool. I'd do so, but
> currently I have no need for SQL-assigned IPs, as I only have one
> RADIUS server - and if it fails over, the least thing I have to worry
> about is current IP assignments.
>
> I recommend finding someone who is adept at *SQL and buy them a pizza.
> Then ask them to 'translate' those queries for you.
>
> Jan
>
> On 16/10/06, Roberto Gonzalez Azevedo <rgonzalez at censanet.com.br> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > Does somebody knows how to configure sqlippool with MySQL ?
> > The sqlippool.conf example is for pgsql. And for MySQL ?
> >
> > Here is my sqlippool.conf, corrected for MySQL:
> > "
> >
> > sqlippool sqlippool {
> >
> >  #
> >  # SQL connection information
> >  #
> >  sql-instance-name = "sql"
> >
> >  # lease_duration. fix for lost acc-stop packets
> >  lease-duration = 3600
> >
> >  # Attribute which should be considered unique per NAS
> >  pool-key = "%{Acct-Session-Id}"
> >  pool-name = "mypool"
> >
> >  # pool-key = "%{Calling-Station-Id}"
> >
> >
> >  #
> >  # This series of queries allocates an IP address
> >  #
> >  allocate-clear = "UPDATE radippool \
> >   SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
> >   expiry_time = NOW() - INTERVAL 1 SECOND \
> >   WHERE pool_key = '${pool-key}'"
> >
> >  # note the ORDER BY clause of next query, it'll try to allocate IPs
> >  # like Cisco internal pools do - it _trys_ to allocate the same
> > IP-address # which user had last session...
> >  allocate-find = "SELECT FramedIPAddress FROM radippool \
> >   WHERE pool_name = '%{reply:Pool-Name}' AND expiry_time < NOW() \
> >   ORDER BY pool_name, (UserName <> '%{User-Name}'), (CallingStationId <>
> > '%{Calling-Station-Id}'), expiry_time \
> >   LIMIT 1 \
> >   FOR UPDATE"
> >
> >  allocate-update = "UPDATE radippool \
> >   SET NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
> >   CallingStationId = '%{Calling-Station-Id}', UserName = '%{User-Name}',
> > \ expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
> >   WHERE FramedIPAddress = '%{Framed-IP-Address}'"
> >
> >
> >
> >  #
> >  # This series of queries frees an IP number when an accounting
> >  # START record arrives
> >  #
> >  start-update = "UPDATE radippool \
> >   SET expiry_time = NOW() + INTERVAL %J SECOND \
> >   WHERE NASIPAddress = '%n' AND pool_key = '${pool-key}' AND pool_name =
> > '%P'"
> >
> >  #
> >  # This series of queries frees an IP number when an accounting
> >  # STOP record arrives
> >  #
> >  stop-clear = "UPDATE radippool \
> >   SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
> >   expiry_time = NOW() - INTERVAL 1 SECOND \
> >   WHERE NASIPAddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'
> > AND UserName = '%{User-Name}' \
> >   AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress =
> > '%{Framed-IP-Address}'"
> >
> >
> >
> >
> >  #
> >  # This series of queries frees an IP number when an accounting
> >  # ALIVE record arrives
> >  #
> >  alive-update = "UPDATE radippool \
> >   SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
> >   WHERE NASIPAddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}'
> > AND UserName = '%{User-Name}' \
> >   AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress =
> > '%{Framed-IP-Address}'"
> >
> >
> >  #
> >  # This series of queries frees the IP numbers allocate to a
> >  # NAS when an accounting ON record arrives
> >  #
> >  on-clear = "UPDATE radippool \
> >   SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
> >   expiry_time = NOW() - INTERVAL 1 SECOND \
> >   WHERE NASIPAddress = '%{NAS-IP-Address}' AND UserName = '%{User-Name}'
> > \ AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress =
> > '%{Framed-IP-Address}'"
> >
> >  #
> >  # This series of queries frees the IP numbers allocate to a
> >  # NAS when an accounting OFF record arrives
> >  #
> >  off-clear = "UPDATE radippool \
> >   SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \
> >   expiry_time = NOW() - INTERVAL 1 SECOND \
> >   WHERE NASIPAddress = '%{NAS-IP-Address}' AND UserName = '%{User-Name}'
> > \ AND CallingStationId = '%{Calling-Station-Id}' AND FramedIPAddress =
> > '%{Framed-IP-Address}'"
> >
> >
> > }
> > "
> >
> > Here is the radiusd -X:
> > "
> > ...
> > Mon Oct 16 17:50:50 2006 : Debug:   Processing the post-auth section of
> > radiusd.conf
> > Mon Oct 16 17:50:50 2006 : Debug: modcall: entering group post-auth for
> > request 0
> > Mon Oct 16 17:50:50 2006 : Debug:   modsingle[post-auth]: calling
> > sqlippool (rlm_sqlippool) for request 0
> > Mon Oct 16 17:50:50 2006 : Debug: rlm_sqlippool: Framed-IP-Address
> > already exists
> > Mon Oct 16 17:50:50 2006 : Debug:   modsingle[post-auth]: returned from
> > sqlippool (rlm_sqlippool) for request 0
> > Mon Oct 16 17:50:50 2006 : Debug:   modcall[post-auth]: module
> > "sqlippool" returns noop for request 0
> > Mon Oct 16 17:50:50 2006 : Debug: modcall: leaving group post-auth
> > (returns noop) for request 0
> > ...
> > ...
> > Mon Oct 16 17:50:50 2006 : Debug:   modsingle[accounting]: calling
> > sqlippool (rlm_sqlippool) for request 1
> > Mon Oct 16 17:50:50 2006 : Debug: rlm_sql (sql): Reserving sql socket id:
> > 2 Mon Oct 16 17:50:50 2006 : Debug: radius_xlat:  'BEGIN'
> > Mon Oct 16 17:50:50 2006 : Debug: radius_xlat:  'UPDATE radippool   SET
> > expiry_time = NOW() + INTERVAL 3600 SECOND   WHERE NASIPAddress =
> > 'xx.xx.xx.xx' AND pool_key = '4533F0AA608100' AND pool_name =
> > 'sqlippool'' ...
> > "
> >
> > This 'update' will never match !!! Here is the radippool's current line:
> > "
> > mysql> select * from radippool;
> >
> > +----+-----------+-----------------+--------------+-----------------+----
> >--------------+---------------------+----------+----------+
> >
> > | id | pool_name | FramedIPAddress | NASIPAddress | CalledStationId |
> >
> > CallingStationId | expiry_time         | UserName | pool_key |
> > +----+-----------+-----------------+--------------+-----------------+----
> >--------------+---------------------+----------+----------+
> >
> > |  1 | mypool    | 192.168.0.1     |              |                 |
> > |
> >                | 0000-00-00 00:00:00 |          |          |
> >
> > +----+-----------+-----------------+--------------+-----------------+----
> >--------------+---------------------+----------+----------+ 1 row in set
> > (0.00 sec)
> > "
> >
> > In radgroupcheck:
> > "
> > In (mysql) radgroupcheck :
> >
> > +-----+-------------------+------------------+----+-------------+------+
> >
> > | id  | GroupName         | Attribute        | op | Value       | prio |
> >
> > +-----+-------------------+------------------+----+-------------+------+
> >
> > |  39 | mygroup           | Pool-Name        | := | mypool      |    1 |
> >
> > ...
> > "
> >
> >
> > Thanks.
> > - --
> > - ----------------------------
> > Roberto Gonzalez Azevedo
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.4.5 (GNU/Linux)
> >
> > iD8DBQFFM/aEF+EMwkXLsEwRAsmvAKC5gV8xZN8g4gxZwrdpVQi7eTCzHgCdEfNn
> > 1O0G6WtRGvyLBter1vtzQSk=
> > =z+5W
> > -----END PGP SIGNATURE-----
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html

-- 

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20061017/b3cb31c5/attachment.pgp>


More information about the Freeradius-Users mailing list