Hello, to some time ago informed me that the ippool not work properly with mysql. As it is now? I'm not sure what the problems were occurring, but informed me that it worked better and smoothly only in postgres. They try to succeed in this scenario freeradius / mysql? Thanks for listening, Michell 2012/6/28 Fajar A. Nugraha <list@fajar.net>

On Thu, Jun 28, 2012 at 7:03 PM, Taz Manian <djtazie@hotmail.com> wrote:

...

Hi Guys,

Im having a problem with Ippools with freeradius2 and i cant seem to get any username to get an address from the pool.

90% of the usernames will have static IP's but i want a few to be in a pool but i really am stumped - i tried putting

username@realm Framed-Pool := EZPOOL

into the radreply section and it gives me a reply when i test it #

Please check the wiki, IIRC you should put it in radcheck, not radreply. And the attribute is Pool-Name, not Framed-Pool.

...

so i know is readying that - i then have a pool set up in radippool

Also, IMHO you should just use rlm_sqlipool. It's easier to setup and debug.

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

On Thu, Jun 28, 2012 at 7:26 PM, Michell <bill.cvel@gmail.com> wrote:

Hello,

to some time ago informed me that the ippool not work properly with mysql.

It works just fine

As it is now? I'm not sure what the problems were occurring, but informed me that it worked better and smoothly only in postgres.

IIRC from the discussion, postgres should perform better compared to mysql on the DEFAULT setup due to locking (or lack of it). Function-wise, both work fine. If your load is pretty light, OR you know how to adjust your mysql setup to avoid the locking issue, it should be irrelevant. -- Fajar

Taz Manian wrote:

I checked each one of them and not one said anything about radcheck or radreply.

Because they give examples for the "users" file. They don't give examples for SQL, LDAP, external programs, Perl, Python, etc. The "users" file example has the Pool-Name on the first line. The documentation for the "users" file says that this makes it a check item. The documentation for the SQL module describes how to map "users" file entries to SQL. It *is* documented. We expect that *some* independent understanding is necessary. Alan DeKok.

Thanks for the input so far everyone.Can you guys confirm that this is the correct format for the SQl side of things please ?Im still having problems and just want to confirm that this is correct. mysql> select * from radcheck; +----+--------------------+--------------------+----+------------+ | id | username | attribute | op | value | +----+--------------------+--------------------+----+------------+ 1 username@realm Password := 1234567 mysql> select * from usergroup; +---------------+--------------------+----------+ |username | GroupName | priority | +---------------+--------------------+----------+ username@realm MyGroup 1 mysql> select * from radgroupcheck; +----+---------------------+-----------+----+---------------+ | id | groupname | attribute | op | value | +----+---------------------+-----------+----+---------------+ 1 MyGroup Pool-Name := EZ_POOL_NAME mysql> select * from radippool; +----+---------------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+ | id | pool_name | framedipaddress | nasipaddress | calledstationid | callingstationid | expiry_time | username | pool_key | +----+---------------+-----------------+--------------+-----------------+------------------+-------------+----------+----------+ 1 EZ_POOL_NAME 192.168.1.2 0000-00-00 00:00:00 2 EZ_POOL_NAME 192.168.1.3 0000-00-00 00:00:00 mysql> select * from radgroupreply; +----+-----------------+---------------+-------+-------------+ | id | GroupName | Attribute | Op | Value | +----+-----------------+---------------+-------+-------------+ 1 EZ_POOL_NAME Framed-MTU := 1500 2 EZ_POOL_NAME Service-Type := Framed-User 3 EZ_POOL_NAME Framed-Protocol := PPP If theres anything im missing it would help Thanks

Date: Thu, 28 Jun 2012 20:38:52 +0700 Subject: Re: FreeRADIUS SQl Ippool problem - From: list@fajar.net To: djtaz@iol.ie; freeradius-users@lists.freeradius.org

On Thu, Jun 28, 2012 at 8:22 PM, Taz Manian <djtazie@hotmail.com> wrote:

...

I did check the wiki , i have been on it for the last 3 days trying to figure this out

I did a search for Pool-Name and i got 4 different results as below

http://wiki.freeradius.org/search?q=Pool-Name

http://wiki.freeradius.org/Rlm_sqlippool

Did you notice I specifically mentioned that page?

...

http://wiki.freeradius.org/Rlm_ippool http://wiki.freeradius.org/Users http://wiki.freeradius.org/Ippool%20and%20radius%20clients

I checked each one of them and not one said anything about radcheck or radreply.

Did you read this paragraph?

" To assign a user an IP from a pool you simply need to have a Pool-Name Attribute (Keep in mind that it is a CONTROL attribute, not a reply attribute) in the required configuration file, which is either in files(users), sql or any other type of configuration schema. "

It should be clear enough that Pool-Name should not be in rad(group)reply (since that table holds reply attributes).

If you don't know where to put CONTROL attribute (which is in rad(group)check, btw), then we might need to add an entry for that.

...

I checked on Google and found some pages that said TO USE Framed-Pool , and i could see that when i ran radiusd -X

Your primary source of information should be the included documentation (comments on the config file, man pages, etc).

After that, the wiki.

After that, this list.

If you decide to follow some random page, then no wonder you get random result.

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

Thanks Alan, But the username isnt the problem, that authenticates properly and if i put in mysql> select * from radreply; +----+-------------------------+------------------------+---------------+------+ | id | UserName | Attribute | Value | Op | +----+-------------------------+------------------------+---------------+------+ | 1 | username@realm | Framed-IP-Address | 1.2.3.4 | := | Then i get the framed IP address back no problems, the problem is that the pool isnt working at all, and i know its something stupid im doing but i cant see to see it.Ive built 3 radius boxes - one proxy , one for l2pt tunnels that gets a hand off from the proxy if thats the correct realm and one sql based one (also passed from the proxy) which works fine for userthat have the framed IP's but not for users that have pools. I also have hand offs from a Cisco ACS devices and all of that works perfectly.Im just stumped on the ip pools and its the last thing i want to get working so its frustrating. I get no info back for the pools at all when i do a radiusd -X Taz > Date: Fri, 29 Jun 2012 07:46:36 -0400

