How to execute TWO OR MORE Sql statement?

Duane Cox duanec at illicom.net
Thu Jul 27 16:58:50 CEST 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 at northpb.com>
To: "FreeRadius users mailing list" <freeradius-users at 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
>




More information about the Freeradius-Users mailing list