Can't get my "SQL Based IP Pool" running ; ( running FreeRADIUS Version 1.1.7 on FreeBSD 7.0 with Postgresql-Server-8.3beta2. I got everything working fine so far except SQL Based IP Pool --> "/http://wiki.freeradius.org/Rlm_sqlippool/" <-- ; ) PostgreSQL: ######################################################## CREATE TABLE radippool ( id BIGSERIAL PRIMARY KEY, pool_name text NOT NULL, FramedIPAddress INET, NASIPAddress text NOT NULL, CalledStationId VARCHAR(64), CallingStationId text DEFAULT ''::text NOT NULL, expiry_time TIMESTAMP(0) without time zone NOT NULL, username text DEFAULT ''::text, pool_key VARCHAR(30) NOT NULL ); ################################################################# If I now try to do the following step - like the HowTo describes I'll get folowing result: INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.1'); INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.2'); INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.3'); INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.4'); .. my phpPgAdmin Webinterface is telling me following Error: ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *SQL Error:* ERROR: null value in column "nasipaddress" violates not-null constraint *In the command:* INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.1'); INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.2'); INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.3'); INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '192.168.0.4'); ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ I do only have success if I execute the Postgres command changed like that: INSERT INTO radippool (pool_name, framedipaddress, nasipaddress, expiry_time, pool_key) VALUES ('mypool', '192.168.5.1', '10.1.10.80', '2008-12-31 00:00:00', '0'); INSERT INTO radippool (pool_name, framedipaddress, nasipaddress, expiry_time, pool_key) VALUES ('mypool', '192.168.5.2', '10.1.10.80', '2008-12-31 00:00:00', '0'); ^^ but I'm not happy with those Datas .. actualy I'm pretty unsure if that's working stuff !! Besides that I tried to have a expire date like 86400 Seconds .. but didn't work. And Where is my Netmask in radippool table?? ;) question over question ;) any way .. still "radius -X" tells me: ++++++++++++++++++++++++++++++++++++++++++++ modcall: leaving group CHAP (returns ok) for request 0 Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 0 rlm_sqlippool: Framed-IP-Address already exists modcall[post-auth]: module "sqlippool" returns noop for request 0 rlm_sql (sql): Processing sql_postauth radius_xlat: 'test' ++++++++++++++++++++++++++++++++++++++++++++ This is how my radiusd.conf looks like in the ippool section: ################################################################################## ################################################################################## ippool main_pool { # range-start,range-stop: The start and end ip # addresses for the ip pool # range-start = 192.168.1.1 # range-stop = 192.168.3.254 # netmask: The network mask used for the ip's # netmask = 255.255.255.0 # cache-size: The gdbm cache size for the db # files. Should be equal to the number of ip's # available in the ip pool # cache-size = 800 # session-db: The main db file used to allocate ip's to clients # session-db = ${raddbdir}/db.ippool # ip-index: Helper db index file used in multilink # ip-index = ${raddbdir}/db.ipindex # override: Will this ippool override a Framed-IP-Address already set override = yes # maximum-timeout: If not zero specifies the maximum time in seconds an # entry may be active. Default: 0 # maximum-timeout = 0 } # $INCLUDE ${confdir}/sqlippool.conf $INCLUDE ${confdir}/postgresqlippool.conf # OTP token support. Not included by default. # $INCLUDE ${confdir}/otp.conf ################################################################################## ################################################################################## This is how my users conf does look like: ################################################## test User-Password == "123456" Service-Type = Framed-User, Framed-Protocol = PPP, Framed-IP-Address = 255.255.255.254, # Framed-IP-Netmask = 255.255.255.255, Framed-Routing = Broadcast-Listen, Framed-Filter-Id = "std.ppp", Framed-MTU = 1492, Framed-Compression = Van-Jacobsen-TCP-IP, Pool-Name = "mypool", ################################################## ^^ WTF goes wrong here ??? ; )) If I comment "Framed-IP-Address = 255.255.255.254," out it says pool-name not found radius -X tells me: rlm_sqlippool: missing pool_name modcall[post-auth]: module "sqlippool" returns noop for request 0 rlm_sql (sql): Processing sql_postauth It would be very greathful to get some useful advices here ; ) Thank you Regards, Leander P.S. It might be a double posting - but I'm not sure because I didn't get a confirmation about it and I also didn't find it ... so if yes - sorry for that ; )

Ivan Kalik wrote:

...

This is how my users conf does look like:

################################################## test User-Password == "123456" Service-Type = Framed-User, Framed-Protocol = PPP, Framed-IP-Address = 255.255.255.254, # Framed-IP-Netmask = 255.255.255.255, Framed-Routing = Broadcast-Listen, Framed-Filter-Id = "std.ppp", Framed-MTU = 1492, Framed-Compression = Van-Jacobsen-TCP-IP, Pool-Name = "mypool", ################################################## ^^ WTF goes wrong here ??? ; ))

1. Pool-Name is a check not a reply item. It also uses := as an operator. This is documented.

