Redundant SQL connection. Starting radiusd and Respond with Access-Accept if all databases do not work

classic Classic list List threaded Threaded
2 messages Options
| Threaded
Open this post in threaded view
|

Redundant SQL connection. Starting radiusd and Respond with Access-Accept if all databases do not work

Никита Борисенков
The task is to allow or prohibit calls to subscribers. If the subscriber
has valid status, then we send Access-Accept. If the subscriber calls
the emergency service, then we send Access-Accept. In other cases, we
send Access-Reject.

I configured two radius server, and two sql server. Replication
configured between sql servers.

I write accounting and authentication to the database.

I want that when one database is unavailable, data is written to another
database. But if both databases are not available, then we send
Access-Accept

Some questions:
* Is there a way to not duplicate config for modules sql1 and sql2
because they differ only in ip-address?
* Is there a way to start freeradius if one or all databases are down?
* What is the correct way to verify that sql1 and sql2 modules are fail
without checking "Calling-Station-Id-Is-Exists" for empty string?
* How can I simplify this configuration?


My config:

sql sql1 {
     driver   = "rlm_sql_mysql"
     dialect  = "mysql"

     server   = "localhost"
     port     = 3306
     login    = "radius"
     password = "test"

     query_timeout  = 5

     radius_db = "radius"

     read_clients = no

     # Table to keep radius client info
     client_table = "nas"

     # If you want both stop and start records logged to the
     # same SQL table, leave this as is.  If you want them in
     # different tables, put the start table in acct_table1
     # and stop table in acct_table2
     acct_table1 = "radacct"
     acct_table2 = "radacct"

     # Allow for storing data after authentication
     postauth_table = "radpostauth"

     # Tables containing 'check' items
     authcheck_table = "radcheck"
     groupcheck_table = "radgroupcheck"

     # Tables containing 'reply' items
     authreply_table = "radreply"
     groupreply_table = "radgroupreply"

     # Table to keep group info
     usergroup_table = "radusergroup"

     pool {
         start = ${thread[pool].start_servers}
         min   = ${thread[pool].min_spare_servers}
         max   = ${thread[pool].max_servers}
         spare = ${thread[pool].max_spare_servers}

         uses = 0
         retry_delay = 30
         lifetime = 0
         idle_timeout = 60
     }

     # This entry should be used for additional instances (sql foo {})
     # of the SQL module.
     group_attribute = "${.:instance}-SQL-Group"

     # This entry should be used for the default instance (sql {})
     # of the SQL module.
     #group_attribute = "SQL-Group"

     # Read database-specific queries
     # $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
     $INCLUDE ${modconfdir}/sql/main/${dialect}/queries.conf
}

sql sql2 {
     driver   = "rlm_sql_mysql"
     dialect  = "mysql"

     server   = "172.27.65.243"
     port     = 3306
     login    = "radius"
     password = "test"

     query_timeout  = 5

     radius_db = "radius"

     read_clients = no

     # Table to keep radius client info
     client_table = "nas"

     # If you want both stop and start records logged to the
     # same SQL table, leave this as is.  If you want them in
     # different tables, put the start table in acct_table1
     # and stop table in acct_table2
     acct_table1 = "radacct"
     acct_table2 = "radacct"

     # Allow for storing data after authentication
     postauth_table = "radpostauth"

     # Tables containing 'check' items
     authcheck_table = "radcheck"
     groupcheck_table = "radgroupcheck"

     # Tables containing 'reply' items
     authreply_table = "radreply"
     groupreply_table = "radgroupreply"

     # Table to keep group info
     usergroup_table = "radusergroup"

     pool {
         start = ${thread[pool].start_servers}
         min   = ${thread[pool].min_spare_servers}
         max   = ${thread[pool].max_servers}
         spare = ${thread[pool].max_spare_servers}

         uses = 0
         retry_delay = 30
         lifetime = 0
         idle_timeout = 60
     }

     # This entry should be used for additional instances (sql foo {})
     # of the SQL module.
     group_attribute = "${.:instance}-SQL-Group"

     # This entry should be used for the default instance (sql {})
     # of the SQL module.
     #group_attribute = "SQL-Group"

     # Read database-specific queries
     #$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf
     $INCLUDE ${modconfdir}/sql/main/${dialect}/queries.conf
}

