| # -*- text -*- |
| # |
| # main/postgresql/queries.conf -- PostgreSQL configuration for default schema (schema.sql) |
| # |
| # $Id: 0f2a29afff36136bb171a9a97ee90199b017e46c $ |
| |
| # 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" |
| |
| ####################################################################### |
| # 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 = '%{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. |
| ####################################################################### |
| |
| # |
| # 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" |
| |
| ####################################################################### |
| # 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}:-none}.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" |
| |
| type { |
| accounting-on { |
| query = "\ |
| UPDATE ${....acct_table1} \ |
| SET \ |
| AcctStopTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctSessionTime = (%{integer:Event-Timestamp} - EXTRACT(EPOCH FROM(AcctStartTime))), \ |
| AcctTerminateCause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \ |
| WHERE AcctStopTime IS NULL \ |
| AND NASIPAddress= '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ |
| AND AcctStartTime <= '%S'::timestamp" |
| } |
| |
| accounting-off { |
| query = "${..accounting-on.query}" |
| } |
| |
| 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}}', \ |
| %{%{NAS-Port}:-NULL}, \ |
| '%{NAS-Port-Type}', \ |
| TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| TO_TIMESTAMP(%{integer: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)" |
| |
| query = "\ |
| UPDATE ${....acct_table1} \ |
| SET \ |
| AcctStartTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| ConnectInfo_start = '%{Connect-Info}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ |
| AND AcctStopTime IS NULL" |
| |
| # and again where we don't have "AND AcctStopTime IS NULL" |
| query = "\ |
| UPDATE ${....acct_table1} \ |
| SET \ |
| AcctStartTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| ConnectInfo_start = '%{Connect-Info}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}'" |
| } |
| |
| interim-update { |
| query = "\ |
| UPDATE ${....acct_table1} \ |
| SET \ |
| FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ |
| AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \ |
| AcctInterval = (%{integer:Event-Timestamp} - EXTRACT(EPOCH FROM (COALESCE(AcctUpdateTime, AcctStartTime)))), \ |
| AcctUpdateTime = TO_TIMESTAMP(%{integer: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 AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress= '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ |
| 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}}', \ |
| %{%{NAS-Port}:-NULL}, \ |
| '%{NAS-Port-Type}', \ |
| TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| TO_TIMESTAMP(%{integer: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)" |
| } |
| |
| stop { |
| query = "\ |
| UPDATE ${....acct_table2} \ |
| SET \ |
| AcctStopTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \ |
| (%{integer:Event-Timestamp} - 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, \ |
| ConnectInfo_stop = '%{Connect-Info}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ |
| 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}}', \ |
| %{%{NAS-Port}:-NULL}, \ |
| '%{NAS-Port-Type}', \ |
| TO_TIMESTAMP(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \ |
| TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| TO_TIMESTAMP(%{integer: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)" |
| |
| # and again where we don't have "AND AcctStopTime IS NULL" |
| query = "\ |
| UPDATE ${....acct_table2} \ |
| SET \ |
| AcctStopTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \ |
| AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \ |
| (%{integer:Event-Timestamp} - 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, \ |
| ConnectInfo_stop = '%{Connect-Info}' \ |
| WHERE AcctSessionId = '%{Acct-Session-Id}' \ |
| AND UserName = '%{SQL-User-Name}' \ |
| AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}'" |
| } |
| |
| # |
| # No Acct-Status-Type == ignore the packet |
| # |
| none { |
| 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) \ |
| VALUES(\ |
| '%{User-Name}', \ |
| '%{%{User-Password}:-Chap-Password}', \ |
| '%{reply:Packet-Type}', \ |
| NOW())" |
| } |