Again: Major impact on authentication!

[Peter Nixon]

On Tue 06 Feb 2007 18:40, Guilherme Franco wrote:
> Hello Mr. Nixon!
>
> No, the radius server and the DB server are connected in the same
> switch, using gigabit UTP, resulting in 0.090ms RTT.
>
> The proxy server is also directly connected to this switch.
>
> I've been using CVS builds mainly because the rlm_sqlippool was under
> development with constant updates done by you. The last one I've used
> was freeradius-server-snapshot-20070120.tar.bz2 but the same behaviour
> appeared, then I've switched to 1.1.4.

OK. Well we have added a few more things this week. The last commit was just 
over an hour ago for:
http://bugs.freeradius.org/show_bug.cgi?id=414

> Humm Oracle support would be great! But I remember that in the
> previous builds, I had to remove the BEGIN from allocate-begin in
> rlm_sqlippool.c and recompile it because Oracle understands BEGIN as a
> function/procedure/transaction start and the next steps taken by
> rlm_sqlippool didn't fit in the correct structure (missing END; and
> other statements). That way, no errors raised but sqlippool wouldn't
> recognize the IP queried by the SELECT then.

Oh. I had forgotten this issue with oracle.

There are 2 different issues that may be causing this.

1) The bug in sqlippool that relied on a bug in the postgresql driver both of 
which are now fixed.

2) Transactions may either be "BEGIN" and "END" or the newer SQL99 standard 
of "START TRANSACTION" and "COMMIT". Postgresql (and it seems MySQL from 
reading the docs) support both forms and for some reason sqlippool starts 
the transaction with BEGIN and ends it with COMMIT which is actually a 
mixture of transaction dialects.

Can you please confirm whether on not Oracle supports the SQL99 syntax? If so 
I will change all the "BEGIN"s to "START TRANSACTION". If it does not I will 
do the opposite.

> Mr. Peter, also importantly, as we have both static and dynamic
> ippools, two instances of sqlippool was running, namely sqlippool
> DYNAMIC and sqlippool STATIC, called in this order by radiusd.
>
> The allocate-find was not working correctly, not giving the same
> static IPs to the user every time, so I've decided to remove one
> sqlippool instance and I've created the following function:
>
>
> CREATE OR REPLACE FUNCTION FOOBAR(user text, pool text, nas text)
> RETURNS inet AS $$
> declare
> ip_temp inet := null;
> BEGIN
>     if pool = 'DYNAMIC' then
> select framedipaddress into ip_temp from radippool where expiry_time <
> 'now'::timestamp(0) and pool_name = pool ORDER BY (username <> user),
> (callingstationid <> nas), expiry_time LIMIT 1 FOR UPDATE;
> return ip_temp;
>     end if;
>
>     if pool <> 'DYNAMIC' then
>      select framedipaddress into ip_temp from radippool where username
> = user and pool_name = pool;
>
>      if ip_temp is not null then
>      return ip_temp;
>         end if;
>
>         if ip_temp is null then
> select framedipaddress into ip_temp from radippool where expiry_time <
> 'now'::timestamp(0) and username = '' and pool_name = pool LIMIT 1 FOR
> UPDATE;
> UPDATE radippool SET username = user where framedipaddress = ip_temp;
> return ip_temp;
>         end if;
>
>     END IF;
>
> END;
> $$ LANGUAGE plpgsql;
>
> That way, allocate-find became just allocate-find = "select
> FOOBAR('%{User-Name}','%{check:Pool-Name}','%{Calling-Station-Id}')"

ok.

> No fail-over would occur anymore, the function works in less than
> 50ms, the static ip of the user is permanently written in the DB so I
> think it became better this way, at least for me.

The patches I committed today and yesterday improved the exit codes which 
should make failover more flexible.

> This setup was running fine since October 2006, until now that things
> started to freak out.

I am sorry to hear that. I really am not sure what the problem could be...

Regards
-- 

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070206/502a568a/attachment.pgp>