I found a bug in match_tariff that prevented it to work
when groups were enabled. The fix is in the cvs.
----- Original Message -----
From: "Kompnet" <kompnet@xxxxxxxxx>
Sent: Thursday, August 26, 2004 5:11 PM
Subject: Error in sqlbilling?
> Michal!
> Help, plees.
> I need to have 2 groups of users:
> 1-st - my GW 'komp-net' - prepaid cards platform with 0 tax,
> 2-nd - clients GW's 'trade_formula' with notnull tariff.
> It seems there is an error in functions or in queries, which analise groups of
> users.
> When I've configured 'komp-net' as user 1 grp 1 and 'trade_formula' as user 2
> grp 2 I couldn't established a call nor from 'komp-net' GW, nor from
> 'trade_formula' GW.
> When I've configured 'trade_formula' as user 2 grp 1 and 'komp-net' as user 1
> no grp I could establsh a call from 'komp-net' GW and coldn't from
> 'trade_formula' GW.
> For borth configurations I've chancged tariff table according to grp
> configuration.
> I've found one mistake in c_voiptariff.sql:
> active BOOLEAN NO NULL DEFAULT TRUE in 23-th string.
> Maybe there are more mistakes in other files?
> There are fragments from postges's log:
> 1) 'trade_formula' as user 2 grp 1 and 'komp-net' as user 1 no grp
> Call from 'komp-net' GW:
>
> rad_recv: Access-Request packet from host 192.168.110.223:56396, id=95,
> length=262
> User-Name = "komp-net"
> NAS-IP-Address = 192.168.110.223
> NAS-Identifier = "KompnetGK-2.0"
> NAS-Port-Type = Virtual
> Service-Type = Login-User
> Framed-IP-Address = 192.168.110.224
> Calling-Station-Id = "904436"
> Called-Station-Id = "0022#88632347611"
> h323-conf-id = "h323-conf-id=786C6485 F67A11D8 85FAE765 2C7323DC"
> h323-call-origin = "h323-call-origin=originate"
> h323-call-type = "h323-call-type=VoIP"
> h323-gw-id = "h323-gw-id=KompnetGK-2.0"
> User-Password = "komp-net"
> Processing the authorize section of radiusd.conf
> modcall: entering group authorize for request 121
> modcall[authorize]: module "preprocess" returns ok for request 121
> radius_xlat:
> '/usr/local/var/log/radius/radacct/192.168.110.223/auth-detail-20040826'
> rlm_detail:
> /usr/local/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d
> expands to
> /usr/local/var/log/radius/radacct/192.168.110.223/auth-detail-20040826
> modcall[authorize]: module "auth_log" returns ok for request 121
> modcall[authorize]: module "chap" returns noop for request 121
> rlm_realm: No '@' in User-Name = "komp-net", looking up realm NULL
> rlm_realm: No such realm "NULL"
> modcall[authorize]: module "suffix" returns noop for request 121
> radius_xlat: 'komp-net'
> rlm_sql (sql): sql_set_user escaped user --> 'komp-net'
> radius_xlat: 'SELECT id, 'komp-net', attrname, attrvalue, attrop FROM
> ??radius_get_check_attrs(
> 'komp-net', NULLIF('192.168.110.224', '')::INET, ???CASE ????WHEN
> '0022#88632347611' = '' THEN T
> RUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' = 'Call-Check' THEN TRUE
> ELSE FALSE END, ??
> ?NULLIF('904436',''), NULLIF('0022#88632347611',''), ???'' ???)'
> rlm_sql (sql): Reserving sql socket id: 8
> rlm_sql_postgresql: query: SELECT id, 'komp-net', attrname, attrvalue, attrop
> FROM ??radius_get_
> check_attrs('komp-net', NULLIF('192.168.110.224', '')::INET, ???CASE ????WHEN
> '0022#88632347611'
> = '' THEN TRUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' =
> 'Call-Check' THEN TRUE ELSE F
> ALSE END, ???NULLIF('904436',''), NULLIF('0022#88632347611',''), ???'' ???)
> rlm_sql_postgresql: Status: PGRES_TUPLES_OK
> rlm_sql_postgresql: affected rows =
> radius_xlat: ''
> radius_xlat: 'SELECT id, 'komp-net', attrname, attrvalue, attrop FROM
> ??radius_get_reply_attrs(
> 'komp-net', NULLIF('192.168.110.224', '')::INET, ???CASE ????WHEN
> '0022#88632347611' = '' THEN T
> RUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' = 'Call-Check' THEN TRUE
> ELSE FALSE END, ??
> ?NULLIF('904436',''), NULLIF('0022#88632347611',''), ???'' ???)'
> rlm_sql_postgresql: query: SELECT id, 'komp-net', attrname, attrvalue, attrop
> FROM ??radius_get_
> reply_attrs('komp-net', NULLIF('192.168.110.224', '')::INET, ???CASE ????WHEN
> '0022#88632347611'
> = '' THEN TRUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' =
> 'Call-Check' THEN TRUE ELSE F
> ALSE END, ???NULLIF('904436',''), NULLIF('0022#88632347611',''), ???'' ???)
> rlm_sql_postgresql: Status: PGRES_TUPLES_OK
> rlm_sql_postgresql: affected rows =
> radius_xlat: ''
> rlm_sql (sql): Released sql socket id: 8
> modcall[authorize]: module "sql" returns ok for request 121
> modcall: group authorize returns ok for request 121
> auth: type Local
> auth: user supplied User-Password matches local User-Password
> Login OK: [komp-net/komp-net] (from client gnugk port 0 cli 904436)
> Processing the post-auth section of radiusd.conf
> modcall: entering group post-auth for request 121
> radius_xlat:
> '/usr/local/var/log/radius/radacct/192.168.110.223/reply-detail-20040826'
> rlm_detail:
> /usr/local/var/log/radius/radacct/%{Client-IP-Address}/reply-detail-%Y%m%d
> expands t
> o /usr/local/var/log/radius/radacct/192.168.110.223/reply-detail-20040826
> modcall[post-auth]: module "reply_log" returns ok for request 121
> modcall: group post-auth returns ok for request 121
> Sending Access-Accept of id 95 to 192.168.110.223:56396
>
> 2) 'trade_formula' as user 2 grp 1 and 'komp-net' as user 1 no grp
> Call from 'trade_formula' GW:
>
> rad_recv: Access-Request packet from host 192.168.110.223:56396, id=96,
> length=263
> User-Name = "trade_formula"
> NAS-IP-Address = 192.168.110.223
> NAS-Identifier = "KompnetGK-2.0"
> NAS-Port-Type = Virtual
> Service-Type = Login-User
> Framed-IP-Address = 192.168.110.230
> Calling-Station-Id = "001"
> Called-Station-Id = "0022#88632347611"
> h323-conf-id = "h323-conf-id=2B21DD2 13001000 3F140080 C826BA70"
> h323-call-origin = "h323-call-origin=originate"
> h323-call-type = "h323-call-type=VoIP"
> h323-gw-id = "h323-gw-id=KompnetGK-2.0"
> User-Password = "trade_formula"
> Processing the authorize section of radiusd.conf
> modcall: entering group authorize for request 124
> modcall[authorize]: module "preprocess" returns ok for request 124
> radius_xlat:
> '/usr/local/var/log/radius/radacct/192.168.110.223/auth-detail-20040826'
> rlm_detail:
> /usr/local/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d
> expands to
> /usr/local/var/log/radius/radacct/192.168.110.223/auth-detail-20040826
> modcall[authorize]: module "auth_log" returns ok for request 124
> modcall[authorize]: module "chap" returns noop for request 124
> rlm_realm: No '@' in User-Name = "trade_formula", looking up realm NULL
> rlm_realm: No such realm "NULL"
> modcall[authorize]: module "suffix" returns noop for request 124
> radius_xlat: 'trade_formula'
> rlm_sql (sql): sql_set_user escaped user --> 'trade_formula'
> radius_xlat: 'SELECT id, 'trade_formula', attrname, attrvalue, attrop FROM
> ??radius_get_check_a
> ttrs('trade_formula', NULLIF('192.168.110.230', '')::INET, ???CASE ????WHEN
> '0022#88632347611' =
> '' THEN TRUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' = 'Call-Check'
> THEN TRUE ELSE FAL
> SE END, ???NULLIF('001',''), NULLIF('0022#88632347611',''), ???'' ???)'
> rlm_sql (sql): Reserving sql socket id: 5
> rlm_sql_postgresql: query: SELECT id, 'trade_formula', attrname, attrvalue,
> attrop FROM ??radius
> _get_check_attrs('trade_formula', NULLIF('192.168.110.230', '')::INET, ???CASE
> ????WHEN '0022#88
> 632347611' = '' THEN TRUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' =
> 'Call-Check' THEN T
> RUE ELSE FALSE END, ???NULLIF('001',''), NULLIF('0022#88632347611',''), ???''
> ???)
> WARNING: Error occurred while executing PL/pgSQL function match_tariff
> WARNING: line 33 at select into variables
> rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
> rlm_sql_postgresql: affected rows =
> rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
> SQL_DOWN
> rlm_sql (sql): Attempting to connect rlm_sql_postgresql #5
> rlm_sql (sql): Connected new DB handle, #5
> rlm_sql_postgresql: query: SELECT id, 'trade_formula', attrname, attrvalue,
> attrop FROM ??radius
> _get_check_attrs('trade_formula', NULLIF('192.168.110.230', '')::INET, ???CASE
> ????WHEN '0022#88
> 632347611' = '' THEN TRUE ????ELSE FALSE ???END, ???CASE WHEN 'Login-User' =
> 'Call-Check' THEN T
> RUE ELSE FALSE END, ???NULLIF('001',''), NULLIF('0022#88632347611',''), ???''
> ???)
> WARNING: Error occurred while executing PL/pgSQL function match_tariff
> WARNING: line 33 at select into variables
> rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
> rlm_sql_postgresql: affected rows =
> rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
> SQL_DOWN
> rlm_sql (sql): failed after re-connect
> rlm_sql_getvpdata: database query error
> rlm_sql (sql): SQL query error; rejecting user
> rlm_sql (sql): Released sql socket id: 5
> modcall[authorize]: module "sql" returns fail for request 124
> modcall: group authorize returns fail for request 124
> There was no response configured: rejecting request 124
> Server rejecting request 124.
> Finished request 124
> Going to the next request
>
> I can't give you more unformation because I don't know how to trace queries
> with pgsql functions.
> Help, pls. I mark time on one place three days.
> --
> Best regards,
> Igor Prokhorov
-------------------------------------------------------
SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media
100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33
Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift.
http://www.shop4tech.com/z/Inkjet_Cartridges/9_108_r285
_______________________________________________________
List: Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549
Homepage: http://www.gnugk.org/