2. Don't use User-Password as a password attribute in 1.1.7. What should you use? Read instructions in users file, readme, FAQ etc.

Reading documentation provided with the server helps. A lot.

Ivan Kalik Kalik Informatika ISP

Hi Ivan, well, ... I'm not a complete noob : ) I did read the docs - but I might missunderstood something in it - that's why I'm here ; ) . ... I actually use Dialup Admin to handle users - and I also do have a running a working radius and PPPoE server thing ... accounting works ... everything works based on SQL ... so ... but right now I'm at a point where I want to expand it and want to make use of IP-Pools based on SQL. I'm actually not using regular "users" conf file anymore because it's SQL based and Dialup Admin is handling my SQL DB tables - so the User-Password thing shouldn't be my problem I only pasted it here because the rest of the attributes look like that in my Dialup Admin. I read to use := as operator - and I already tried - but without success. Same Error then before appears. rlm_sqlippool: Framed-IP-Address already exists modcall[post-auth]: module "sqlippool" returns noop for request 0 Thanks Regards, Leander

From your previous post:

PostgreSQL: ######################################################## CREATE TABLE radippool ( id BIGSERIAL PRIMARY KEY, pool_name text NOT NULL, FramedIPAddress INET, NASIPAddress text NOT NULL, CalledStationId VARCHAR(64), CallingStationId text DEFAULT ''::text NOT NULL, expiry_time TIMESTAMP(0) without time zone NOT NULL, username text DEFAULT ''::text, pool_key VARCHAR(30) NOT NULL ); ################################################################# This is not the schema from ippool.sql. You have made changes and broke the queries. Use the schema provided with the server.

This is my actual output for a user who has attribute: "Pool-Name = mypool"

root ~ # radtest leander 123456 10.1.10.80:1812 2 schaefer Sending Access-Request of id 79 to 10.1.10.80 port 1812 User-Name = "leander" User-Password = "123456" NAS-IP-Address = 255.255.255.255 NAS-Port = 2 rad_recv: Access-Accept packet from host 10.1.10.80:1812, id=79, length=122 Framed-Protocol = PPP Framed-MTU = 1492 Framed-Compression = Van-Jacobson-TCP-IP Session-Timeout = 86400 Framed-IP-Address = 255.255.255.254 Service-Type = Framed-User Framed-IP-Netmask = 255.255.255.255 root ~ #

^^ Btw. Why is my NAS-IP-Address = 255.255.255.255 ? I didn't write that in clients.conf? Where might I change that?

You can't configure NAS-IP-Address in radtest. If you want to send different NAS-IP-Address use radclient.

.... just once again .. because I can't get rid of the guess that my mistake is in the data I typed into the DB table:

INSERT INTO radippool (pool_name, framedipaddress, nasipaddress, expiry_time, pool_key) VALUES ('mypool', '192.168.5.1', '10.1.10.80', '2008-12-31 00:00:00', '0');

Don't. Type what's documented. If you use proper schema it will work.

^^ 1. What is pool_key standing for? Why do _I_ have to write that in manually?

No you don't. You are just breking thing further. Again, use proper schema.

^^ 2. What is expiry_time good for if there is already an lease-duration = 3600 configured in the postgresqlippool.conf? And why do I get forced to use unix timestamp instead of using a simple time in secounds when it'll be expired?

Seconds from when? You do need a timestamp for lease start then. Ivan Kalik Kalik Informatika ISP

^^ It's not ippool.sql - we're talking about the RADIPPOOL table - No I haven't changed this - go and check it by yourself:/freeradius-1.1.7/freeradius-1.1.7/doc/examples/postgresql And besides that I'm not using "ippool.sql" but instead of I'm using "postgresqlippool.conf" because I'm using PostgreSQL as DB Server.

You are correct. Schema in 1.1.7 is broken and will not work as documented. I have attached postgre ippool.sql schema from 2.0.4. That will correct the input errors. It's also indexed so it should work faster.

Didn't you read what I wrote? ; ) It won't work , because there are fields which can't be NULL ... for example "pool_key" and so on

...

.. proper shema won't work.

This one should.

...

...

^^ 2. What is expiry_time good for if there is already an lease-duration = 3600 configured in the postgresqlippool.conf? And why do I get forced to use unix timestamp instead of using a simple time in secounds when it'll be expired?

Seconds from when? You do need a timestamp for lease start then.

^^ ah, ok that makes sence ; ) but untill now I'm only able to tell the DB a fix Timestamp like 2008-12-31 11:15:00 but how do I change that the server is picking up the actual time everytime somebody gets an IP. Might there be a special variable for our case?

start-update query should do that. Ivan Kalik Kalik Informatika ISP

Leander S. wrote:

this is how my radius.conf looks like in the IPPOOL section:

The SQL IPpool module in 1.1.7 is broken. Don't use it.

##################################################################################

##################################################################################

