summaryrefslogtreecommitdiffstats
path: root/raddb/mods-config/sql/main/postgresql/queries.conf
diff options
context:
space:
mode:
Diffstat (limited to 'raddb/mods-config/sql/main/postgresql/queries.conf')
-rw-r--r--raddb/mods-config/sql/main/postgresql/queries.conf742
1 files changed, 742 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/postgresql/queries.conf b/raddb/mods-config/sql/main/postgresql/queries.conf
new file mode 100644
index 0000000..18a1ed0
--- /dev/null
+++ b/raddb/mods-config/sql/main/postgresql/queries.conf
@@ -0,0 +1,742 @@
+# -*- text -*-
+#
+# main/postgresql/queries.conf -- PostgreSQL configuration for default schema (schema.sql)
+#
+# $Id$
+
+# Use the driver specific SQL escape method.
+#
+# If you enable this configuration item, the "safe_characters"
+# configuration is ignored. FreeRADIUS then uses the PostgreSQL escape
+# functions to escape input strings. The only downside to making this
+# change is that the PostgreSQL escaping method is not the same the one
+# used by FreeRADIUS. So characters which are NOT in the
+# "safe_characters" list will now be stored differently in the database.
+#
+#auto_escape = yes
+
+# Safe characters list for sql queries. Everything else is replaced
+# with their mime-encoded equivalents.
+# The default list should be ok
+# Using 'auto_escape' is preferred
+# 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}"
+
+#######################################################################
+# Query config: Event-Timestamp
+#######################################################################
+# event_timestamp_epoch is the basis for the time inserted into
+# accounting records. Typically this will be the Event-Timestamp of the
+# accounting request, which is usually provided by a NAS.
+#
+# Uncomment the next line, if you want the timestamp to be based on the
+# request reception time recorded by this server, for example if you
+# distrust the provided Event-Timestamp.
+#event_timestamp_epoch = "%l"
+
+event_timestamp_epoch = "%{%{integer:Event-Timestamp}:-%l}"
+
+# event_timestamp is the SQL snippet for converting an epoch timestamp
+# to an SQL date.
+
+event_timestamp = "TO_TIMESTAMP(${event_timestamp_epoch})"
+
+#######################################################################
+# Query config: Class attribute
+#######################################################################
+#
+# 3.0.22 and later have a "class" column in the accounting table.
+#
+# However, we do NOT want to break existing configurations by adding
+# the Class attribute to the default queries. If we did that, then
+# systems using newer versions of the server would fail, because
+# there is no "class" column in their accounting tables.
+#
+# The solution to that is the following "class" subsection. If your
+# database has a "class" column for the various tables, then you can
+# uncomment the configuration items here. The queries below will
+# then automatically insert the Class attribute into radacct,
+# radpostauth, etc.
+#
+class {
+ #
+ # Delete the '#' character from each of the configuration
+ # items in this section. This change puts the Class
+ # attribute into the various tables. Leave the double-quoted
+ # string there, as the value for the configuration item.
+ #
+ # See also policy.d/accounting, and the "insert_acct_class"
+ # policy. You will need to list (or uncomment)
+ # "insert_acct_class" in the "post-auth" section in order to
+ # create a Class attribute.
+ #
+ column_name = # ", Class"
+ packet_xlat = # ", '%{Class}'"
+ reply_xlat = # ", '%{reply:Class}'"
+}
+
+#######################################################################
+# 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"
+
+#######################################################################
+# Open Query
+#######################################################################
+# This query is run whenever a new connection is opened.
+# It is commented out by default.
+#
+# If you have issues with connections hanging for too long, uncomment
+# the next line, and set the timeout in milliseconds. As a general
+# rule, if the queries take longer than a second, something is wrong
+# with the database.
+#open_query = "set statement_timeout to 1000"
+
+#######################################################################
+# 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
+#######################################################################
+
+client_query = "\
+ SELECT id, nasname, shortname, type, secret, server \
+ FROM ${client_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
+#######################################################################
+
+#
+# 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}') \
+# ORDER BY id"
+
+#authorize_reply_query = "\
+# SELECT id, UserName, Attribute, Value, Op \
+# FROM ${authreply_table} \
+# 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_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!
+#
+#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 = ${groupcheck_table}.GroupName \
+# ORDER BY ${groupcheck_table}.id"
+
+#authorize_group_reply_query = "\
+# SELECT \
+# ${groupreply_table}.id, ${groupreply_table}.GroupName, \
+# ${groupreply_table}.Attribute, ${groupreply_table}.Value, ${groupreply_table}.Op \
+# FROM ${groupreply_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 = '%{${group_attribute}}' \
+ ORDER BY id"
+
+authorize_group_reply_query = "\
+ SELECT id, GroupName, Attribute, Value, op \
+ FROM ${groupreply_table} \
+ WHERE GroupName = '%{${group_attribute}}' \
+ 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.
+#######################################################################
+
+simul_count_query = "\
+ SELECT COUNT(RadAcctId) \
+ FROM ${acct_table1} a \
+ LEFT OUTER JOIN nasreload n USING (NASIPAddress) \
+ WHERE UserName='%{SQL-User-Name}' \
+ AND AcctStopTime IS NULL \
+ AND (a.AcctStartTime > n.ReloadTime OR n.ReloadTime IS NULL)"
+
+simul_verify_query = "\
+ SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, \
+ FramedProtocol \
+ FROM ${acct_table1} a \
+ LEFT OUTER JOIN nasreload n USING (nasipaddress) \
+ WHERE UserName='%{SQL-User-Name}' \
+ AND AcctStopTime IS NULL \
+ AND (a.AcctStartTime > n.reloadtime OR n.reloadtime IS NULL)"
+
+#######################################################################
+# Group Membership Queries
+#######################################################################
+# group_membership_query - Check user group membership
+#######################################################################
+
+# 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"
+
+#######################################################################
+# Accounting and Post-Auth Queries
+#######################################################################
+# These queries insert/update accounting and authentication records.
+# The query to use is determined by the value of 'reference'.
+# This value is used as a configuration path and should resolve to one
+# or more 'query's. If reference points to multiple queries, and a query
+# fails, the next query is executed.
+#
+# Behaviour is identical to the old 1.x/2.x module, except we can now
+# fail between N queries, and query selection can be based on any
+# combination of attributes, or custom 'Acct-Status-Type' values.
+#######################################################################
+
+accounting {
+ reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
+
+ # Write SQL queries to a logfile. This is potentially useful for bulk inserts
+ # when used with the rlm_sql_null driver.
+# logfile = ${logdir}/accounting.sql
+
+ column_list = "\
+ AcctSessionId, \
+ AcctUniqueId, \
+ UserName, \
+ Realm, \
+ NASIPAddress, \
+ NASPortId, \
+ NASPortType, \
+ AcctStartTime, \
+ AcctUpdateTime, \
+ AcctStopTime, \
+ AcctSessionTime, \
+ AcctAuthentic, \
+ ConnectInfo_start, \
+ ConnectInfo_Stop, \
+ AcctInputOctets, \
+ AcctOutputOctets, \
+ CalledStationId, \
+ CallingStationId, \
+ AcctTerminateCause, \
+ ServiceType, \
+ FramedProtocol, \
+ FramedIpAddress, \
+ FramedIpv6Address, \
+ FramedIpv6Prefix, \
+ FramedInterfaceId, \
+ DelegatedIpv6Prefix \
+ ${..class.column_name}"
+
+ type {
+
+ accounting-on {
+
+ #
+ # "Bulk update" Accounting-On/Off strategy.
+ #
+ # Immediately terminate all sessions associated with a
+ # given NAS.
+ #
+ # Note: If a large number of sessions require closing
+ # then the bulk update may be take a long time to run
+ # and lock an excessive number of rows. See the
+ # strategy below for an alternative approach that does
+ # not touch the radacct session data.
+ #
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStopTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ AcctSessionTime = (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime))), \
+ AcctTerminateCause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
+ WHERE AcctStopTime IS NULL \
+ AND NASIPAddress= '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+ AND AcctStartTime <= ${....event_timestamp}"
+
+ #
+ # "Lightweight" Accounting-On/Off strategy.
+ #
+ # Record the reload time of the NAS and let the
+ # administrator actually close the sessions in radacct
+ # out-of-band, if desired.
+ #
+ # Implementation advice, together with a stored
+ # procedure for closing sessions and a view showing
+ # the effective stop time of each session is provided
+ # in process-radacct.sql.
+ #
+ # To enable this strategy, just change the previous
+ # query to "-query", and this one to "query". The
+ # previous one will be ignored, and this one will be
+ # enabled.
+ #
+ -query = "\
+ INSERT INTO nasreload (NASIPAddress, ReloadTime) \
+ VALUES ('%{NAS-IP-Address}', ${....event_timestamp}) \
+ ON CONFLICT ON (NASIPAddress) \
+ DO UPDATE SET \
+ ReloadTime = ${....event_timestamp}"
+
+ }
+
+ accounting-off {
+ query = "${..accounting-on.query}"
+ }
+
+ #
+ # Implement the "sql_session_start" policy.
+ # See raddb/policy.d/accounting for more details.
+ #
+ # You also need to fix the other queries as
+ # documented below. Look for "sql_session_start".
+ #
+ post-auth {
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
+ '%{NAS-Port-Type}', \
+ ${....event_timestamp}, \
+ ${....event_timestamp}, \
+ NULL, \
+ 0, \
+ '', \
+ '%{Connect-Info}', \
+ NULL, \
+ 0, \
+ 0, \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ NULL, \
+ '%{Service-Type}', \
+ '', \
+ NULL, \
+ NULL, \
+ NULL, \
+ NULL, \
+ NULL \
+ ${....class.reply_xlat})"
+
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStartTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ ConnectInfo_start = '%{Connect-Info}', \
+ AcctSessionId = '%{Acct-Session-Id}' \
+ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND AcctStopTime IS NULL"
+ }
+
+ start {
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
+ '%{NAS-Port-Type}', \
+ ${....event_timestamp}, \
+ ${....event_timestamp}, \
+ NULL, \
+ 0, \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ NULL, \
+ 0, \
+ 0, \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ NULL, \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet, \
+ NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ NULLIF('%{Framed-Interface-Id}', ''), \
+ NULLIF('%{Delegated-IPv6-Prefix}', '')::inet \
+ ${....class.packet_xlat} ) \
+ ON CONFLICT (AcctUniqueId) \
+ DO UPDATE \
+ SET \
+ AcctStartTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE ${....acct_table1}.AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND ${....acct_table1}.AcctStopTime IS NULL"
+
+ #
+ # When using "sql_session_start", you should comment out
+ # the previous query, and enable this one.
+ #
+ # Just change the previous query to "-query",
+ # and this one to "query". The previous one
+ # will be ignored, and this one will be
+ # enabled.
+ #
+ -query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctSessionId = '%{Acct-Session-Id}', \
+ AcctUniqueId = '%{Acct-Unique-Session-Id}', \
+ AcctAuthentic = '%{Acct-Authentic}', \
+ ConnectInfo_start = '%{Connect-Info}', \
+ ServiceType = '%{Service-Type}', \
+ FramedProtocol = '%{Framed-Protocol}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
+ DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
+ AcctStartTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp} \
+ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND AcctStopTime IS NULL"
+
+ # and again where we don't have "AND AcctStopTime IS NULL"
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStartTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
+ }
+
+ interim-update {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
+ DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
+ AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \
+ AcctInterval = (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM (COALESCE(AcctUpdateTime, AcctStartTime)))), \
+ AcctUpdateTime = ${....event_timestamp}, \
+ 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 AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND AcctStopTime IS NULL"
+
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
+ '%{NAS-Port-Type}', \
+ ${....event_timestamp}, \
+ ${....event_timestamp}, \
+ NULL, \
+ %{%{Acct-Session-Time}:-NULL}, \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ NULL, \
+ (('%{%{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}', \
+ NULL, \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet, \
+ NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ NULLIF('%{Framed-Interface-Id}', ''), \
+ NULLIF('%{Delegated-IPv6-Prefix}', '')::inet \
+ ${....class.packet_xlat}) \
+ ON CONFLICT (AcctUniqueId) \
+ DO NOTHING"
+
+ #
+ # When using "sql_session_start", you should comment out
+ # the previous query, and enable this one.
+ #
+ # Just change the previous query to "-query",
+ # and this one to "query". The previous one
+ # will be ignored, and this one will be
+ # enabled.
+ #
+ -query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctSessionId = '%{Acct-Session-Id}', \
+ AcctUniqueId = '%{Acct-Unique-Session-Id}', \
+ AcctAuthentic = '%{Acct-Authentic}', \
+ ConnectInfo_start = '%{Connect-Info}', \
+ ServiceType = '%{Service-Type}', \
+ FramedProtocol = '%{Framed-Protocol}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
+ DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
+ (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
+ 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 AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND AcctStopTime IS NULL"
+ }
+
+ stop {
+ query = "\
+ UPDATE ${....acct_table2} \
+ SET \
+ AcctStopTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
+ (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
+ 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}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
+ DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND AcctStopTime IS NULL"
+
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \
+ '%{NAS-Port-Type}', \
+ TO_TIMESTAMP(${....event_timestamp_epoch} - %{%{Acct-Session-Time}:-0}), \
+ ${....event_timestamp}, \
+ ${....event_timestamp}, \
+ NULLIF('%{Acct-Session-Time}', '')::bigint, \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ NULL, \
+ (('%{%{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, \
+ NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ NULLIF('%{Framed-Interface-Id}', ''), \
+ NULLIF('%{Delegated-IPv6-Prefix}', '')::inet \
+ ${....class.packet_xlat}) \
+ ON CONFLICT (AcctUniqueId) \
+ DO NOTHING"
+
+ #
+ # When using "sql_session_start", you should comment out
+ # the previous query, and enable this one.
+ #
+ # Just change the previous query to "-query",
+ # and this one to "query". The previous one
+ # will be ignored, and this one will be
+ # enabled.
+ #
+ -query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctSessionId = '%{Acct-Session-Id}', \
+ AcctUniqueId = '%{Acct-Unique-Session-Id}', \
+ AcctAuthentic = '%{Acct-Authentic}', \
+ ConnectInfo_start = '%{Connect-Info}', \
+ ServiceType = '%{Service-Type}', \
+ FramedProtocol = '%{Framed-Protocol}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
+ DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
+ AcctStopTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
+ (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
+ 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}', \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' \
+ AND AcctStopTime IS NULL"
+
+ # and again where we don't have "AND AcctStopTime IS NULL"
+ query = "\
+ UPDATE ${....acct_table2} \
+ SET \
+ AcctStopTime = ${....event_timestamp}, \
+ AcctUpdateTime = ${....event_timestamp}, \
+ AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
+ (${....event_timestamp_epoch} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
+ 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}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, \
+ FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, \
+ FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), \
+ DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
+ }
+
+ #
+ # No Acct-Status-Type == ignore the packet
+ #
+ accounting {
+ query = "SELECT true"
+ }
+ }
+}
+
+
+#######################################################################
+# Authentication Logging Queries
+#######################################################################
+# postauth_query - Insert some info after authentication
+#######################################################################
+
+post-auth {
+ # Write SQL queries to a logfile. This is potentially useful for bulk inserts
+ # when used with the rlm_sql_null driver.
+# logfile = ${logdir}/post-auth.sql
+
+ query = "\
+ INSERT INTO ${..postauth_table} \
+ (username, pass, reply, authdate ${..class.column_name}) \
+ VALUES(\
+ '%{User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', \
+ '%S.%M' \
+ ${..class.reply_xlat})"
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-21 21:38:55 +0000