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