ippool main_pool {

# range-start,range-stop: The start and end ip # addresses for the ip pool # range-start = 192.168.1.1 # range-stop = 192.168.3.254

# netmask: The network mask used for the ip's # netmask = 255.255.255.0

# cache-size: The gdbm cache size for the db # files. Should be equal to the number of ip's # available in the ip pool # cache-size = 800

# session-db: The main db file used to allocate ip's to clients # session-db = ${raddbdir}/db.ippool

# ip-index: Helper db index file used in multilink # ip-index = ${raddbdir}/db.ipindex

# override: Will this ippool override a Framed-IP-Address already set # override = no

# maximum-timeout: If not zero specifies the maximum time in seconds an # entry may be active. Default: 0 # maximum-timeout = 0 }

# $INCLUDE ${confdir}/sqlippool.conf

$INCLUDE ${confdir}/postgresqlippool.conf

# OTP token support. Not included by default. # $INCLUDE ${confdir}/otp.conf

##################################################################################

##################################################################################

and this is my postgresqlippool.conf which I'm using - it's left original.

## Configuration for the SQL based IP Pool module (rlm_sqlippool) ## ## The database schemas are available at: ## ## doc/examples/*.sql ## ## $Id: postgresqlippool.conf,v 1.1.2.1 2007/07/16 06:35:22 pnixon Exp $

sqlippool sqlippool {

################################################ ## SQL instance to use (from postgresql.conf) ## ################################################ sql-instance-name = "sql"

## SQL table to use for ippool range and lease info ippool_table = "radippool"

## IP lease duration. (Leases expire even if Acct Stop packet is lost) lease-duration = 3600

## Attribute which should be considered unique per NAS ## Using NAS-Port gives behaviour similar to rlm_ippool. (And ACS) ## Using Calling-Station-Id works for NAS that send fixed NAS-Port ## ONLY change this if you know what you are doing! pool-key = "%{NAS-Port}" #pool-key = "%{Calling-Station-Id}"

########################################### ## PostgreSQL specific queries. ## ###########################################

## This query allocates an IP address from the Pool ## The ORDER BY clause of this query tries to allocate the same IP-address ## to the user that they had last session... allocate-find = "SELECT framedipaddress FROM ${ippool_table} \ WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \ ORDER BY (username <> '%{SQL-User-Name}'), \ (callingstationid <> '%{Calling-Station-Id}'), expiry_time \ LIMIT 1 \ FOR UPDATE"

## If you prefer to allocate a random IP address every time, use this query instead #allocate-find = "SELECT framedipaddress FROM ${ippool_table} \ # WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < 'now'::timestamp(0) \ # ORDER BY RANDOM() \ # LIMIT 1 \ # FOR UPDATE"

## This query marks the IP address handed out by "allocate-find" as used ## for the period of "lease-duration" after which time it may be reused. allocate-update = "UPDATE ${ippool_table} \ SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \ callingstationid = '%{Calling-Station-Id}', username = '%{SQL-User-Name}', \ expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \ WHERE framedipaddress = '%I'"

## This query frees the IP address assigned to "pool-key" when a new request ## comes in for the same "pool-key". This means that either you are losing ## accounting Stop records or you use Calling-Station-Id instead of NAS-Port ## as your "pool-key" and your users are able to reconnect before your NAS ## has timed out their previous session. (Generally on wireless networks) ## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port ## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}' ## from the WHERE clause) allocate-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', \ expiry_time = 'now'::timestamp(0) - '1 second'::interval \ WHERE pool_key = '${pool-key}' \ AND nasipaddress = '%{Nas-IP-Address}'"

## This query extends an IP address lease by "lease-duration" when an accounting ## START record arrives start-update = "UPDATE ${ippool_table} \ SET expiry_time = 'now'::timestamp(0) + '${lease-duration} second'::interval \ WHERE nasipaddress = '%{NAS-IP-Address}' \ AND pool_key = '${pool-key}'"

## This query frees an IP address when an accounting ## STOP record arrives stop-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', \ expiry_time = 'now'::timestamp(0) - '1 second'::interval \ WHERE nasipaddress = '%{Nas-IP-Address}' \ AND pool_key = '${pool-key}' \ AND username = '%{SQL-User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' \ AND framedipaddress = '%{Framed-IP-Address}'"

## This query extends an IP address lease by "lease-duration" when an accounting ## ALIVE record arrives alive-update = "UPDATE ${ippool_table} \ SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \ WHERE nasipaddress = '%{Nas-IP-Address}' \ AND pool_key = '${pool-key}' \ AND username = '%{SQL-User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' \ AND framedipaddress = '%{Framed-IP-Address}'"

## This query frees all IP addresses allocated to a NAS when an ## accounting ON record arrives from that NAS on-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', \ expiry_time = 'now'::timestamp(0) - '1 second'::interval \ WHERE nasipaddress = '%{Nas-IP-Address}'"

## This query frees all IP addresses allocated to a NAS when an ## accounting OFF record arrives from that NAS off-clear = "UPDATE ${ippool_table} \ SET nasipaddress = '', pool_key = 0, callingstationid = '', \ expiry_time = 'now'::timestamp(0) - '1 second'::interval \ WHERE nasipaddress = '%{Nas-IP-Address}'"

}

Thank you for helping

Regards,

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