sqlippool + MySQL
Guilherme Franco
guilhermefranco at gmail.com
Tue Oct 17 13:17:00 CEST 2006
Hi Peter,
Regarding this post, the problem with Oracle and sqlippool still exists.
I've altered the postgresql inet to varchar and it works. But in
oracle, with varchar it does not works (the query return exactly the
same result in oracle's sqlplus as in postgresql, but freeradius keeps
saying "sqlippool_query1: row[0] returned NULL
rlm_sqlippool: ip=[] len=0
radius_xlat: 'COMMIT'
rlm_sqlippool: IP number could not be allocated.
").
So this proves that it's not an issue with the queries (at least for oracle).
Cheers.
On 10/17/06, Peter Nixon <listuser at peternixon.net> wrote:
> 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
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>
>
--
Guilherme de Oliveira Franco
Damovo - Brasil
More information about the Freeradius-Users
mailing list