From: aland@deployingradius.com To: djtaz@iol.ie; freeradius-users@lists.freeradius.org Subject: Re: FreeRADIUS SQl Ippool problem -

Taz Manian wrote:

...

Can you guys confirm that this is the correct format for the SQl side of things please ?

Im still having problems and just want to confirm that this is correct.

It's not.

...

mysql> select * from radcheck; +----+--------------------+--------------------+----+------------+ | id | username | attribute | op | value | +----+--------------------+--------------------+----+------------+ 1 username@realm <mailto:username@realm> Password := 1234567

Fix that. Honestly, it's been wrong for 6 years. Why do people still do this?

See the FAQ for a *correct* example.

...

If theres anything im missing it would help

a) did you follow the directions?

b) does it work?

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

Sorry , i thought i had attached the radiusd -X report......... rad_recv: Access-Request packet from host 192.168.1.150 port 56916, id=97, length=123 User-Name = "username@realm" Acct-Session-Id = "1340979220W15beb" NAS-Port = 0 Calling-Station-Id = "1115551212" NAS-Identifier = "192.168.1.150" User-Password = "1234567" Message-Authenticator = 0x5b68c4b10cdd7a5dc3cf1d01361e6d24 # Executing section authorize from file /etc/raddb/sites-enabled/default +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop [suffix] Looking up realm "realm" for User-Name = "username@realm" [suffix] No such realm "realm" ++[suffix] returns noop [eap] No EAP-Message, not doing EAP ++[eap] returns noop [sql] expand: %{User-Name} -> username@realm [sql] sql_set_user escaped user --> 'username@realm' rlm_sql (sql): Reserving sql socket id: 1 [sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'username@realm' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'username@realm' ORDER BY id [sql] expand: SELECT groupname FROM usergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM usergroup WHERE username = 'username@realm' ORDER BY priority [sql] expand: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'MyGroup' ORDER BY id [sql] User found in group MyGroup [sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'MyGroup' ORDER BY id rlm_sql (sql): Released sql socket id: 1 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP # Executing group from file /etc/raddb/sites-enabled/default +- entering group PAP {...} [pap] login attempt with password "1234567" [pap] Using clear text password "1234567" [pap] User authenticated successfully ++[pap] returns ok # Executing section post-auth from file /etc/raddb/sites-enabled/default +- entering group post-auth {...} [sql] expand: %{User-Name} -> username@realm [sql] sql_set_user escaped user --> 'username@realm' [sql] expand: %{User-Password} -> 1234567 [sql] expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'username@realm', '1234567', 'Access-Accept', '2012-06-29 15:13:08') rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'username@realm', '1234567', 'Access-Accept', '2012-06-29 15:13:08') rlm_sql (sql): Reserving sql socket id: 0 rlm_sql (sql): Released sql socket id: 0 ++[sql] returns ok ++[exec] returns noop Sending Access-Accept of id 97 to 192.168.1.150 port 56916 Finished request 1. Going to the next request Waking up in 4.9 seconds. > Date: Fri, 29 Jun 2012 20:57:50 +0700

Subject: Re: FreeRADIUS SQl Ippool problem - From: list@fajar.net To: djtaz@iol.ie; freeradius-users@lists.freeradius.org

On Fri, Jun 29, 2012 at 8:46 PM, Taz Manian <djtazie@hotmail.com> wrote:

...

But the username isnt the problem, that authenticates properly and if i put in

You should fix that. There might be a compatibility code that allows it to work today, but the next versions might not have it.

...

I get no info back for the pools at all when i do a radiusd -X

And the debug log says ... what?

If you think you can solve it by yourself so that you don't need to send the output to this list, then don't bother asking here. Seriusly.

In any case, the debug log SHOULD tell you whether it runs sqlipool-related queries. If so, then try running those queries manually, see if the result is indeed what you expect them to be.

If it DOESN'T run sqlipool-related queries, then your problem is elsewhere. Probably because your setup DOESN'T read radgroupcheck by default (yes, it can be that way, if you configure it to do so). In that case moving Pool-Name to radcheck should be an easy way to test.

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

Did you put a call to sqlippool instance inside post-auth, like the wiki tells you to?

-- Fajar That was the problem - i had it in there but had commented it out at some stage because or an error that popped up and forgot to enable it again.Thanks for the help on this and i do apologise for annoying you, i know your sick of people like me but i did read up on it but one silly mistake caused me to try so many different things that i got myself mixed up with it all.Anyway , thanks again , its all working now and ill know to look out for that in the future :D Taz

On 06/29/2012 02:54 PM, Alan DeKok wrote:

Taz Manian wrote:

...

but one silly mistake caused me to try so many different things that i got myself mixed up with it all.

THAT is really the problem. Trying "many different things" is always a disaster. Follow the instructions *exactly*. Step by step.

Trying "many things" means you don't understand the problem. You're just randomly editing files "until it works". Well, it won't.

And do yourself a favor and put your configuration files under source control, make only minor incremental changes, if works commit it to the source repository. That way if things don't work it's trivial 2 second process to reset everything back to a known working state. You also will have a history of everything you changed and why as well as the ability to go back to any configuration at any point in time. If you don't know how to use source control, stop and learn it. -- John Dennis <jdennis@redhat.com> Looking to carve out IT costs? www.redhat.com/carveoutcosts/