"Fossies" - the Fresh Open Source Software Archive

Member "freeradius-server-3.0.23/raddb/mods-config/sql/main/postgresql/queries.conf" (10 Jun 2021, 26134 Bytes) of package /linux/misc/freeradius-server-3.0.23.tar.bz2:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Generic config files source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. See also the latest Fossies "Diffs" side-by-side code changes report for "queries.conf": 3.0.22_vs_3.0.23.

    1 # -*- text -*-
    2 #
    3 #  main/postgresql/queries.conf -- PostgreSQL configuration for default schema (schema.sql)
    4 #
    5 #  $Id: 5be7fcaefa774920040f5893f42e5c961ead2b38 $
    6 
    7 # Use the driver specific SQL escape method.
    8 #
    9 # If you enable this configuration item, the "safe_characters"
   10 # configuration is ignored.  FreeRADIUS then uses the PostgreSQL escape
   11 # functions to escape input strings.  The only downside to making this
   12 # change is that the PostgreSQL escaping method is not the same the one
   13 # used by FreeRADIUS.  So characters which are NOT in the
   14 # "safe_characters" list will now be stored differently in the database.
   15 #
   16 #auto_escape = yes
   17 
   18 # Safe characters list for sql queries. Everything else is replaced
   19 # with their mime-encoded equivalents.
   20 # The default list should be ok
   21 # Using 'auto_escape' is preferred
   22 # safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
   23 
   24 #######################################################################
   25 #  Query config:  Username
   26 #######################################################################
   27 # This is the username that will get substituted, escaped, and added
   28 # as attribute 'SQL-User-Name'.  '%{SQL-User-Name}' should be used
   29 # below everywhere a username substitution is needed so you you can
   30 # be sure the username passed from the client is escaped properly.
   31 #
   32 # Uncomment the next line, if you want the sql_user_name to mean:
   33 #
   34 #    Use Stripped-User-Name, if it's there.
   35 #    Else use User-Name, if it's there,
   36 #    Else use hard-coded string "none" as the user name.
   37 #
   38 #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
   39 
   40 sql_user_name = "%{User-Name}"
   41 
   42 #######################################################################
   43 #  Query config:  Event-Timestamp
   44 #######################################################################
   45 # event_timestamp_epoch is the basis for the time inserted into
   46 # accounting records. Typically this will be the Event-Timestamp of the
   47 # accounting request, which is usually provided by a NAS.
   48 #
   49 # Uncomment the next line, if you want the timestamp to be based on the
   50 # request reception time recorded by this server, for example if you
   51 # distrust the provided Event-Timestamp.
   52 #event_timestamp_epoch = "%l"
   53 
   54 event_timestamp_epoch = "%{%{integer:Event-Timestamp}:-%l}"
   55 
   56 # event_timestamp is the SQL snippet for converting an epoch timestamp
   57 # to an SQL date.
   58 
   59 event_timestamp = "TO_TIMESTAMP(${event_timestamp_epoch})"
   60 
   61 #######################################################################
   62 #  Query config:  Class attribute
   63 #######################################################################
   64 #
   65 #  3.0.22 and later have a "class" column in the accounting table.
   66 #
   67 #  However, we do NOT want to break existing configurations by adding
   68 #  the Class attribute to the default queries.  If we did that, then
   69 #  systems using newer versions of the server would fail, because
   70 #  there is no "class" column in their accounting tables.
   71 #
   72 #  The solution to that is the following "class" subsection.  If your
   73 #  database has a "class" column for the various tables, then you can
   74 #  uncomment the configuration items here.  The queries below will
   75 #  then automatically insert the Class attribute into radacct,
   76 #  radpostauth, etc.
   77 #
   78 class {
   79     #
   80     #  Delete the '#' character from each of the configuration
   81     #  items in this section.  This change puts the Class
   82     #  attribute into the various tables.  Leave the double-quoted
   83     #  string there, as the value for the configuration item.
   84     #
   85     #  See also policy.d/accounting, and the "insert_acct_class"
   86     #  policy.  You will need to list (or uncomment)
   87     #  "insert_acct_class" in the "post-auth" section in order to
   88     #  create a Class attribute.
   89     #
   90     column_name =   # ", Class"
   91     packet_xlat =   # ", '%{Class}'"
   92     reply_xlat =    # ", '%{Reply:Class}'"
   93 }
   94 
   95 #######################################################################
   96 #  Default profile
   97 #######################################################################
   98 # This is the default profile. It is found in SQL by group membership.
   99 # That means that this profile must be a member of at least one group
  100 # which will contain the corresponding check and reply items.
  101 # This profile will be queried in the authorize section for every user.
  102 # The point is to assign all users a default profile without having to
  103 # manually add each one to a group that will contain the profile.
  104 # The SQL module will also honor the User-Profile attribute. This
  105 # attribute can be set anywhere in the authorize section (ie the users
  106 # file). It is found exactly as the default profile is found.
  107 # If it is set then it will *overwrite* the default profile setting.
  108 # The idea is to select profiles based on checks on the incoming
  109 # packets, not on user group membership. For example:
  110 # -- users file --
  111 # DEFAULT   Service-Type == Outbound-User, User-Profile := "outbound"
  112 # DEFAULT   Service-Type == Framed-User, User-Profile := "framed"
  113 #
  114 # By default the default_user_profile is not set
  115 #
  116 # default_user_profile = "DEFAULT"
  117 
  118 #######################################################################
  119 #  Open Query
  120 #######################################################################
  121 # This query is run whenever a new connection is opened.
  122 # It is commented out by default.
  123 #
  124 # If you have issues with connections hanging for too long, uncomment
  125 # the next line, and set the timeout in milliseconds.  As a general
  126 # rule, if the queries take longer than a second, something is wrong
  127 # with the database.
  128 #open_query = "set statement_timeout to 1000"
  129 
  130 #######################################################################
  131 #  NAS Query
  132 #######################################################################
  133 #  This query retrieves the radius clients
  134 #
  135 #  0. Row ID (currently unused)
  136 #  1. Name (or IP address)
  137 #  2. Shortname
  138 #  3. Type
  139 #  4. Secret
  140 #  5. Server
  141 #######################################################################
  142 
  143 client_query = "\
  144     SELECT id, nasname, shortname, type, secret, server \
  145     FROM ${client_table}"
  146 
  147 #######################################################################
  148 #  Authorization Queries
  149 #######################################################################
  150 #  These queries compare the check items for the user
  151 #  in ${authcheck_table} and setup the reply items in
  152 #  ${authreply_table}.  You can use any query/tables
  153 #  you want, but the return data for each row MUST
  154 #  be in the  following order:
  155 #
  156 #  0. Row ID (currently unused)
  157 #  1. UserName/GroupName
  158 #  2. Item Attr Name
  159 #  3. Item Attr Value
  160 #  4. Item Attr Operation
  161 #######################################################################
  162 
  163 #
  164 #  Use these for case insensitive usernames. WARNING: Slower queries!
  165 #
  166 #authorize_check_query = "\
  167 #   SELECT id, UserName, Attribute, Value, Op \
  168 #   FROM ${authcheck_table} \
  169 #   WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
  170 #   ORDER BY id"
  171 
  172 #authorize_reply_query = "\
  173 #   SELECT id, UserName, Attribute, Value, Op \
  174 #   FROM ${authreply_table} \
  175 #   WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
  176 #   ORDER BY id"
  177 
  178 authorize_check_query = "\
  179     SELECT id, UserName, Attribute, Value, Op \
  180     FROM ${authcheck_table} \
  181     WHERE Username = '%{SQL-User-Name}' \
  182     ORDER BY id"
  183 
  184 authorize_reply_query = "\
  185     SELECT id, UserName, Attribute, Value, Op \
  186     FROM ${authreply_table} \
  187     WHERE Username = '%{SQL-User-Name}' \
  188     ORDER BY id"
  189 
  190 #
  191 #  Use these for case insensitive usernames. WARNING: Slower queries!
  192 #
  193 #authorize_group_check_query = "\
  194 #   SELECT \
  195 #       ${groupcheck_table}.id, ${groupcheck_table}.GroupName, ${groupcheck_table}.Attribute, \
  196 #       ${groupcheck_table}.Value, ${groupcheck_table}.Op \
  197 #   FROM ${groupcheck_table}, ${usergroup_table} \
  198 #   WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') \
  199 #   AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
  200 #   ORDER BY ${groupcheck_table}.id"
  201 
  202 #authorize_group_reply_query = "\
  203 #   SELECT \
  204 #       ${groupreply_table}.id, ${groupreply_table}.GroupName, \
  205 #       ${groupreply_table}.Attribute, ${groupreply_table}.Value, ${groupreply_table}.Op \
  206 #   FROM ${groupreply_table}, ${usergroup_table} \
  207 #   WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') \
  208 #   AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
  209 #   ORDER BY ${groupreply_table}.id"
  210 
  211 authorize_group_check_query = "\
  212     SELECT id, GroupName, Attribute, Value, op \
  213     FROM ${groupcheck_table} \
  214     WHERE GroupName = '%{${group_attribute}}' \
  215     ORDER BY id"
  216 
  217 authorize_group_reply_query = "\
  218     SELECT id, GroupName, Attribute, Value, op \
  219     FROM ${groupreply_table} \
  220     WHERE GroupName = '%{${group_attribute}}' \
  221     ORDER BY id"
  222 
  223 #######################################################################
  224 # Simultaneous Use Checking Queries
  225 #######################################################################
  226 # simul_count_query     - query for the number of current connections
  227 #                       - If this is not defined, no simultaneous use checking
  228 #                       - will be performed by this module instance
  229 # simul_verify_query    - query to return details of current connections for verification
  230 #                       - Leave blank or commented out to disable verification step
  231 #                       - Note that the returned field order should not be changed.
  232 #######################################################################
  233 
  234 simul_count_query = "\
  235     SELECT COUNT(*) \
  236     FROM ${acct_table1} \
  237     WHERE UserName='%{SQL-User-Name}' \
  238     AND AcctStopTime IS NULL"
  239 
  240 simul_verify_query = "\
  241     SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, \
  242         FramedProtocol \
  243     FROM ${acct_table1} \
  244     WHERE UserName='%{SQL-User-Name}' \
  245     AND AcctStopTime IS NULL"
  246 
  247 #######################################################################
  248 # Group Membership Queries
  249 #######################################################################
  250 # group_membership_query        - Check user group membership
  251 #######################################################################
  252 
  253 # Use these for case insensitive usernames. WARNING: Slower queries!
  254 #group_membership_query = "\
  255 #   SELECT GroupName \
  256 #   FROM ${usergroup_table} \
  257 #   WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
  258 #   ORDER BY priority"
  259 
  260 group_membership_query = "\
  261     SELECT GroupName \
  262     FROM ${usergroup_table} \
  263     WHERE UserName='%{SQL-User-Name}' \
  264     ORDER BY priority"
  265 
  266 #######################################################################
  267 # Accounting and Post-Auth Queries
  268 #######################################################################
  269 # These queries insert/update accounting and authentication records.
  270 # The query to use is determined by the value of 'reference'.
  271 # This value is used as a configuration path and should resolve to one
  272 # or more 'query's. If reference points to multiple queries, and a query
  273 # fails, the next query is executed.
  274 #
  275 # Behaviour is identical to the old 1.x/2.x module, except we can now
  276 # fail between N queries, and query selection can be based on any
  277 # combination of attributes, or custom 'Acct-Status-Type' values.
  278 #######################################################################
  279 
  280 accounting {
  281     reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
  282 
  283     # Write SQL queries to a logfile. This is potentially useful for bulk inserts
  284     # when used with the rlm_sql_null driver.
  285 #   logfile = ${logdir}/accounting.sql
  286 
  287     column_list = "\
  288         AcctSessionId, \
  289         AcctUniqueId, \
  290         UserName, \
  291         Realm, \
  292         NASIPAddress, \
  293         NASPortId, \
  294         NASPortType, \
  295         AcctStartTime, \
  296         AcctUpdateTime, \
  297         AcctStopTime, \
  298         AcctSessionTime, \
  299         AcctAuthentic, \
  300         ConnectInfo_start, \
  301         ConnectInfo_Stop, \
  302         AcctInputOctets, \
  303         AcctOutputOctets, \
  304         CalledStationId, \
  305         CallingStationId, \
  306         AcctTerminateCause, \
  307         ServiceType, \
  308         FramedProtocol, \
  309         FramedIpAddress, \
  310         FramedIpv6Address, \
  311         FramedIpv6Prefix, \
  312         FramedInterfaceId, \
  313         DelegatedIpv6Prefix \
  314         ${..class.column_name}"
  315 
  316     type {
  317         accounting-on {
  318             query = "\
  319                 UPDATE ${....acct_table1} \
  320                 SET \
  321                     AcctStopTime = ${....event_timestamp}, \
  322                     AcctUpdateTime = ${....event_timestamp}, \
  323                     AcctSessionTime = (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime))), \
  324                     AcctTerminateCause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
  325                 WHERE AcctStopTime IS NULL \
  326                 AND NASIPAddress= '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
  327                 AND AcctStartTime <= ${....event_timestamp}"
  328         }
  329 
  330         accounting-off {
  331             query = "${..accounting-on.query}"
  332         }
  333 
  334         #
  335         #  Implement the "sql_session_start" policy.
  336         #  See raddb/policy.d/accounting for more details.
  337         #
  338         #  You also need to fix the other queries as
  339         #  documented below.  Look for "sql_session_start".
  340         #
  341         post-auth {
  342             query = "\
  343                 INSERT INTO ${....acct_table1} \
  344                     (${...column_list}) \
  345                 VALUES(\
  346                     '%{Acct-Session-Id}', \
  347                     '%{Acct-Unique-Session-Id}', \
  348                     '%{SQL-User-Name}', \
  349                     NULLIF('%{Realm}', ''), \
  350                     '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
  351                     NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
  352                     '%{NAS-Port-Type}', \
  353                     ${....event_timestamp}, \
  354                     NULL, \
  355                     NULL, \
  356                     0, \
  357                     '', \
  358                     '%{Connect-Info}', \
  359                     NULL, \
  360                     0, \
  361                     0, \
  362                     '%{Called-Station-Id}', \
  363                     '%{Calling-Station-Id}', \
  364                     NULL, \
  365                     '%{Service-Type}', \
  366                     '', \
  367                     NULL, \
  368                     NULL, \
  369                     NULL, \
  370                     NULL, \
  371                     NULL \
  372                     ${....class.reply_xlat})"
  373 
  374             query = "\
  375                 UPDATE ${....acct_table1} \
  376                 SET \
  377                     AcctStartTime = ${....event_timestamp}, \
  378                     AcctUpdateTime = ${....event_timestamp}, \
  379                     ConnectInfo_start = '%{Connect-Info}', \
  380                     AcctSessionId = '%{Acct-Session-Id}' \
  381                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  382                 AND AcctStopTime IS NULL"
  383         }
  384 
  385         start {
  386             query = "\
  387                 INSERT INTO ${....acct_table1} \
  388                     (${...column_list}) \
  389                 VALUES(\
  390                     '%{Acct-Session-Id}', \
  391                     '%{Acct-Unique-Session-Id}', \
  392                     '%{SQL-User-Name}', \
  393                     NULLIF('%{Realm}', ''), \
  394                     '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
  395                     NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
  396                     '%{NAS-Port-Type}', \
  397                     ${....event_timestamp}, \
  398                     ${....event_timestamp}, \
  399                     NULL, \
  400                     0, \
  401                     '%{Acct-Authentic}', \
  402                     '%{Connect-Info}', \
  403                     NULL, \
  404                     0, \
  405                     0, \
  406                     '%{Called-Station-Id}', \
  407                     '%{Calling-Station-Id}', \
  408                     NULL, \
  409                     '%{Service-Type}', \
  410                     '%{Framed-Protocol}', \
  411                     NULLIF('%{Framed-IP-Address}', '')::inet, \
  412                     NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  413                     NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  414                     NULLIF('%{Framed-Interface-Id}', ''), \
  415                     NULLIF('%{Delegated-IPv6-Prefix}', '')::inet \
  416                     ${....class.packet_xlat} ) \
  417                 ON CONFLICT (AcctUniqueId) \
  418                 DO UPDATE \
  419                 SET \
  420                     AcctStartTime = ${....event_timestamp}, \
  421                     AcctUpdateTime = ${....event_timestamp}, \
  422                     ConnectInfo_start = '%{Connect-Info}' \
  423                 WHERE ${....acct_table1}.AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  424                 AND ${....acct_table1}.AcctStopTime IS NULL"
  425 
  426             #
  427             #  When using "sql_session_start", you should comment out
  428             #  the previous query, and enable this one.
  429             #
  430             #  Just change the previous query to "-query",
  431             #  and this one to "query".  The previous one
  432             #  will be ignored, and this one will be
  433             #  enabled.
  434             #
  435             -query = "\
  436                 UPDATE ${....acct_table1} \
  437                 SET \
  438                     AcctSessionId = '%{Acct-Session-Id}', \
  439                     AcctUniqueId = '%{Acct-Unique-Session-Id}', \
  440                     AcctAuthentic = '%{Acct-Authentic}', \
  441                     ConnectInfo_start = '%{Connect-Info}', \
  442                     ServiceType = '%{Service-Type}', \
  443                     FramedProtocol = '%{Framed-Protocol}', \
  444                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  445                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  446                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  447                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  448                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
  449                     AcctStartTime = ${....event_timestamp}, \
  450                     AcctUpdateTime = ${....event_timestamp} \
  451                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  452                 AND AcctStopTime IS NULL"
  453 
  454             # and again where we don't have "AND AcctStopTime IS NULL"
  455             query = "\
  456                 UPDATE ${....acct_table1} \
  457                 SET \
  458                     AcctStartTime = ${....event_timestamp}, \
  459                     AcctUpdateTime = ${....event_timestamp}, \
  460                     ConnectInfo_start = '%{Connect-Info}' \
  461                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
  462         }
  463 
  464         interim-update {
  465             query = "\
  466                 UPDATE ${....acct_table1} \
  467                 SET \
  468                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  469                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  470                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  471                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  472                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
  473                     AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \
  474                     AcctInterval = (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM (COALESCE(AcctUpdateTime, AcctStartTime)))), \
  475                     AcctUpdateTime = ${....event_timestamp}, \
  476                     AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  477                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  478                     AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  479                         '%{%{Acct-Output-Octets}:-0}'::bigint) \
  480                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  481                 AND AcctStopTime IS NULL"
  482 
  483             query = "\
  484                 INSERT INTO ${....acct_table1} \
  485                     (${...column_list}) \
  486                 VALUES(\
  487                     '%{Acct-Session-Id}', \
  488                     '%{Acct-Unique-Session-Id}', \
  489                     '%{SQL-User-Name}', \
  490                     NULLIF('%{Realm}', ''), \
  491                     '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
  492                     NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
  493                     '%{NAS-Port-Type}', \
  494                     ${....event_timestamp}, \
  495                     ${....event_timestamp}, \
  496                     NULL, \
  497                     %{%{Acct-Session-Time}:-NULL}, \
  498                     '%{Acct-Authentic}', \
  499                     '%{Connect-Info}', \
  500                     NULL, \
  501                     (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  502                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  503                     (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  504                         '%{%{Acct-Output-Octets}:-0}'::bigint), \
  505                     '%{Called-Station-Id}', \
  506                     '%{Calling-Station-Id}', \
  507                     NULL, \
  508                     '%{Service-Type}', \
  509                     '%{Framed-Protocol}', \
  510                     NULLIF('%{Framed-IP-Address}', '')::inet, \
  511                     NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  512                     NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  513                     NULLIF('%{Framed-Interface-Id}', ''), \
  514                     NULLIF('%{Delegated-IPv6-Prefix}', '')::inet \
  515                     ${....class.packet_xlat}) \
  516                 ON CONFLICT (AcctUniqueId) \
  517                 DO NOTHING"
  518 
  519             #
  520             #  When using "sql_session_start", you should comment out
  521             #  the previous query, and enable this one.
  522             #
  523             #  Just change the previous query to "-query",
  524             #  and this one to "query".  The previous one
  525             #  will be ignored, and this one will be
  526             #  enabled.
  527             #
  528             -query = "\
  529                 UPDATE ${....acct_table1} \
  530                 SET \
  531                     AcctSessionId = '%{Acct-Session-Id}', \
  532                     AcctUniqueId = '%{Acct-Unique-Session-Id}', \
  533                     AcctAuthentic = '%{Acct-Authentic}', \
  534                     ConnectInfo_start = '%{Connect-Info}', \
  535                     ServiceType = '%{Service-Type}', \
  536                     FramedProtocol = '%{Framed-Protocol}', \
  537                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  538                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  539                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  540                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  541                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
  542                     AcctUpdateTime = ${....event_timestamp}, \
  543                     AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
  544                         (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
  545                     AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  546                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  547                     AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  548                         '%{%{Acct-Output-Octets}:-0}'::bigint) \
  549                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  550                 AND AcctStopTime IS NULL"
  551         }
  552 
  553         stop {
  554             query = "\
  555                 UPDATE ${....acct_table2} \
  556                 SET \
  557                     AcctStopTime = ${....event_timestamp}, \
  558                     AcctUpdateTime = ${....event_timestamp}, \
  559                     AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
  560                         (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
  561                     AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  562                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  563                     AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  564                         '%{%{Acct-Output-Octets}:-0}'::bigint), \
  565                     AcctTerminateCause = '%{Acct-Terminate-Cause}', \
  566                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  567                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  568                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  569                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  570                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
  571                     ConnectInfo_stop = '%{Connect-Info}' \
  572                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  573                 AND AcctStopTime IS NULL"
  574 
  575             query = "\
  576                 INSERT INTO ${....acct_table1} \
  577                     (${...column_list}) \
  578                 VALUES(\
  579                     '%{Acct-Session-Id}', \
  580                     '%{Acct-Unique-Session-Id}', \
  581                     '%{SQL-User-Name}', \
  582                     NULLIF('%{Realm}', ''), \
  583                     '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
  584                     NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
  585                     '%{NAS-Port-Type}', \
  586                     TO_TIMESTAMP(${....event_timestamp_epoch} - %{%{Acct-Session-Time}:-0}), \
  587                     ${....event_timestamp}, \
  588                     ${....event_timestamp}, \
  589                     NULLIF('%{Acct-Session-Time}', '')::bigint, \
  590                     '%{Acct-Authentic}', \
  591                     '%{Connect-Info}', \
  592                     NULL, \
  593                     (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  594                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  595                     (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  596                         '%{%{Acct-Output-Octets}:-0}'::bigint), \
  597                     '%{Called-Station-Id}', \
  598                     '%{Calling-Station-Id}', \
  599                     '%{Acct-Terminate-Cause}', \
  600                     '%{Service-Type}', \
  601                     '%{Framed-Protocol}', \
  602                     NULLIF('%{Framed-IP-Address}', '')::inet, \
  603                     NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  604                     NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  605                     NULLIF('%{Framed-Interface-Id}', ''), \
  606                     NULLIF('%{Delegated-IPv6-Prefix}', '')::inet \
  607                     ${....class.packet_xlat}) \
  608                 ON CONFLICT (AcctUniqueId) \
  609                 DO NOTHING"
  610 
  611             #
  612             #  When using "sql_session_start", you should comment out
  613             #  the previous query, and enable this one.
  614             #
  615             #  Just change the previous query to "-query",
  616             #  and this one to "query".  The previous one
  617             #  will be ignored, and this one will be
  618             #  enabled.
  619             #
  620             -query = "\
  621                 UPDATE ${....acct_table1} \
  622                 SET \
  623                     AcctSessionId = '%{Acct-Session-Id}', \
  624                     AcctUniqueId = '%{Acct-Unique-Session-Id}', \
  625                     AcctAuthentic = '%{Acct-Authentic}', \
  626                     ConnectInfo_start = '%{Connect-Info}', \
  627                     ServiceType = '%{Service-Type}', \
  628                     FramedProtocol = '%{Framed-Protocol}', \
  629                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  630                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  631                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  632                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  633                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
  634                     AcctStopTime = ${....event_timestamp}, \
  635                     AcctUpdateTime = ${....event_timestamp}, \
  636                     AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
  637                         (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
  638                     AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  639                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  640                     AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  641                         '%{%{Acct-Output-Octets}:-0}'::bigint), \
  642                     AcctTerminateCause = '%{Acct-Terminate-Cause}', \
  643                     ConnectInfo_stop = '%{Connect-Info}' \
  644                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
  645                 AND AcctStopTime IS NULL"
  646 
  647             # and again where we don't have "AND AcctStopTime IS NULL"
  648             query = "\
  649                 UPDATE ${....acct_table2} \
  650                 SET \
  651                     AcctStopTime = ${....event_timestamp}, \
  652                     AcctUpdateTime = ${....event_timestamp}, \
  653                     AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
  654                         (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
  655                     AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
  656                         '%{%{Acct-Input-Octets}:-0}'::bigint), \
  657                     AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
  658                         '%{%{Acct-Output-Octets}:-0}'::bigint), \
  659                     AcctTerminateCause = '%{Acct-Terminate-Cause}', \
  660                     FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  661                     FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
  662                     FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
  663                     FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
  664                     DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
  665                     ConnectInfo_stop = '%{Connect-Info}' \
  666                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
  667         }
  668 
  669         #
  670         #  No Acct-Status-Type == ignore the packet
  671         #
  672         accounting {
  673              query = "SELECT true"
  674         }
  675     }
  676 }
  677 
  678 
  679 #######################################################################
  680 # Authentication Logging Queries
  681 #######################################################################
  682 # postauth_query                - Insert some info after authentication
  683 #######################################################################
  684 
  685 post-auth {
  686     # Write SQL queries to a logfile. This is potentially useful for bulk inserts
  687     # when used with the rlm_sql_null driver.
  688 #   logfile = ${logdir}/post-auth.sql
  689 
  690     query = "\
  691         INSERT INTO ${..postauth_table} \
  692             (username, pass, reply, authdate ${..class.column_name}) \
  693         VALUES(\
  694             '%{User-Name}', \
  695             '%{%{User-Password}:-%{Chap-Password}}', \
  696             '%{reply:Packet-Type}', \
  697             '%S.%M' \
  698             ${..class.reply_xlat})"
  699 }