strangeness with 3rd sql pool and unlang in policy.d/<filename>

7 Feb 2018

      Hi,

can someone advise what i am doing wrong here:

<background>
i have 3 different sql servers, hence 3 sql pools
sql_auth		-> db for auth and accounting
sql_ippool		-> db for ip pools
sql_policyLookup	-> db for

the sql_auth and sql_ippool work without any issues in the policy.d/filename

the sql_policyLoopup behave strangely,
</background>

effectively this is what i want to do

apply-sv-policy {
	update control {
		&Tmp-String-0 := "%{sql_policyLookup:SELECT AttrValue \
                 FROM SVRadiusPackageMap \
                 LEFT JOIN UserConfiguredPackages \
                 ON UserConfiguredPackages.PackageID = PackageName \
                 WHERE UserName = '%{SQL-User-Name}' AND ServiceID = 'lte' AND \	
		AttrName = 'IS-SV-Policy-Profile'}"
         }
}

this is what i have to do

apply-sv-policy {
         if ( "{sql_policyLookup:select concat('%{SQL-User-Name}', '1')" ) {
                 update control {
                     &Tmp-String-0 := "%{sql_policyLookup:SELECT AttrValue \
                 FROM SVRadiusPackageMap \
                 LEFT JOIN UserConfiguredPackages \
                 ON UserConfiguredPackages.PackageID = PackageName \
                 WHERE UserName = '%{SQL-User-Name}' AND ServiceID = 'lte' AND \
                 AttrName = 'IS-SV-Policy-Profile'}"

                 }
         }

         update control {
                 &Tmp-String-0 := "%{sql_policyLookup:SELECT AttrValue \
                 FROM SVRadiusPackageMap \
                 LEFT JOIN UserConfiguredPackages \
                 ON UserConfiguredPackages.PackageID = PackageName \
                 WHERE UserName = '%{SQL-User-Name}' AND ServiceID = 'lte' AND \
                 AttrName = 'IS-SV-Policy-Profile'}"

         }
}

i have had to duplicate the sql query as it only produces a result the 3rd time.

(5) # Executing section preacct from file 
/usr/local/etc/raddb/sites-enabled/copy-to-sde
(5)   preacct {
(5)     [preprocess] = ok
(5)     policy apply-sv-policy {
(5)       if ( "{sql_policyLookup:select concat('%{SQL-User-Name}', '1')" ) {
(5)       EXPAND {sql_policyLookup:select concat('%{SQL-User-Name}', '1')
(5)          --> {sql_policyLookup:select concat('', '1')

<me>
as you can see, the SQL-User-Name is not expanded.
</me>

(5)       if ( "{sql_policyLookup:select concat('%{SQL-User-Name}', '1')" )  -> TRUE
(5)       if ( "{sql_policyLookup:select concat('%{SQL-User-Name}', '1')" )  {
(5)         update control {
(5)           EXPAND %{User-Name}
(5)              --> 27747638935@lte.broadband.is
(5)           SQL-User-Name set to '27747638935@lte.broadband.is'
rlm_sql (sql_policyLookup): Reserved connection (2)
(5)           Executing select query: SELECT AttrValue FROM SVRadiusPackageMap 
               LEFT JOIN UserConfiguredPackages ON 
UserConfiguredPackages.PackageID = PackageName WHERE UserName = '' AND ServiceID 
= 'lte' AND AttrName = 'IS-SV-Policy-Profile'

<me>
as you can see, the SQL-User-Name is not expanded here either.
</me>

(5)           SQL query returned no results
rlm_sql (sql_policyLookup): Released connection (2)
Need 4 more connections to reach 10 spares
rlm_sql (sql_policyLookup): Opening additional connection (6), 1 of 26 pending 
slots used
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql_mysql: Connected to database 'isdsl' on greatrepublic.dial-up.net via 
TCP/IP, server version 5.1.56-log, protocol version 10
(5)           EXPAND %{sql_policyLookup:SELECT AttrValue                FROM 
SVRadiusPackageMap                 LEFT JOIN UserConfiguredPackages 
   ON UserConfiguredPackages.PackageID = PackageName               WHERE UserNam
e = '%{SQL-User-Name}' AND ServiceID = 'lte' AND                AttrName = 
'IS-SV-Policy-Profile'}
(5)              -->
(5)           &Tmp-String-0 :=
(5)         } # update control = noop
(5)       } # if ( "{sql_policyLookup:select concat('%{SQL-User-Name}', '1')" ) 
= noop

<me>
duplicated block
</me>

(5)       update control {
(5)         EXPAND %{User-Name}
(5)            --> 27747638935@lte.broadband.is
(5)         SQL-User-Name set to '27747638935@lte.broadband.is'
rlm_sql (sql_policyLookup): Reserved connection (3)
(5)         Executing select query: SELECT AttrValue            FROM 
SVRadiusPackageMap                 LEFT JOIN UserConfiguredPackages 
   ON UserConfiguredPackages.PackageID = PackageName               WHERE 
UserName = '277
47638935@lte.broadband.is' AND ServiceID = 'lte' AND            AttrName = 
'IS-SV-Policy-Profile'

<me>
as you can see, the SQL-User-Name is expanded here
</me>

(5)         SQL query returned no results
rlm_sql (sql_policyLookup): Released connection (3)
(5)         EXPAND %{sql_policyLookup:SELECT AttrValue          FROM 
SVRadiusPackageMap                 LEFT JOIN UserConfiguredPackages 
   ON UserConfiguredPackages.PackageID = PackageName               WHERE 
UserName = '%{S
QL-User-Name}' AND ServiceID = 'lte' AND                AttrName = 
'IS-SV-Policy-Profile'}
(5)            -->
(5)         &Tmp-String-0 :=

<me>
this use is not configure, so blank expected.
</me>

(5)       } # update control = noop
(5)       if ( &Tmp-String-0 ) {
(5)       if ( &Tmp-String-0 )  -> FALSE
(5)     } # policy apply-sv-policy = noop
(5)     update control {
(5)       Proxy-To-Realm := sandvinesde
(5)     } # update control = noop

Regards
Vikash Badal
Senior Technical Systems Engineer
T +27 11 5750825
C +27 83 6774915

www.is.co.za
Internet Solutions is a division of Dimension Data

Disclaimer: https://www.is.co.za/legal/#e-mail-confidentiality-notice-and-disclaimer

strangeness with 3rd sql pool and unlang in policy.d/<filename>

Vikash Badal (IS)