From 7d02fc90249afe60bb05120eb2b34ad2eae843cc Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Fri, 25 Nov 2022 23:23:10 +0100
Subject: [PATCH] [matrix] Use Postgres as the database.

Matrix automatically generates and populates necessary tables.
---
 databases/matrix/matrix_db_create.sql | 14 ++++++++++++++
 databases/matrix/matrix_db_drop.sql   |  9 +++++++++
 matrix/homeserver.yaml                |  8 +++++---
 postgres/pg_hba.conf                  |  1 +
 4 files changed, 29 insertions(+), 3 deletions(-)
 create mode 100644 databases/matrix/matrix_db_create.sql
 create mode 100644 databases/matrix/matrix_db_drop.sql

diff --git a/databases/matrix/matrix_db_create.sql b/databases/matrix/matrix_db_create.sql
new file mode 100644
index 0000000..857db5d
--- /dev/null
+++ b/databases/matrix/matrix_db_create.sql
@@ -0,0 +1,14 @@
+DO $$
+BEGIN
+    IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'synapse_user')
+    THEN
+        CREATE USER synapse_user;
+    END IF;
+END$$;
+
+CREATE DATABASE matrix_db
+    ENCODING 'UTF8'
+    LC_COLLATE='C'
+    LC_CTYPE='C'
+    TEMPLATE=template0
+    OWNER synapse_user;
diff --git a/databases/matrix/matrix_db_drop.sql b/databases/matrix/matrix_db_drop.sql
new file mode 100644
index 0000000..4ae4e8a
--- /dev/null
+++ b/databases/matrix/matrix_db_drop.sql
@@ -0,0 +1,9 @@
+DROP DATABASE matrix_db;
+
+DO $$
+BEGIN
+    IF EXISTS (SELECT * FROM pg_user WHERE usename = 'synapse_user')
+    THEN
+        DROP USER synapse_user;
+    END IF;
+END$$;
diff --git a/matrix/homeserver.yaml b/matrix/homeserver.yaml
index 1ed7f77..d842ab4 100644
--- a/matrix/homeserver.yaml
+++ b/matrix/homeserver.yaml
@@ -312,11 +312,13 @@ listeners:
 # Database configuration
 database:
   # The database engine name
-  name: "sqlite3"
+  name: "psycopg2"
   # Arguments to pass to the engine
   args:
-    # Path to the database
-    database: "/var/lib/matrix-synapse/homeserver.db"
+    user: "synapse_user"
+    database: "matrix_db"
+    cp_min: 5
+    cp_max: 10
 
 # Number of events to cache in memory.
 event_cache_size: "10K"
diff --git a/postgres/pg_hba.conf b/postgres/pg_hba.conf
index 2b6bc56..81e5670 100644
--- a/postgres/pg_hba.conf
+++ b/postgres/pg_hba.conf
@@ -93,6 +93,7 @@ local   all             postgres                                peer
 local   mail_db         dovecot                                 trust
 host    mail_db         dovecot         ::1/128                 trust
 host    mail_db         dkim            ::1/128                 trust
+local   matrix_db       synapse_user                            trust
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
-- 
2.39.5