From 7c1d77c753a3396a5febbfdcef102e249df2342f Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Sun, 14 Jan 2024 18:28:31 +0100
Subject: [PATCH] Log requests as json to a separate log file.

---
 COPYING      |  4 ++--
 create.sql   | 24 ++++++++++++++++++++++++
 drop.sql     | 21 +++++++++++++++++++++
 website.conf | 21 ++++++++++++++++++++-
 4 files changed, 67 insertions(+), 3 deletions(-)
 create mode 100644 create.sql
 create mode 100644 drop.sql

diff --git a/COPYING b/COPYING
index 9d0bdd7..cd8989e 100644
--- a/COPYING
+++ b/COPYING
@@ -631,7 +631,7 @@ to attach them to the start of each source file to most effectively
 state the exclusion of warranty; and each file should have at least
 the "copyright" line and a pointer to where the full notice is found.
 
-    Copyright (C) 2023 Jakub Czajka <jakub@ekhem.eu.org>
+    Copyright (C) 2023-2024 Jakub Czajka <jakub@ekhem.eu.org>
 
     This program is free software: you can redistribute it and/or modify
     it under the terms of the GNU General Public License as published by
@@ -651,7 +651,7 @@ Also add information on how to contact you by electronic and paper mail.
   If the program does terminal interaction, make it output a short
 notice like this when it starts in an interactive mode:
 
-    website Copyright (C) 2023 Jakub Czajka <jakub@ekhem.eu.org>
+    website Copyright (C) 2023-2024 Jakub Czajka <jakub@ekhem.eu.org>
     This program comes with ABSOLUTELY NO WARRANTY; for details type `show w'.
     This is free software, and you are welcome to redistribute it
     under certain conditions; type `show c' for details.
diff --git a/create.sql b/create.sql
new file mode 100644
index 0000000..59d7312
--- /dev/null
+++ b/create.sql
@@ -0,0 +1,24 @@
+/* Copyright (c) 2024 Jakub Czajka <jakub@ekhem.eu.org>
+   License: GPL-3.0 or later. */
+
+CREATE TABLE IF NOT EXISTS access_logs (
+    access_log TEXT NOT NULL,
+    created_at DATE NOT NULL
+);
+
+DO $$
+BEGIN
+    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'rsyslog')
+    THEN
+      CREATE ROLE rsyslog LOGIN;
+
+      GRANT INSERT
+      ON access_logs
+      TO rsyslog;
+
+      /* Execute for the current database. */
+      EXECUTE FORMAT('GRANT CONNECT
+                      ON DATABASE %I
+                      TO rsyslog', current_database());
+    END IF;
+END$$;
diff --git a/drop.sql b/drop.sql
new file mode 100644
index 0000000..55adc27
--- /dev/null
+++ b/drop.sql
@@ -0,0 +1,21 @@
+/* Copyright (c) 2024 Jakub Czajka <jakub@ekhem.eu.org>
+   License: GPL-3.0 or later. */
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT * FROM pg_user WHERE usename = 'rsyslog')
+    THEN
+        REVOKE INSERT
+        ON access_logs
+        FROM rsyslog;
+
+        EXECUTE
+            FORMAT('REVOKE CONNECT
+                    ON DATABASE %I
+                    FROM rsyslog;', current_database());
+
+        DROP ROLE rsyslog;
+    END IF;
+END$$;
+
+DROP TABLE IF EXISTS access_logs;
diff --git a/website.conf b/website.conf
index c61906e..6858ad5 100644
--- a/website.conf
+++ b/website.conf
@@ -1,6 +1,19 @@
-# Copyright (c) 2023 Jakub Czajka <jakub@ekhem.eu.org>
+# Copyright (c) 2023-2024 Jakub Czajka <jakub@ekhem.eu.org>
 # License: GPL-3.0 or later.
 
+log_format as_json '{"time": "${dollar}time_iso8601", '
+   '"ip": "${dollar}remote_addr", '
+   '"request": "${dollar}request", '
+   '"path": "${dollar}request_uri", '
+   '"status": "${dollar}status", '
+   '"user_agent": "${dollar}http_user_agent", '
+   '"user_id_got": "${dollar}uid_got", '
+   '"user_id_set": "${dollar}uid_set", '
+   '"remote_user": "${dollar}remote_user", '
+   '"body_bytes_sent": "${dollar}body_bytes_sent", '
+   '"request_time": "${dollar}request_time", '
+   '"http_referrer": "${dollar}http_referer" }';
+
 server {
     server_name www.${public_domain} ${public_domain};
 
@@ -10,6 +23,12 @@ server {
     ssl_certificate ${public_ssl_cert_dir}/fullchain.pem;
     ssl_certificate_key ${public_ssl_cert_dir}/privkey.pem;
 
+    userid on;
+    userid_name uid;
+    userid_expires 365d;
+
+    access_log ${website_log_file} as_json;
+
     location /cv {
         root ${prod_dir}/cv;
         rewrite ^ /cv.pdf break;
-- 
2.39.5