diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool/postgresql/queries.conf')
| -rw-r--r-- | raddb/mods-config/sql/ippool/postgresql/queries.conf | 207 |
1 files changed, 207 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool/postgresql/queries.conf b/raddb/mods-config/sql/ippool/postgresql/queries.conf new file mode 100644 index 0000000..ce6f355 --- /dev/null +++ b/raddb/mods-config/sql/ippool/postgresql/queries.conf @@ -0,0 +1,207 @@ +# -*- text -*- +# +# ippool/postgresql/queries.conf -- PostgreSQL queries for rlm_sqlippool +# +# $Id$ + + +# Using SKIP LOCKED speeds up selection queries +# However, it requires PostgreSQL >= 9.5 Uncomment the +# following if you are running a suitable version of PostgreSQL +# +#skip_locked = "SKIP LOCKED" +skip_locked = "" + +# +# This series of queries allocates an IP address +# + +# +# The suggested queries locate IPs and update them in one query +# so no need for transaction wrappers +# +allocate_begin = "" +allocate_commit = "" + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + WITH cte AS ( \ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ORDER BY expiry_time DESC \ + LIMIT 1 \ + FOR UPDATE ${skip_locked} \ + ) \ + UPDATE ${ippool_table} \ + SET \ + nasipaddress = '%{NAS-IP-Address}', \ + pool_key = '${pool_key}', \ + callingstationid = '%{Calling-Station-Id}', \ + username = '%{SQL-User-Name}', \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ + FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \ + RETURNING cte.framedipaddress" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + WITH cte AS ( \ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < 'now'::timestamp(0) \ + ORDER BY expiry_time \ + LIMIT 1 \ + FOR UPDATE ${skip_locked} \ + ) \ + UPDATE ${ippool_table} \ + SET \ + nasipaddress = '%{NAS-IP-Address}', \ + pool_key = '${pool_key}', \ + callingstationid = '%{Calling-Station-Id}', \ + username = '%{SQL-User-Name}', \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ + FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \ + RETURNING cte.framedipaddress" + +# +# If you prefer to allocate a random IP address every time, use this query instead +# Note: This is very slow if you have a lot of free IPs. +# +#allocate_find = "\ +# WITH cte AS ( \ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < 'now'::timestamp(0) \ +# ORDER BY RANDOM() \ +# LIMIT 1 \ +# FOR UPDATE ${skip_locked} \ +# ) \ +# UPDATE ${ippool_table} \ +# SET \ +# nasipaddress = '%{NAS-IP-Address}', \ +# pool_key = '${pool_key}', \ +# callingstationid = '%{Calling-Station-Id}', \ +# username = '%{SQL-User-Name}', \ +# expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ +# FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \ +# RETURNING cte.framedipaddress" + +# +# If an IP could not be allocated, check to see whether the pool exists or not +# This allows the module to differentiate between a full pool and no pool +# Note: If you are not running redundant pool modules this query may be commented +# out to save running this query every time an ip is not allocated. +# +pool_check = "\ + SELECT id \ + FROM ${ippool_table} \ + WHERE pool_name='%{control:${pool_name}}' \ + LIMIT 1" + +# +# This query marks the IP address handed out by "allocate-find" as used +# for the period of "lease_duration" after which time it may be reused. +# This is only needed if the allocate_existing / allocate_find queries +# do not update the pool +# +#allocate_update = "\ +# UPDATE ${ippool_table} \ +# SET \ +# nasipaddress = '%{NAS-IP-Address}', \ +# pool_key = '${pool_key}', \ +# callingstationid = '%{Calling-Station-Id}', \ +# username = '%{SQL-User-Name}', \ +# expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ +# WHERE framedipaddress = '%I'" + +# +# Use a stored procedure to find AND allocate the address. Read and customise +# `procedure.sql` in this directory to determine the optimal configuration. +# +# This requires PostgreSQL >= 9.5 as SKIP LOCKED is used. +# +# The "NO LOAD BALANCE" comment is included here to indicate to a PgPool +# system that this needs to be a write transaction. PgPool itself cannot +# detect this from the statement alone. If you are using PgPool and do not +# have this comment, the query may go to a read only server, and will fail. +# This has no negative effect if you are not using PgPool. +# +#allocate_begin = "" +#allocate_find = "\ +# /*NO LOAD BALANCE*/ \ +# SELECT fr_allocate_previous_or_new_framedipaddress( \ +# '%{control:${pool_name}}', \ +# '%{SQL-User-Name}', \ +# '%{Calling-Station-Id}', \ +# '%{NAS-IP-Address}', \ +# '${pool_key}', \ +# '${lease_duration}' \ +# )" +#allocate_update = "" +#allocate_commit = "" + +# +# This query extends an IP address lease by "lease_duration" when an accounting +# START record arrives +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ + WHERE nasipaddress = '%{NAS-IP-Address}' \ + AND pool_key = '${pool_key}'" + +# +# This query expires an IP address when an accounting +# STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# This query extends an IP address lease by "lease_duration" when an accounting +# ALIVE record arrives +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} seconds'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{${attribute_name}}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}'" + +# +# This query expires all IP addresses allocated to a NAS when an +# accounting ON record arrives from that NAS +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# This query expires all IP addresses allocated to a NAS when an +# accounting OFF record arrives from that NAS +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" |