plpgsql freeradius authentication function
Hi I have a plpgsql function being called from freeradius to do authentication but i keep getting Access-Reject from radius although when i run the function without freeradius it works fine. Please find below the function and how i call it CREATE OR REPLACE FUNCTION try (your_name TEXT, tiger TEXT) RETURNS RECORD AS $$ DECLARE users_rec RECORD; BEGIN if tiger = '0000' then SELECT INTO users_rec * FROM aniradcheck WHERE username = your_name; else SELECT INTO users_rec * FROM radcheck WHERE username = your_name; end if; RETURN users_rec.username; END; $$ LANGUAGE plpgsql; SELECT id,username,attribute,op,value FROM try('7140944444','0000') as(id integer,username varchar,attribute varchar,op character,value varchar); Appreciate if you can help Thanks
In which statement do you implement this query? Date: Sat, 16 Oct 2010 11:49:36 -0400 Subject: plpgsql freeradius authentication function From: kakyea@gmail.com To: freeradius-users@lists.freeradius.org Hi I have a plpgsql function being called from freeradius to do authentication but i keep getting Access-Reject from radius although when i run the function without freeradius it works fine. Please find below the function and how i call it CREATE OR REPLACE FUNCTION try (your_name TEXT, tiger TEXT) RETURNS RECORD AS $$ DECLARE users_rec RECORD; BEGIN if tiger = '0000' then SELECT INTO users_rec * FROM aniradcheck WHERE username = your_name; else SELECT INTO users_rec * FROM radcheck WHERE username = your_name; end if; RETURN users_rec.username; END; $$ LANGUAGE plpgsql; SELECT id,username,attribute,op,value FROM try('7140944444','0000') as(id integer,username varchar,attribute varchar,op character,value varchar); Appreciate if you can help Thanks - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi I implemented this in the freeradius postgresql.conf file SELECT id,username,attribute,op,value FROM try('7140944444','0000') as(id integer,username varchar,attribute varchar,op character,value varchar); and implemented this function on the Postgres database server CREATE OR REPLACE FUNCTION try (your_name TEXT, tiger TEXT) RETURNS RECORD AS $$ DECLARE users_rec RECORD; BEGIN if tiger = '0000' then SELECT INTO users_rec * FROM aniradcheck WHERE username = your_name; else SELECT INTO users_rec * FROM radcheck WHERE username = your_name; end if; RETURN users_rec.username; END; $$ LANGUAGE plpgsql; I am generally trying to authenticate with a postgres function Thanks and your help very much appreciated. On Mon, Oct 18, 2010 at 2:20 AM, Santiago Balaguer García < santiagoawa@hotmail.com> wrote:
In which statement do you implement this query?
------------------------------ Date: Sat, 16 Oct 2010 11:49:36 -0400 Subject: plpgsql freeradius authentication function From: kakyea@gmail.com To: freeradius-users@lists.freeradius.org
Hi
I have a plpgsql function being called from freeradius to do authentication but i keep getting Access-Reject from radius although when i run the function without freeradius it works fine. Please find below the function and how i call it
CREATE OR REPLACE FUNCTION try (your_name TEXT, tiger TEXT) RETURNS RECORD AS $$ DECLARE users_rec RECORD;
BEGIN if tiger = '0000' then SELECT INTO users_rec * FROM aniradcheck WHERE username = your_name; else SELECT INTO users_rec * FROM radcheck WHERE username = your_name; end if; RETURN users_rec.username; END;
$$ LANGUAGE plpgsql;
SELECT id,username,attribute,op,value FROM try('7140944444','0000') as(id integer,username varchar,attribute varchar,op character,value varchar);
Appreciate if you can help Thanks
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Kafui Akyea wrote:
Hi
I implemented this in the freeradius postgresql.conf file SELECT id,username,attribute,op,value
That's the wrong order. See the default SELECT in raddb/sql/postgresql/dialup.conf.
I am generally trying to authenticate with a postgres function
Make sure it returns exactly the same information, in the same order, as the default queries. Alan DeKok.
Alan, Thanks a lot that worked Kafui On Mon, Oct 18, 2010 at 11:52 AM, Alan DeKok <aland@deployingradius.com>wrote:
Kafui Akyea wrote:
Hi
I implemented this in the freeradius postgresql.conf file SELECT id,username,attribute,op,value
That's the wrong order. See the default SELECT in raddb/sql/postgresql/dialup.conf.
I am generally trying to authenticate with a postgres function
Make sure it returns exactly the same information, in the same order, as the default queries.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi There is one more problem i am encountering although the function works. If i try to authenticate a user who is not valid i do not get an Access-Reject but rather i get RADIUS: Retransmit to (192.168.1.12:1812,1813) for id 1645/201 RADIUS: Retransmit to (192.168.1.12:1812,1813) for id 1645/201 RADIUS: Retransmit to (192.168.1.12:1812,1813) for id 1645/201 RADIUS: No response from (192.168.1.12:1812,1813) for id 1645/201 RADIUS/DECODE: No response from radius-server; parse response; FAIL RADIUS/DECODE: Case error(no response/ bad packet/ op decode);parse response; FAIL But that for a valid user works just fine I implemented this in the freeradius postgresql.conf file SELECT id,username,attribute,value,op FROM try('7140944444','0000') as(id integer,username varchar,attribute varchar,value varchar,op character); and implemented this function on the Postgres database server CREATE OR REPLACE FUNCTION try (your_name TEXT, tiger TEXT) RETURNS RECORD AS $$ DECLARE users_rec RECORD; BEGIN if tiger = '0000' then SELECT INTO users_rec * FROM aniradcheck WHERE username = your_name; else SELECT INTO users_rec * FROM radcheck WHERE username = your_name; end if; RETURN users_rec.username; END; $$ LANGUAGE plpgsql; Help will be very much appreciated On Mon, Oct 18, 2010 at 2:23 PM, Kafui Akyea <kakyea@gmail.com> wrote:
Alan,
Thanks a lot that worked
Kafui
On Mon, Oct 18, 2010 at 11:52 AM, Alan DeKok <aland@deployingradius.com>wrote:
Kafui Akyea wrote:
Hi
I implemented this in the freeradius postgresql.conf file SELECT id,username,attribute,op,value
That's the wrong order. See the default SELECT in raddb/sql/postgresql/dialup.conf.
I am generally trying to authenticate with a postgres function
Make sure it returns exactly the same information, in the same order, as the default queries.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi,
If i try to authenticate a user who is not valid i do not get an Access-Reject but rather i get RADIUS: Retransmit to ([1]192.168.1.12:1812,1813) for id 1645/201 RADIUS: Retransmit to ([2]192.168.1.12:1812,1813) for id 1645/201 RADIUS: Retransmit to ([3]192.168.1.12:1812,1813) for id 1645/201 RADIUS: No response from ([4]192.168.1.12:1812,1813) for id 1645/201 RADIUS/DECODE: No response from radius-server; parse response; FAIL RADIUS/DECODE: Case error(no response/ bad packet/ op decode);parse response; FAIL
its falling through to sending it off to another server/process - what is 192.168.1.12 - check your proxy.conf - and look at the full debug output of 'radiusd -X' (which you seem to be trying to summarise) - it'll tell you why its doing what it does alan
This is what the freeradius debug looks like when i try to authenticate a user who is not valid. I dont get an Access-Reject. But for valid users the function works fine. radius_xlat: 'SELECT id,username,attribute,value,op FROM tryagain('7140949870','0000') ????as (id integer,username varchar,attribute varchar,value varchar,op character)' rlm_sql (sql): Reserving sql socket id: 1 rlm_sql_postgresql: query: SELECT id,username,attribute,value,op FROM tryagain('7140949870','0000') ????as (id integer,username varchar,attribute varchar,value varchar,op character) rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: affected rows = rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row. rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 1 modcall[authorize]: module "sql" returns fail for request 3 modcall: leaving group authorize (returns fail) for request 3 Finished request 3 On Mon, Oct 18, 2010 at 5:41 PM, Alan Buxey <A.L.M.Buxey@lboro.ac.uk> wrote:
Hi,
If i try to authenticate a user who is not valid i do not get an Access-Reject but rather i get RADIUS: Retransmit to ([1]192.168.1.12:1812,1813) for id 1645/201 RADIUS: Retransmit to ([2]192.168.1.12:1812,1813) for id 1645/201 RADIUS: Retransmit to ([3]192.168.1.12:1812,1813) for id 1645/201 RADIUS: No response from ([4]192.168.1.12:1812,1813) for id 1645/201 RADIUS/DECODE: No response from radius-server; parse response; FAIL RADIUS/DECODE: Case error(no response/ bad packet/ op decode);parse response; FAIL
its falling through to sending it off to another server/process - what is 192.168.1.12 - check your proxy.conf - and look at the full debug output of 'radiusd -X' (which you seem to be trying to summarise) - it'll tell you why its doing what it does
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Kafui Akyea wrote:
This is what the freeradius debug looks like when i try to authenticate a user who is not valid. ... rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row. rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 1 modcall[authorize]: module "sql" returns fail for request 3
The answer is the same as last time: ensure that your function returns the same values as the default queries. If you're going to modify the default queries, it helps to understand what they do and how they work. Alan DeKok.
I have not changed the order of the default queries. Because for users in radcheck table it authenticates perfectly but for users who are not thats when i need to get an Access-Reject but i dont get anything at all. On Tue, Oct 19, 2010 at 1:18 AM, Alan DeKok <aland@deployingradius.com>wrote:
Kafui Akyea wrote:
This is what the freeradius debug looks like when i try to authenticate a user who is not valid. ... rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row. rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 1 modcall[authorize]: module "sql" returns fail for request 3
The answer is the same as last time: ensure that your function returns the same values as the default queries.
If you're going to modify the default queries, it helps to understand what they do and how they work.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi I am sending an aaa authorize request to from a cisco tcl ivr script to freeradius. After the authentication is done i want to retrieve the Username and Password values that are sent back to the cisco gateway to the IVR script. I am able to retrieve the other H323 attribute value pairs with infotag get aaa_avpair command but i am unable to retrieve the Username and Password from the returned values. Help will be very much appreciated. kafui On Tue, Oct 19, 2010 at 11:04 AM, Kafui Akyea <kakyea@gmail.com> wrote:
I have not changed the order of the default queries. Because for users in radcheck table it authenticates perfectly but for users who are not thats when i need to get an Access-Reject but i dont get anything at all.
On Tue, Oct 19, 2010 at 1:18 AM, Alan DeKok <aland@deployingradius.com>wrote:
Kafui Akyea wrote:
This is what the freeradius debug looks like when i try to authenticate a user who is not valid. ... rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row. rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 1 modcall[authorize]: module "sql" returns fail for request 3
The answer is the same as last time: ensure that your function returns the same values as the default queries.
If you're going to modify the default queries, it helps to understand what they do and how they work.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Kafui Akyea wrote:
I am sending an aaa authorize request to from a cisco tcl ivr script to freeradius.
That doesn't matter.
After the authentication is done i want to retrieve the Username and Password values that are sent back to the cisco gateway to the IVR script.
You've already said that.
I am able to retrieve the other H323 attribute value pairs with infotag get aaa_avpair command but i am unable to retrieve the Username and Password from the returned values.
The default configuration works.
Help will be very much appreciated.
Well... my suggestion is for you to not modify things you don't understand. There's nothing magic about the queries. If you have basic SQL knowledge, it should be pretty simple to get the stored procedures to return the same data as the default queries. In any case, this is an SQL issue, and has *nothing* to do with FreeRADIUS. Alan DeKok.
participants (4)
-
Alan Buxey -
Alan DeKok -
Kafui Akyea -
Santiago Balaguer García