+# -*- text -*-
+##
+## dialup.conf -- PostgreSQL configuration for default schema (schema.sql)
+##
+## $Id$
+
+# Safe characters list for sql queries. Everything else is replaced
+# with their mime-encoded equivalents.
+# The default list should be ok
+# safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
+
+#######################################################################
+# Query config: Username
+#######################################################################
+# This is the username that will get substituted, escaped, and added
+# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used
+# below everywhere a username substitution is needed so you you can
+# be sure the username passed from the client is escaped properly.
#
-# $Id$
-#
-# Configuration for the SQL module, when using Postgresql.
-#
-# The database schema is available at:
+# Uncomment the next line, if you want the sql_user_name to mean:
#
-# doc/examples/postgresql.sql
+# Use Stripped-User-Name, if it's there.
+# Else use User-Name, if it's there,
+# Else use hard-coded string "none" as the user name.
#
+#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
+sql_user_name = "%{User-Name}"
- # Safe characters list for sql queries. Everything else is replaced
- # with their mime-encoded equivalents.
- # The default list should be ok
-# safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
-
- #######################################################################
- # Query config: Username
- #######################################################################
- # This is the username that will get substituted, escaped, and added
- # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used
- # below everywhere a username substitution is needed so you you can
- # be sure the username passed from the client is escaped properly.
- #
- # Uncomment the next line, if you want the sql_user_name to mean:
- #
- # Use Stripped-User-Name, if it's there.
- # Else use User-Name, if it's there,
- # Else use hard-coded string "none" as the user name.
- #
- #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
- #
- sql_user_name = "%{User-Name}"
-
- #######################################################################
- # Default profile
- #######################################################################
- # This is the default profile. It is found in SQL by group membership.
- # That means that this profile must be a member of at least one group
- # which will contain the corresponding check and reply items.
- # This profile will be queried in the authorize section for every user.
- # The point is to assign all users a default profile without having to
- # manually add each one to a group that will contain the profile.
- # The SQL module will also honor the User-Profile attribute. This
- # attribute can be set anywhere in the authorize section (ie the users
- # file). It is found exactly as the default profile is found.
- # If it is set then it will *overwrite* the default profile setting.
- # The idea is to select profiles based on checks on the incoming
- # packets, not on user group membership. For example:
- # -- users file --
- # DEFAULT Service-Type == Outbound-User, User-Profile := "outbound"
- # DEFAULT Service-Type == Framed-User, User-Profile := "framed"
- #
- # By default the default_user_profile is not set
- #
-# default_user_profile = "DEFAULT"
+#######################################################################
+# Default profile
+#######################################################################
+# This is the default profile. It is found in SQL by group membership.
+# That means that this profile must be a member of at least one group
+# which will contain the corresponding check and reply items.
+# This profile will be queried in the authorize section for every user.
+# The point is to assign all users a default profile without having to
+# manually add each one to a group that will contain the profile.
+# The SQL module will also honor the User-Profile attribute. This
+# attribute can be set anywhere in the authorize section (ie the users
+# file). It is found exactly as the default profile is found.
+# If it is set then it will *overwrite* the default profile setting.
+# The idea is to select profiles based on checks on the incoming
+# packets, not on user group membership. For example:
+# -- users file --
+# DEFAULT Service-Type == Outbound-User, User-Profile := "outbound"
+# DEFAULT Service-Type == Framed-User, User-Profile := "framed"
+#
+# By default the default_user_profile is not set
+#
+# default_user_profile = "DEFAULT"
- #######################################################################
- # NAS Query
- #######################################################################
- # This query retrieves the radius clients
- #
- # 0. Row ID (currently unused)
- # 1. Name (or IP address)
- # 2. Shortname
- # 3. Type
- # 4. Secret
- #######################################################################
+#######################################################################
+# NAS Query
+#######################################################################
+# This query retrieves the radius clients
+#
+# 0. Row ID (currently unused)
+# 1. Name (or IP address)
+# 2. Shortname
+# 3. Type
+# 4. Secret
+# 5. Server
+#######################################################################
- nas_query = "SELECT id, nasname, shortname, type, secret FROM ${nas_table}"
+nas_query = "SELECT id, nasname, shortname, type, secret, server FROM ${nas_table}"
- #######################################################################
- # Authorization Queries
- #######################################################################
- # These queries compare the check items for the user
- # in ${authcheck_table} and setup the reply items in
- # ${authreply_table}. You can use any query/tables
- # you want, but the return data for each row MUST
- # be in the following order:
- #
- # 0. Row ID (currently unused)
- # 1. UserName/GroupName
- # 2. Item Attr Name
- # 3. Item Attr Value
- # 4. Item Attr Operation
- #######################################################################
+#######################################################################
+# Authorization Queries
+#######################################################################
+# These queries compare the check items for the user
+# in ${authcheck_table} and setup the reply items in
+# ${authreply_table}. You can use any query/tables
+# you want, but the return data for each row MUST
+# be in the following order:
+#
+# 0. Row ID (currently unused)
+# 1. UserName/GroupName
+# 2. Item Attr Name
+# 3. Item Attr Value
+# 4. Item Attr Operation
+#######################################################################
- # Use these for case insensitive usernames. WARNING: Slower queries!
+# Use these for case insensitive usernames. WARNING: Slower queries!
# authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
# FROM ${authcheck_table} \
# WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
# WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
# ORDER BY id"
- authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
- FROM ${authcheck_table} \
- WHERE Username = '%{SQL-User-Name}' \
- ORDER BY id"
+authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
+ FROM ${authcheck_table} \
+ WHERE Username = '%{SQL-User-Name}' \
+ ORDER BY id"
- authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \
- FROM ${authreply_table} \
- WHERE Username = '%{SQL-User-Name}' \
- ORDER BY id"
+authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \
+ FROM ${authreply_table} \
+ WHERE Username = '%{SQL-User-Name}' \
+ ORDER BY id"
- # Use these for case insensitive usernames. WARNING: Slower queries!
+# Use these for case insensitive usernames. WARNING: Slower queries!
# authorize_group_check_query = "SELECT ${groupcheck_table}.id, ${groupcheck_table}.GroupName, \
# ${groupcheck_table}.Attribute, ${groupcheck_table}.Value, ${groupcheck_table}.Op \
# FROM ${groupcheck_table}, ${usergroup_table} \
# WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
# ORDER BY ${groupreply_table}.id"
- authorize_group_check_query = "SELECT id, GroupName, Attribute, Value, op \
- FROM ${groupcheck_table} \
- WHERE GroupName = '%{Sql-Group}' \
- ORDER BY id"
+authorize_group_check_query = "SELECT id, GroupName, Attribute, Value, op \
+ FROM ${groupcheck_table} \
+ WHERE GroupName = '%{Sql-Group}' \
+ ORDER BY id"
- authorize_group_reply_query = "SELECT id, GroupName, Attribute, Value, op \
- FROM ${groupreply_table} \
- WHERE GroupName = '%{Sql-Group}' \
- ORDER BY id"
+authorize_group_reply_query = "SELECT id, GroupName, Attribute, Value, op \
+ FROM ${groupreply_table} \
+ WHERE GroupName = '%{Sql-Group}' \
+ ORDER BY id"
- #######################################################################
- # Simultaneous Use Checking Queries
- #######################################################################
- # simul_count_query - query for the number of current connections
- # - If this is not defined, no simultaneous use checking
- # - will be performed by this module instance
- # simul_verify_query - query to return details of current connections for verification
- # - Leave blank or commented out to disable verification step
- # - Note that the returned field order should not be changed.
- #######################################################################
+#######################################################################
+# Simultaneous Use Checking Queries
+#######################################################################
+# simul_count_query - query for the number of current connections
+# - If this is not defined, no simultaneous use checking
+# - will be performed by this module instance
+# simul_verify_query - query to return details of current connections for verification
+# - Leave blank or commented out to disable verification step
+# - Note that the returned field order should not be changed.
+#######################################################################
- # Uncomment simul_count_query to enable simultaneous use checking
- # simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
- # simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
+# Uncomment simul_count_query to enable simultaneous use checking
+# simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
+# simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
- #######################################################################
- # Accounting Queries
- #######################################################################
- # accounting_onoff_query - query for Accounting On/Off packets
- # accounting_update_query - query for Accounting update packets
- # accounting_update_query_alt - query for Accounting update packets
- # (alternate in case first query fails)
- # accounting_start_query - query for Accounting start packets
- # accounting_start_query_alt - query for Accounting start packets
- # (alternate in case first query fails)
- # accounting_stop_query - query for Accounting stop packets
- # accounting_stop_query_alt - query for Accounting start packets
- # (alternate in case first query doesn't
- # affect any existing rows in the table)
- #######################################################################
+#######################################################################
+# Accounting Queries
+#######################################################################
+# accounting_onoff_query - query for Accounting On/Off packets
+# accounting_update_query - query for Accounting update packets
+# accounting_update_query_alt - query for Accounting update packets
+# (alternate in case first query fails)
+# accounting_start_query - query for Accounting start packets
+# accounting_start_query_alt - query for Accounting start packets
+# (alternate in case first query fails)
+# accounting_stop_query - query for Accounting stop packets
+# accounting_stop_query_alt - query for Accounting start packets
+# (alternate in case first query doesn't
+# affect any existing rows in the table)
+#######################################################################
- accounting_onoff_query = "UPDATE ${acct_table1} \
- SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
- AcctSessionTime = (EXTRACT(EPOCH FROM ('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone \
- - '%{%{Acct-Delay-Time}:-0}'::interval)))::BIGINT, \
- AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = 0 \
- WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'::timestamp"
+accounting_onoff_query = "UPDATE ${acct_table1} \
+ SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
+ AcctSessionTime = (EXTRACT(EPOCH FROM ('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone \
+ - '%{%{Acct-Delay-Time}:-0}'::interval)))::BIGINT, \
+ AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+ AcctStopDelay = 0 \
+ WHERE AcctStopTime IS NULL \
+ AND NASIPAddress= '%{NAS-IP-Address}' \
+ AND AcctStartTime <= '%S'::timestamp"
- accounting_update_query = "UPDATE ${acct_table1} \
- SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
- AcctSessionTime = '%{Acct-Session-Time}', \
- AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
- AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint) \
- WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
- AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
+accounting_update_query = "UPDATE ${acct_table1} \
+ SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ AcctSessionTime = '%{Acct-Session-Time}', \
+ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint) \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
- accounting_update_query_alt = "INSERT INTO ${acct_table1} \
- (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, \
- NASPortId, NASPortType, AcctStartTime, \
- AcctSessionTime, AcctAuthentic, AcctInputOctets, \
- AcctOutputOctets, CalledStationId, CallingStationId, \
- ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) \
- VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
- '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
- %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', \
- ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), \
- '%{Acct-Session-Time}', '%{Acct-Authentic}', \
- (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
- (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
- '%{Called-Station-Id}', \
- '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
- NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')"
+accounting_update_query_alt = "INSERT INTO ${acct_table1} \
+ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, \
+ NASPortId, NASPortType, AcctStartTime, \
+ AcctSessionTime, AcctAuthentic, AcctInputOctets, \
+ AcctOutputOctets, CalledStationId, CallingStationId, \
+ ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) \
+ VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{NAS-IP-Address}', \
+ %{%{NAS-Port}:-NULL}, '%{NAS-Port-Type}', \
+ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), \
+ '%{Acct-Session-Time}', '%{Acct-Authentic}', \
+ (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')"
- accounting_start_query = "INSERT INTO ${acct_table1} \
- (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, \
- NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
- ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, \
- FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \
- VALUES('%{Acct-Session-Id}', \
- '%{Acct-Unique-Session-Id}', \
- '%{SQL-User-Name}', '%{Realm}', \
- '%{NAS-IP-Address}', \
- %{%{NAS-Port}:-NULL}::integer, \
- '%{NAS-Port-Type}', \
- ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
- '%{Acct-Authentic}', \
- '%{Connect-Info}', \
- '%{Called-Station-Id}', \
- '%{Calling-Station-Id}', \
- '%{Service-Type}', \
- '%{Framed-Protocol}', \
- NULLIF('%{Framed-IP-Address}', '')::inet, \
- 0', \
- '%{X-Ascend-Session-Svr-Key}')"
+accounting_start_query = "INSERT INTO ${acct_table1} \
+ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, \
+ NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
+ ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, \
+ FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \
+ VALUES('%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{NAS-IP-Address}', \
+ %{%{NAS-Port}:-NULL}, \
+ '%{NAS-Port-Type}', \
+ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet, \
+ 0, \
+ '%{X-Ascend-Session-Svr-Key}')"
- accounting_start_query_alt = "UPDATE ${acct_table1} \
- SET AcctStartTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
- AcctStartDelay = 0, \
- ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
- AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
+accounting_start_query_alt = "UPDATE ${acct_table1} \
+ SET AcctStartTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
+ AcctStartDelay = 0, \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStopTime IS NULL"
- accounting_stop_query = "UPDATE ${acct_table2} \
- SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
- AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
- (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE \
- - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct-Session-Time}' END, \
- AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
- AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
- AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 0, \
- FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, ConnectInfo_stop = '%{Connect-Info}' \
- WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
- AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
+accounting_stop_query = "UPDATE ${acct_table2} \
+ SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
+ AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
+ (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE \
+ - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT \
+ ELSE NULLIF('%{Acct-Session-Time}','')::BIGINT END, \
+ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+ AcctStopDelay = 0, \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStopTime IS NULL"
- accounting_stop_query_alt = "INSERT INTO ${acct_table2} \
- (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \
- AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, \
- CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) \
- values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
- %{%{NAS-Port}:-NULL}::integer, '%{NAS-Port-Type}', ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), \
- ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, '%{Acct-Authentic}', \
- '%{Connect-Info}', (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
- (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), '%{Called-Station-Id}', \
- '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', \
- NULLIF('%{Framed-IP-Address}', '')::inet, 0)"
+accounting_stop_query_alt = "INSERT INTO ${acct_table2} \
+ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \
+ AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, \
+ CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) \
+ values('%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{NAS-IP-Address}', \
+ %{%{NAS-Port}:-NULL}, \
+ '%{NAS-Port-Type}', \
+ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval - '%{%{Acct-Session-Time}:-0}'::interval), \
+ ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
+ NULLIF('%{Acct-Session-Time}', '')::bigint, '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Acct-Terminate-Cause}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet, 0)"
- #######################################################################
- # Group Membership Queries
- #######################################################################
- # group_membership_query - Check user group membership
- #######################################################################
+#######################################################################
+# Group Membership Queries
+#######################################################################
+# group_membership_query - Check user group membership
+#######################################################################
- # Use these for case insensitive usernames. WARNING: Slower queries!
+# Use these for case insensitive usernames. WARNING: Slower queries!
# group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') ORDER BY priority"
- group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}' ORDER BY priority"
+group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}' ORDER BY priority"
- #######################################################################
- # Authentication Logging Queries
- #######################################################################
- # postauth_query - Insert some info after authentication
- #######################################################################
- postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply, authdate) \
- VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW())"
+#######################################################################
+# Authentication Logging Queries
+#######################################################################
+# postauth_query - Insert some info after authentication
+#######################################################################
+postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply, authdate) \
+ VALUES ('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW())"