王世彦 wrote:

Oh, my have another question about Freeradius.

I see when the Freeradius receives whatever a request, Freeradius will execute ONE SQL statement which is defined in the sql.conf.

My question is how to make Freeradius to execute TOW OR MORE SQL statement when Freeradius receives a request.

I am new to Freeradius and apologize for asking simple question.

Create a stored procedure in the database that contains all of the SQL queries necessary. Then call that stored procedure via sql.conf. This works fine with Postgres. Dan

Im using stored procs to do a series of queries even with IF THEN ELSE structires, with variables being the result of a query and being used in other queries. Works like a charm for me with mysql5 and freeradius 1.1.2 In the sql.conf I just use as query something like "call storedproc1(var1,var2,var3)" Regards, Jurgen -----Oorspronkelijk bericht----- Van: freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org [mailto:freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org] Namens Alan Lumb Verzonden: woensdag 26 juli 2006 14:08 Aan: FreeRadius users mailing list Onderwerp: Re: How to execute TWO OR MORE Sql statement?

Create a stored procedure in the database that contains all of the SQL queries necessary. Then call that stored procedure via sql.conf. This works fine with Postgres.

MySQL 5 supports stored proceedures and functions, however I know that mysql proceedures can cause problems as they can/will return multiple data sets that can cause some apps problems (not sure about freeradius and its support for mysql). I gave up on such a method and moved to rlm_perl. Now I have a perl handling script that performs as many queries as I need and can put lots of logic in the script, strip bits out of usernames and whatnot. Also it can look for certain flags in the database and if present send AV Pairs or VSA's to the NAS to configure them differently. my only issue has been getting rlm_perl to compile properly on suse x86_64, and sometimes have had to goto the extreme of recompiling perl and manually linking DynaLoader into rlm_perl.so So in answer to the original question I recommend rlm_perl if you have the time and patience to get around the setup problems. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Alan, Wouldn't it be possible to have freeradius call different sql sections depending on other attributes. For a loose example as in doc/Autz-Type ?? But I get this error... Failed to link to module 'rlm_sql1': rlm_sql1.so: cannot open shared object file: No such file or directory ----- Original Message ----- From: "Dan O'Neill" <freeradius@northpb.com> To: "FreeRadius users mailing list" <freeradius-users@lists.freeradius.org> Sent: Wednesday, July 26, 2006 9:43 AM Subject: Re: How to execute TWO OR MORE Sql statement?

Alan Lumb wrote:

...

...

Create a stored procedure in the database that contains all of the SQL queries necessary. Then call that stored procedure via sql.conf. This works fine with Postgres.

MySQL 5 supports stored proceedures and functions, however I know that mysql proceedures can cause problems as they can/will return multiple data sets that can cause some apps problems (not sure about freeradius and its support for mysql).

Stored procedures have to be designed to return one or more rows.

Here's an example. I have heartbeat monitor running against Freeradius to monitor authorization on a regular basis. I do not want these heartbeat authentications written into the 'radpostauth' table. The username 'TylerDurden' is filtered out prior to inserting the postauth record.

Here is the Postgres stored procedure in the RADIUS database:

CREATE FUNCTION filtered_insert_radpostauth(text, text, text) RETURNS integer AS $_$ DECLARE _new_filtered_insert_radpostauth_id integer; BEGIN IF ( $1 = 'TylerDurden') THEN RETURN 0; END IF;

INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( $1, $2, $3, NOW() ); IF FOUND THEN SELECT INTO _new_filtered_insert_radpostauth_id MAX(id) FROM radpostauth; RETURN _new_filtered_insert_radpostauth_id; END IF;

return -1; END $_$ LANGUAGE plpgsql;

ALTER FUNCTION public.filtered_insert_radpostauth(text, text, text) OWNER TO postgres;

and the line in postgresql.conf looks like (it's wrapped here):

postauth_query = "SELECT filtered_insert_radpostauth( '%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}' )"

Everyone but TylerDurden gets logged because he doesn't really exist anyway.

Good luck!

Dan

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

Thanks for your reply. I now know how to do it. PS: *b2bua is short for Asterisk+b2bua. It is a prepaid billing solution for VoIP system. -----Original Message----- From: Stefan Winter [mailto:stefan.winter@restena.lu] Sent: 2006年7月26日 1:03 To: FreeRadius users mailing list Subject: Re: How to reply Session-Timeout without password Hi,

My question is how to make the radius server accept the request which do not contain the password and reply the Session-Timeout to the *b2bua?

I have not the faintest idea about *b2bua (WTF?) but if you just want to accept everyone without any checks for your *b2bua NASes, you can achieve it in the "users" file with NAS-IP-Address == your-b2bua-ns, Auth-Type := Accept Session-Timeout := whatever Do keep in mind that everyone who is authenticating via this IP address is *always* *accepted* Greetings, Stefan Winter -- Stefan WINTER Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche - Ingénieur de recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg

Hi!

Now I am a little confused.

For user 005001, I not only want to check the Session-Timeout for accounting, but also want to check its password for authorization.

Before you tell me the "auth by IP address" method, my conf is like this:

005001 Auth-Type := Digest, Password == "005001"

Now my question is: How can I make radius server to use Password for normal authorization and then use the "auth by IP address" method for *b2bua request?

You can use Fall-Through: first the entries with the NAS-IP-Address, but adding a Fall-Through = Yes, and later your other, sepcial, user. Altogether it will look like that: NAS-IP-Address == your-b2bua-ns, Auth-Type := Accept         Session-Timeout := whatever, Fall-Through = Yes 005001 Auth-Type := Digest, Password == "005001" All users whose user name is *not* 005001 are caught with the first expression and not with the second. User 005001 is first caught with the first expression, but later overridden with the second one and thus needs to authenticate. Greetings, Stefan Winter -- Stefan WINTER Stiftung RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche Ingenieur Forschung & Entwicklung 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg E-Mail: stefan.winter@restena.lu     Tel.:    +352 424409-1 http://www.restena.lu               Fax:      +352 422473