Re: How to execute TWO OR MORE Sql statement?

27 Jul 2006

      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

Re: How to execute TWO OR MORE Sql statement?

Duane Cox