2 files changed, 40 insertions, 0 deletions
diff --git a/db/postgres/security_rss_keyword.sql b/db/postgres/security_rss_keyword.sql
new file mode 100644
index 0000000..6026246
--- /dev/null
+++ b/db/postgres/security_rss_keyword.sql
@@ -0,0 +1,24 @@
+create table if not exists security_rss_keyword_store (
+    id serial primary key not null,
+    logdate timestamp without time zone default now(),
+    nick varchar(128) not null,
+    log varchar(1024) not null,
+    channel varchar(256) not null
+);
+
+create or replace function security_rss_trigger_function()
+returns trigger
+language plpgsql
+as $$
+begin
+    if NEW.log like any(array['%nvidia%','%intel%','%dell%']) then
+        insert into security_rss_keyword_store(nick,log,channel)
+        values (NEW.nick,NEW.log,NEW.channel);
+end;
+$$
+
+create or replace trigger security_rss_trigger
+after insert or update
+on public.logs
+for each row
+execute function security_rss_trigger_function();
diff --git a/db/postgres/strip_ascii_escape.pgsql b/db/postgres/strip_ascii_escape.pgsql
new file mode 100644
index 0000000..00714f6
--- /dev/null
+++ b/db/postgres/strip_ascii_escape.pgsql
@@ -0,0 +1,16 @@
+create or replace function strip_ascii_escape() returns trigger
+language plpgsql as $$
+    begin
+        NEW.log = regexp_replace(NEW.log,'\x1b\[[\x30-\x3f]*[\x20-\x2f]*[\x40-\x7e]','','g');
+        NEW.log = regexp_replace(NEW.log,'\x1b[PX^_].*?\x1b\\','','g');
+        NEW.log = regexp_replace(NEW.log,'\x1b\][^\a]*(?:\a|\x1b\\)','','g');
+        NEW.log = regexp_replace(NEW.log,'\x1b[\[\]A-Z\\^_@]','','g');
+        NEW.log = regexp_replace(NEW.log,'[\x00-\x1f\x7f-\x9f\xad]+','','g');
+
+        return NEW;
+    end;
+$$;
+
+create trigger strip_ascii_escape
+before insert on public.logs
+for each row execute function strip_ascii_escape()