Using two tables (postgreSql) to validate users

[Claudiu Filip]

Hi Daniel,


   It is very easy to use as many tables you need.
   You can have in config:

   authorize_check_query = "SELECT * FROM pl_AUTHORIZE_CHECK('%{SQL-User-Name}', '%{User-Password}', '%{Client-IP-Address}')"
   
   pl_AUTHORIZE_CHECK will be a stored procedure on the postgresql backend.
   For example, I used something like this:
   
   sql> CREATE TYPE radius_check_pairs AS (id integer, username text, attrname text, attrval text, attrop text);
   sql> CREATE OR REPLACE FUNCTION pl_AUTHORIZE_CHECK (text, text, text) RETURNS SETOF radius_check_pairs AS $$
$user = $_[0]; $pass = $_[1]; $nasip = $_[2];
my $rv = spi_exec_query("SELECT status FROM accounts WHERE username = '$user' AND password='$pass';", 1);
$status = $rv->{rows}[0]{status};
if ($rv->{processed} < 1) {
        elog(NOTICE, "AUTHCHECK: User $user / $pass NOT FOUND");
        return [ { id => 0, username => $_[0], attrname => 'Auth-Type', attrval=> 'Reject', attrop => ':=' } ];
}
if ($status != '1') {
        elog(NOTICE, "AUTHCHECK: User $user not active");
        push @$reply,  {
                        id => 0, username => $_[0], attrname => 'Auth-Type', attrval => 'Reject', attrop => ':='
        };
        push @$reply,  {
                        id => 1, username => $_[0], attrname => 'Reply-Message',  attrval => 'Acccount suspended!', attrop => ':='
        };
        return($reply);
}
elog(NOTICE, "AUTHCHECK: User $user - login ok");
return [ { id => 0, username => $_[0], attrname => 'Auth-Type', attrval => 'Accept', attrop => ':=' } ];
$$ LANGUAGE plperl;


   The advantages of this scenario.. You can have anything you want in
   this procedure, including cpan modules :> and you can still run the
   radius server on your favorite pentium II with load average 0.


Claudiu Filip
@: claudiu at globtel.ro
Http://www.globtel.ro
T:+40344880100
F:+40344880113

> Hi again...
> I have a doubt: Is it possible to use two tables to check the users? I
> need to do something like this... Freeradius checks if the user is valid
> on the table 1, if it returns true the user is validated, but if the
> return is false, freeradius checks the table 2, trying to validate the
> user once again.