Don't include expired keys in the psk_keys table

FreeRADIUS directly queries psk_keys.  It's important that it not
match expired keys.  Instead create a table psk_keys_tab and make
psk_keys a view that excludes expired keys.

diff --git a/schema.sql b/schema.sql
index 1e0a5f9..752fe2c 100644 (file)
--- a/schema.sql
+++ b/schema.sql
@@ -1,7 +1,8 @@
-create table if not exists psk_keys (keyid text primary key, key blob, client_dh_pub raw(20), key_expiration timestamp);
+create table if not exists psk_keys_tab(keyid text primary key, key blob, client_dh_pub raw(20), key_expiration timestamp);
 create table if not exists authorizations( client_dh_pub raw(20), coi string, acceptor_realm string, hostname string, apc string);
 create index if not exists authorizations_dhpub on authorizations( client_dh_pub);
-CREATE VIEW if not exists authorizations_keys as select keyid, authorizations.* from psk_keys join authorizations on psk_keys.client_dh_pub = authorizations.client_dh_pub and datetime(key_expiration) > datetime('now');
+create view if not exists psk_keys as select * from psk_keys_tab where  datetime(key_expiration) > datetime('now');
+CREATE VIEW if not exists authorizations_keys as select keyid, authorizations.* from psk_keys join authorizations on psk_keys.client_dh_pub = authorizations.client_dh_pub;
  
 
 .quit