aboutsummaryrefslogtreecommitdiffstats
path: root/db/postgres
diff options
context:
space:
mode:
Diffstat (limited to 'db/postgres')
-rw-r--r--db/postgres/security_rss_keyword.sql24
-rw-r--r--db/postgres/strip_ascii_escape.pgsql16
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()