# Create virtual module for SELECTs
instantiate {
     redundant sql1_or_sql2 {
         sql1
         sql2
     }
}

server ats_local {
     authorize {
         update control {
             Calling-Station-Id-Status    = "%{sql1_or_sql2:SELECT
gtdb_users.status FROM gtdb_users WHERE aon='%{Calling-Station-Id}'}"
             Called-Station-Id-Status     = "%{sql1_or_sql2:SELECT
gtdb_users.status FROM gtdb_users WHERE aon='%{Called-Station-Id}'}"

             Calling-Station-Id-Is-Exists = "%{sql1_or_sql2:SELECT
COUNT(gtdb_users.status) FROM gtdb_users WHERE aon='%{Calling-Station-Id}'}"
             Called-Station-Id-Is-Exists  = "%{sql1_or_sql2:SELECT
COUNT(gtdb_users.status) FROM gtdb_users WHERE aon='%{Called-Station-Id}'}"

             Called-Station-Id-Emergency  = "%{sql1_or_sql2:SELECT
COUNT(aon) FROM emergency_cid WHERE aon='%{Called-Station-Id}'}"
         }

         # These attributes in normal condition must contain the number
 >= 0 (because we use sql COUNT() )
         # If there is an empty string inside, then sql queries failed
for some reason and we return Access-Accept

         if (&control:Calling-Station-Id-Is-Exists == '' ||
&control:Called-Station-Id-Is-Exists == '') {
             update control {
                 Auth-Type := Accept
             }
         }
         else {
             if (control:Called-Station-Id-Emergency == '1') {
                 update control {
                     Auth-Type := Accept
                 }
             }

             # ...
             # bunch of elsif for Calling-Station-Id-Status and
Called-Station-Id-Status
             # ...

             else {
                 update control {
                     Auth-Type := Reject
                 }
             }
         }
     }


     authenticate {
     }

     preacct {
         acct_unique
     }

     $INCLUDE ${confdir}/ats_accounting.conf

     session {
     }

     post-auth {
         update reply {
             cisco-avpair = "h323-credit-time=86400"
         }

         if (&User-Name) {
             # this not work, because I can't put "ok" to virtual module
"sql1_or_sql2"
             # sql1_or_sql2
             redundant {
                 sql1
                 sql2
                 ok
             }
         }

         Post-Auth-Type REJECT {
             if (&User-Name) {
                 redundant {
                     sql1
                     sql2
                     ok
                 }
             }
         }

     }

     pre-proxy {
     }

     post-proxy {
     }
}
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Redundant SQL connection. Starting radiusd and Respond with Access-Accept if all databases do not work

Alan DeKok-2
On Nov 28, 2019, at 10:22 AM, Никита Борисенков <[hidden email]> wrote:

>
> The task is to allow or prohibit calls to subscribers. If the subscriber has valid status, then we send Access-Accept. If the subscriber calls the emergency service, then we send Access-Accept. In other cases, we send Access-Reject.
>
> I configured two radius server, and two sql server. Replication configured between sql servers.
>
> I write accounting and authentication to the database.
>
> I want that when one database is unavailable, data is written to another database. But if both databases are not available, then we send Access-Accept
>
> Some questions:
> * Is there a way to not duplicate config for modules sql1 and sql2 because they differ only in ip-address?

  See raddb/templates.conf  You can put all of the configuration *except* the "server" file into a "template" in that file.  Then configure two SQL modules:

sql sql1 {
        $template sql_template
        server = localhost
}

sql sql2 {
        $template sql_template
  server   = 172.27.65.243
}


> * Is there a way to start freeradius if one or all databases are down?

  See the pool config.  This is documented.  Set "start = 0", and "min = 0"

> * What is the correct way to verify that sql1 and sql2 modules are fail without checking "Calling-Station-Id-Is-Exists" for empty string?

  Use a "redundant" block.

> * How can I simplify this configuration?

  It seems OK to me.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html