From 75695d3e42bfe15483cefa43f316a4ae11a3bcca Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Wed, 21 May 2025 09:24:30 +0200 Subject: ... --- .../ccert_policy_server/__main__.py | 7 +- hosts/surtr/email/default.nix | 105 ++++++++++++++---- hosts/surtr/email/internal-policy-server/.envrc | 4 + .../surtr/email/internal-policy-server/.gitignore | 2 + .../internal_policy_server/__init__.py | 0 .../internal_policy_server/__main__.py | 106 ++++++++++++++++++ .../email/internal-policy-server/pyproject.toml | 18 ++++ hosts/surtr/email/internal-policy-server/uv.lock | 119 +++++++++++++++++++++ hosts/surtr/postgresql/default.nix | 17 +++ 9 files changed, 354 insertions(+), 24 deletions(-) create mode 100644 hosts/surtr/email/internal-policy-server/.envrc create mode 100644 hosts/surtr/email/internal-policy-server/.gitignore create mode 100644 hosts/surtr/email/internal-policy-server/internal_policy_server/__init__.py create mode 100644 hosts/surtr/email/internal-policy-server/internal_policy_server/__main__.py create mode 100644 hosts/surtr/email/internal-policy-server/pyproject.toml create mode 100644 hosts/surtr/email/internal-policy-server/uv.lock (limited to 'hosts') diff --git a/hosts/surtr/email/ccert-policy-server/ccert_policy_server/__main__.py b/hosts/surtr/email/ccert-policy-server/ccert_policy_server/__main__.py index 00182523..7117eb63 100644 --- a/hosts/surtr/email/ccert-policy-server/ccert_policy_server/__main__.py +++ b/hosts/surtr/email/ccert-policy-server/ccert_policy_server/__main__.py @@ -44,10 +44,9 @@ class PolicyHandler(StreamRequestHandler): with conn.cursor() as cur: cur.row_factory = namedtuple_row - cur.execute('SELECT "mailbox"."mailbox" as "user", "local", "extension", "domain" FROM "mailbox" INNER JOIN "mailbox_mapping" ON "mailbox".id = "mailbox_mapping"."mailbox" WHERE "mailbox"."mailbox" = %(user)s AND ("local" = %(local)s OR "local" IS NULL) AND ("extension" = %(extension)s OR "extension" IS NULL) AND "domain" = %(domain)s', params = {'user': user, 'local': local, 'extension': extension if extension is not None else '', 'domain': domain}, prepare=True) - for record in cur: - logger.debug('Received result: %s', record) - allowed = True + cur.execute('SELECT EXISTS(SELECT true FROM "mailbox" INNER JOIN "mailbox_mapping" ON "mailbox".id = "mailbox_mapping"."mailbox" WHERE "mailbox"."mailbox" = %(user)s AND ("local" = %(local)s OR "local" IS NULL) AND ("extension" = %(extension)s OR "extension" IS NULL) AND "domain" = %(domain)s) as "exists"', params = {'user': user, 'local': local, 'extension': extension if extension is not None else '', 'domain': domain}, prepare=True) + if (row := cur.fetchone()) is not None: + allowed = row.exists action = '550 5.7.0 Sender address not authorized for current user' if allowed: diff --git a/hosts/surtr/email/default.nix b/hosts/surtr/email/default.nix index 845f6455..c6253e4c 100644 --- a/hosts/surtr/email/default.nix +++ b/hosts/surtr/email/default.nix @@ -1,4 +1,4 @@ -{ config, pkgs, lib, flakeInputs, ... }: +{ config, pkgs, lib, flake, flakeInputs, ... }: with lib; @@ -15,7 +15,7 @@ let for file in $out/pipe/bin/*; do wrapProgram $file \ - --set PATH "${pkgs.coreutils}/bin:${pkgs.rspamd}/bin" + --set PATH "${makeBinPath (with pkgs; [coreutils rspamd])}" done ''; }; @@ -33,12 +33,28 @@ let }); }); }; + internal-policy-server = + let + workspace = flakeInputs.uv2nix.lib.workspace.loadWorkspace { workspaceRoot = ./internal-policy-server; }; + pythonSet = flake.lib.pythonSet { + inherit pkgs; + python = pkgs.python312; + overlay = workspace.mkPyprojectOverlay { + sourcePreference = "wheel"; + }; + }; + virtualEnv = pythonSet.mkVirtualEnv "internal-policy-server-env" workspace.deps.default; + in virtualEnv.overrideAttrs (oldAttrs: { + meta = (oldAttrs.meta or {}) // { + mainProgram = "internal-policy-server"; + }; + }); - nftables-nologin-script = pkgs.writeScript "nftables-mail-nologin" '' - #!${pkgs.zsh}/bin/zsh - + nftables-nologin-script = pkgs.resholve.writeScript "nftables-mail-nologin" { + inputs = with pkgs; [inetutils nftables gnugrep findutils]; + interpreter = lib.getExe pkgs.zsh; + } '' set -e - export PATH="${lib.makeBinPath (with pkgs; [inetutils nftables])}:$PATH" typeset -a as_sets mnt_bys route route6 as_sets=(${lib.escapeShellArgs config.services.email.nologin.ASSets}) @@ -51,7 +67,7 @@ let elif [[ "''${line}" =~ "^route6:\s+(.+)$" ]]; then route6+=($match[1]) fi - done < <(whois -h whois.radb.net "!i''${as_set},1" | egrep -o 'AS[0-9]+' | xargs -- whois -h whois.radb.net -- -i origin) + done < <(whois -h whois.radb.net "!i''${as_set},1" | grep -Eo 'AS[0-9]+' | xargs whois -h whois.radb.net -- -i origin) done for mnt_by in $mnt_bys; do while IFS=$'\n' read line; do @@ -190,6 +206,7 @@ in { "reject_unauth_destination" "reject_unknown_recipient_domain" "reject_unverified_recipient" + "check_policy_service unix:/run/postfix-internal-policy.sock" ]; unverified_recipient_reject_code = "550"; unverified_recipient_reject_reason = "Recipient address lookup failed"; @@ -251,7 +268,7 @@ in { virtual_transport = "dvlmtp:unix:/run/dovecot-lmtp"; smtputf8_enable = false; - authorized_submit_users = "inline:{ root= postfwd= }"; + authorized_submit_users = "inline:{ root= postfwd= dovecot2= }"; postscreen_access_list = ""; postscreen_denylist_action = "drop"; @@ -280,7 +297,7 @@ in { hosts = postgresql:///email dbname = email query = SELECT action FROM virtual_mailbox_access WHERE lookup = '%s' OR (lookup = regexp_replace('%s', '\+[^@]*@', '@') AND NOT EXISTS (SELECT 1 FROM virtual_mailbox_access WHERE lookup = '%s')) - ''},permit_tls_all_clientcerts,reject}'' + ''},check_policy_service unix:/run/postfix-internal-policy.sock,permit_tls_all_clientcerts,reject}'' "-o" "smtpd_relay_restrictions=permit_tls_all_clientcerts,reject" "-o" "{smtpd_data_restrictions = check_policy_service unix:/run/postfwd3/postfwd3.sock}" "-o" "unverified_sender_reject_code=550" @@ -310,7 +327,7 @@ in { hosts = postgresql:///email dbname = email query = SELECT action FROM virtual_mailbox_access WHERE lookup = '%s' OR (lookup = regexp_replace('%s', '\+[^@]*@', '@') AND NOT EXISTS (SELECT 1 FROM virtual_mailbox_access WHERE lookup = '%s')) - ''},permit_sasl_authenticated,reject}'' + ''},check_policy_service unix:/run/postfix-internal-policy.sock,permit_sasl_authenticated,reject}'' "-o" "smtpd_relay_restrictions=permit_sasl_authenticated,reject" "-o" "{smtpd_data_restrictions = check_policy_service unix:/run/postfwd3/postfwd3.sock}" "-o" "unverified_sender_reject_code=550" @@ -672,7 +689,7 @@ in { plugin { plugin = fts fts_xapian fts = xapian - fts_xapian = partial=2 full=20 attachments=1 verbose=1 + fts_xapian = partial=3 full=20 attachments=1 verbose=1 fts_autoindex = yes @@ -692,7 +709,7 @@ in { startAt = "*-*-* 22:00:00 Europe/Berlin"; serviceConfig = { Type = "oneshot"; - ExecStart = "${pkgs.dovecot}/bin/doveadm fts optimize -A"; + ExecStart = "${getExe' pkgs.dovecot "doveadm"} fts optimize -A"; PrivateDevices = true; PrivateNetwork = true; ProtectKernelTunables = true; @@ -777,7 +794,7 @@ in { systemd.services.dovecot2 = { preStart = '' for f in /etc/dovecot/sieve_flag.d/*.sieve /etc/dovecot/sieve_before.d/*.sieve; do - ${pkgs.dovecot_pigeonhole}/bin/sievec $f + ${getExe' pkgs.dovecot_pigeonhole "sievec"} $f done ''; @@ -844,15 +861,16 @@ in { charset utf-8; source_charset utf-8; ''; - root = pkgs.runCommand "mta-sts.${domain}" {} '' - mkdir -p $out/.well-known - cp ${pkgs.writeText "mta-sts.${domain}.txt" '' + root = pkgs.writeTextFile { + name = "mta-sts.${domain}"; + destination = "/.well-known/mta-sts.txt"; + text = '' version: STSv1 mode: enforce max_age: 2419200 mx: mailin.${domain} - ''} $out/.well-known/mta-sts.txt - ''; + ''; + }; }; }) emailDomains); }; @@ -869,7 +887,7 @@ in { systemd.services.spm = { serviceConfig = { Type = "notify"; - ExecStart = "${pkgs.spm}/bin/spm-server"; + ExecStart = getExe' pkgs.spm "spm-server"; User = "spm"; Group = "spm"; @@ -927,7 +945,7 @@ in { serviceConfig = { Type = "notify"; - ExecStart = "${ccert-policy-server}/bin/ccert-policy-server"; + ExecStart = getExe' ccert-policy-server "ccert-policy-server"; Environment = [ "PGDATABASE=email" @@ -960,6 +978,53 @@ in { }; users.groups."postfix-ccert-sender-policy" = {}; + systemd.sockets."postfix-internal-policy" = { + requiredBy = ["postfix.service"]; + wants = ["postfix-internal-policy.service"]; + socketConfig = { + ListenStream = "/run/postfix-internal-policy.sock"; + }; + }; + systemd.services."postfix-internal-policy" = { + after = [ "postgresql.service" ]; + bindsTo = [ "postgresql.service" ]; + + serviceConfig = { + Type = "notify"; + + ExecStart = lib.getExe internal-policy-server; + + Environment = [ + "PGDATABASE=email" + ]; + + DynamicUser = false; + User = "postfix-internal-policy"; + Group = "postfix-internal-policy"; + ProtectSystem = "strict"; + SystemCallFilter = "@system-service"; + NoNewPrivileges = true; + ProtectKernelTunables = true; + ProtectKernelModules = true; + ProtectKernelLogs = true; + ProtectControlGroups = true; + MemoryDenyWriteExecute = true; + RestrictSUIDSGID = true; + KeyringMode = "private"; + ProtectClock = true; + RestrictRealtime = true; + PrivateDevices = true; + PrivateTmp = true; + ProtectHostname = true; + ReadWritePaths = ["/run/postgresql"]; + }; + }; + users.users."postfix-internal-policy" = { + isSystemUser = true; + group = "postfix-internal-policy"; + }; + users.groups."postfix-internal-policy" = {}; + services.postfwd = { enable = true; cache = false; diff --git a/hosts/surtr/email/internal-policy-server/.envrc b/hosts/surtr/email/internal-policy-server/.envrc new file mode 100644 index 00000000..2c909235 --- /dev/null +++ b/hosts/surtr/email/internal-policy-server/.envrc @@ -0,0 +1,4 @@ +use flake + +[[ -d ".venv" ]] || ( uv venv && uv sync ) +. .venv/bin/activate diff --git a/hosts/surtr/email/internal-policy-server/.gitignore b/hosts/surtr/email/internal-policy-server/.gitignore new file mode 100644 index 00000000..4ccfae70 --- /dev/null +++ b/hosts/surtr/email/internal-policy-server/.gitignore @@ -0,0 +1,2 @@ +.venv +**/__pycache__ diff --git a/hosts/surtr/email/internal-policy-server/internal_policy_server/__init__.py b/hosts/surtr/email/internal-policy-server/internal_policy_server/__init__.py new file mode 100644 index 00000000..e69de29b diff --git a/hosts/surtr/email/internal-policy-server/internal_policy_server/__main__.py b/hosts/surtr/email/internal-policy-server/internal_policy_server/__main__.py new file mode 100644 index 00000000..04f1a59a --- /dev/null +++ b/hosts/surtr/email/internal-policy-server/internal_policy_server/__main__.py @@ -0,0 +1,106 @@ +from systemd.daemon import listen_fds +from sdnotify import SystemdNotifier +from socketserver import StreamRequestHandler, ThreadingMixIn +from systemd_socketserver import SystemdSocketServer +import sys +from threading import Thread +from psycopg_pool import ConnectionPool +from psycopg.rows import namedtuple_row + +import logging + + +class PolicyHandler(StreamRequestHandler): + def handle(self): + logger.debug('Handling new connection...') + + self.args = dict() + + line = None + while line := self.rfile.readline().removesuffix(b'\n'): + if b'=' not in line: + break + + key, val = line.split(sep=b'=', maxsplit=1) + self.args[key.decode()] = val.decode() + + logger.info('Connection parameters: %s', self.args) + + allowed = False + user = None + if self.args['sasl_username']: + user = self.args['sasl_username'] + if self.args['ccert_subject']: + user = self.args['ccert_subject'] + + with self.server.db_pool.connection() as conn: + local, domain = self.args['recipient'].split(sep='@', maxsplit=1) + extension = None + if '+' in local: + local, extension = local.split(sep='+', maxsplit=1) + + logger.debug('Parsed recipient address: %s', {'local': local, 'extension': extension, 'domain': domain}) + + with conn.cursor() as cur: + cur.row_factory = namedtuple_row + cur.execute('SELECT id, internal FROM "mailbox_mapping" WHERE ("local" = %(local)s OR "local" IS NULL) AND ("extension" = %(extension)s OR "extension" IS NULL) AND "domain" = %(domain)s', params = {'local': local, 'extension': extension if extension is not None else '', 'domain': domain}, prepare = True) + if (row := cur.fetchone()) is not None: + if not row.internal: + logger.debug('Recipient mailbox is not internal') + allowed = True + elif user: + cur.execute('SELECT EXISTS(SELECT true FROM "mailbox_mapping_access" INNER JOIN "mailbox" ON "mailbox".id = "mailbox_mapping_access"."mailbox" WHERE mailbox_mapping = %(mailbox_mapping)s AND "mailbox"."mailbox" = %(user)s) as "exists"', params = { 'mailbox_mapping': row.id, 'user': user }, prepare = True) + if (row := cur.fetchone()) is not None: + allowed = row.exists + else: + logger.debug('Recipient is not local') + allowed = True + + action = '550 5.7.0 Recipient mailbox mapping not authorized for current user' + if allowed: + action = 'DUNNO' + + logger.info('Reached verdict: %s', {'allowed': allowed, 'action': action}) + self.wfile.write(f'action={action}\n\n'.encode()) + +class ThreadedSystemdSocketServer(ThreadingMixIn, SystemdSocketServer): + def __init__(self, fd, RequestHandlerClass): + super().__init__(fd, RequestHandlerClass) + + self.db_pool = ConnectionPool(min_size=1) + self.db_pool.wait() + +def main(): + global logger + logger = logging.getLogger(__name__) + console_handler = logging.StreamHandler() + console_handler.setFormatter( logging.Formatter('[%(levelname)s](%(name)s): %(message)s') ) + if sys.stderr.isatty(): + console_handler.setFormatter( logging.Formatter('%(asctime)s [%(levelname)s](%(name)s): %(message)s') ) + logger.addHandler(console_handler) + logger.setLevel(logging.DEBUG) + + # log uncaught exceptions + def log_exceptions(type, value, tb): + global logger + + logger.error(value) + sys.__excepthook__(type, value, tb) # calls default excepthook + + sys.excepthook = log_exceptions + + fds = listen_fds() + servers = [ThreadedSystemdSocketServer(fd, PolicyHandler) for fd in fds] + + if servers: + for server in servers: + Thread(name=f'Server for fd{server.fileno()}', target=server.serve_forever).start() + else: + return 2 + + SystemdNotifier().notify('READY=1') + + return 0 + +if __name__ == '__main__': + sys.exit(main()) diff --git a/hosts/surtr/email/internal-policy-server/pyproject.toml b/hosts/surtr/email/internal-policy-server/pyproject.toml new file mode 100644 index 00000000..c697cd01 --- /dev/null +++ b/hosts/surtr/email/internal-policy-server/pyproject.toml @@ -0,0 +1,18 @@ +[project] +name = "internal-policy-server" +version = "0.1.0" +requires-python = ">=3.12" +dependencies = [ + "psycopg>=3.2.9", + "psycopg-binary>=3.2.9", + "psycopg-pool>=3.2.6", + "sdnotify>=0.3.2", + "systemd-socketserver>=1.0", +] + +[project.scripts] +internal-policy-server = "internal_policy_server.__main__:main" + +[build-system] +requires = ["hatchling"] +build-backend = "hatchling.build" diff --git a/hosts/surtr/email/internal-policy-server/uv.lock b/hosts/surtr/email/internal-policy-server/uv.lock new file mode 100644 index 00000000..f7a4e729 --- /dev/null +++ b/hosts/surtr/email/internal-policy-server/uv.lock @@ -0,0 +1,119 @@ +version = 1 +revision = 2 +requires-python = ">=3.12" + +[[package]] +name = "internal-policy-server" +version = "0.1.0" +source = { editable = "." } +dependencies = [ + { name = "psycopg" }, + { name = "psycopg-binary" }, + { name = "psycopg-pool" }, + { name = "sdnotify" }, + { name = "systemd-socketserver" }, +] + +[package.metadata] +requires-dist = [ + { name = "psycopg", specifier = ">=3.2.9" }, + { name = "psycopg-binary", specifier = ">=3.2.9" }, + { name = "psycopg-pool", specifier = ">=3.2.6" }, + { name = "sdnotify", specifier = ">=0.3.2" }, + { name = "systemd-socketserver", specifier = ">=1.0" }, +] + +[[package]] +name = "psycopg" +version = "3.2.9" +source = { registry = "https://pypi.org/simple" } +dependencies = [ + { name = "typing-extensions", marker = "python_full_version < '3.13'" }, + { name = "tzdata", marker = "sys_platform == 'win32'" }, +] +sdist = { url = "https://files.pythonhosted.org/packages/27/4a/93a6ab570a8d1a4ad171a1f4256e205ce48d828781312c0bbaff36380ecb/psycopg-3.2.9.tar.gz", hash = "sha256:2fbb46fcd17bc81f993f28c47f1ebea38d66ae97cc2dbc3cad73b37cefbff700", size = 158122, upload-time = "2025-05-13T16:11:15.533Z" } +wheels = [ + { url = "https://files.pythonhosted.org/packages/44/b0/a73c195a56eb6b92e937a5ca58521a5c3346fb233345adc80fd3e2f542e2/psycopg-3.2.9-py3-none-any.whl", hash = "sha256:01a8dadccdaac2123c916208c96e06631641c0566b22005493f09663c7a8d3b6", size = 202705, upload-time = "2025-05-13T16:06:26.584Z" }, +] + +[[package]] +name = "psycopg-binary" +version = "3.2.9" +source = { registry = "https://pypi.org/simple" } +wheels = [ + { url = "https://files.pythonhosted.org/packages/29/6f/ec9957e37a606cd7564412e03f41f1b3c3637a5be018d0849914cb06e674/psycopg_binary-3.2.9-cp312-cp312-macosx_10_13_x86_64.whl", hash = "sha256:be7d650a434921a6b1ebe3fff324dbc2364393eb29d7672e638ce3e21076974e", size = 4022205, upload-time = "2025-05-13T16:07:48.195Z" }, + { url = "https://files.pythonhosted.org/packages/6b/ba/497b8bea72b20a862ac95a94386967b745a472d9ddc88bc3f32d5d5f0d43/psycopg_binary-3.2.9-cp312-cp312-macosx_11_0_arm64.whl", hash = "sha256:6a76b4722a529390683c0304501f238b365a46b1e5fb6b7249dbc0ad6fea51a0", size = 4083795, upload-time = "2025-05-13T16:07:50.917Z" }, + { url = "https://files.pythonhosted.org/packages/42/07/af9503e8e8bdad3911fd88e10e6a29240f9feaa99f57d6fac4a18b16f5a0/psycopg_binary-3.2.9-cp312-cp312-manylinux_2_17_aarch64.manylinux2014_aarch64.whl", hash = "sha256:96a551e4683f1c307cfc3d9a05fec62c00a7264f320c9962a67a543e3ce0d8ff", size = 4655043, upload-time = "2025-05-13T16:07:54.857Z" }, + { url = "https://files.pythonhosted.org/packages/28/ed/aff8c9850df1648cc6a5cc7a381f11ee78d98a6b807edd4a5ae276ad60ad/psycopg_binary-3.2.9-cp312-cp312-manylinux_2_17_i686.manylinux2014_i686.whl", hash = "sha256:61d0a6ceed8f08c75a395bc28cb648a81cf8dee75ba4650093ad1a24a51c8724", size = 4477972, upload-time = "2025-05-13T16:07:57.925Z" }, + { url = "https://files.pythonhosted.org/packages/5c/bd/8e9d1b77ec1a632818fe2f457c3a65af83c68710c4c162d6866947d08cc5/psycopg_binary-3.2.9-cp312-cp312-manylinux_2_17_ppc64le.manylinux2014_ppc64le.whl", hash = "sha256:ad280bbd409bf598683dda82232f5215cfc5f2b1bf0854e409b4d0c44a113b1d", size = 4737516, upload-time = "2025-05-13T16:08:01.616Z" }, + { url = "https://files.pythonhosted.org/packages/46/ec/222238f774cd5a0881f3f3b18fb86daceae89cc410f91ef6a9fb4556f236/psycopg_binary-3.2.9-cp312-cp312-manylinux_2_17_x86_64.manylinux2014_x86_64.whl", hash = "sha256:76eddaf7fef1d0994e3d536ad48aa75034663d3a07f6f7e3e601105ae73aeff6", size = 4436160, upload-time = "2025-05-13T16:08:04.278Z" }, + { url = "https://files.pythonhosted.org/packages/37/78/af5af2a1b296eeca54ea7592cd19284739a844974c9747e516707e7b3b39/psycopg_binary-3.2.9-cp312-cp312-musllinux_1_2_aarch64.whl", hash = "sha256:52e239cd66c4158e412318fbe028cd94b0ef21b0707f56dcb4bdc250ee58fd40", size = 3753518, upload-time = "2025-05-13T16:08:07.567Z" }, + { url = "https://files.pythonhosted.org/packages/ec/ac/8a3ed39ea069402e9e6e6a2f79d81a71879708b31cc3454283314994b1ae/psycopg_binary-3.2.9-cp312-cp312-musllinux_1_2_i686.whl", hash = "sha256:08bf9d5eabba160dd4f6ad247cf12f229cc19d2458511cab2eb9647f42fa6795", size = 3313598, upload-time = "2025-05-13T16:08:09.999Z" }, + { url = "https://files.pythonhosted.org/packages/da/43/26549af068347c808fbfe5f07d2fa8cef747cfff7c695136172991d2378b/psycopg_binary-3.2.9-cp312-cp312-musllinux_1_2_ppc64le.whl", hash = "sha256:1b2cf018168cad87580e67bdde38ff5e51511112f1ce6ce9a8336871f465c19a", size = 3407289, upload-time = "2025-05-13T16:08:12.66Z" }, + { url = "https://files.pythonhosted.org/packages/67/55/ea8d227c77df8e8aec880ded398316735add8fda5eb4ff5cc96fac11e964/psycopg_binary-3.2.9-cp312-cp312-musllinux_1_2_x86_64.whl", hash = "sha256:14f64d1ac6942ff089fc7e926440f7a5ced062e2ed0949d7d2d680dc5c00e2d4", size = 3472493, upload-time = "2025-05-13T16:08:15.672Z" }, + { url = "https://files.pythonhosted.org/packages/3c/02/6ff2a5bc53c3cd653d281666728e29121149179c73fddefb1e437024c192/psycopg_binary-3.2.9-cp312-cp312-win_amd64.whl", hash = "sha256:7a838852e5afb6b4126f93eb409516a8c02a49b788f4df8b6469a40c2157fa21", size = 2927400, upload-time = "2025-05-13T16:08:18.652Z" }, + { url = "https://files.pythonhosted.org/packages/28/0b/f61ff4e9f23396aca674ed4d5c9a5b7323738021d5d72d36d8b865b3deaf/psycopg_binary-3.2.9-cp313-cp313-macosx_10_13_x86_64.whl", hash = "sha256:98bbe35b5ad24a782c7bf267596638d78aa0e87abc7837bdac5b2a2ab954179e", size = 4017127, upload-time = "2025-05-13T16:08:21.391Z" }, + { url = "https://files.pythonhosted.org/packages/bc/00/7e181fb1179fbfc24493738b61efd0453d4b70a0c4b12728e2b82db355fd/psycopg_binary-3.2.9-cp313-cp313-macosx_11_0_arm64.whl", hash = "sha256:72691a1615ebb42da8b636c5ca9f2b71f266be9e172f66209a361c175b7842c5", size = 4080322, upload-time = "2025-05-13T16:08:24.049Z" }, + { url = "https://files.pythonhosted.org/packages/58/fd/94fc267c1d1392c4211e54ccb943be96ea4032e761573cf1047951887494/psycopg_binary-3.2.9-cp313-cp313-manylinux_2_17_aarch64.manylinux2014_aarch64.whl", hash = "sha256:25ab464bfba8c401f5536d5aa95f0ca1dd8257b5202eede04019b4415f491351", size = 4655097, upload-time = "2025-05-13T16:08:27.376Z" }, + { url = "https://files.pythonhosted.org/packages/41/17/31b3acf43de0b2ba83eac5878ff0dea5a608ca2a5c5dd48067999503a9de/psycopg_binary-3.2.9-cp313-cp313-manylinux_2_17_i686.manylinux2014_i686.whl", hash = "sha256:0e8aeefebe752f46e3c4b769e53f1d4ad71208fe1150975ef7662c22cca80fab", size = 4482114, upload-time = "2025-05-13T16:08:30.781Z" }, + { url = "https://files.pythonhosted.org/packages/85/78/b4d75e5fd5a85e17f2beb977abbba3389d11a4536b116205846b0e1cf744/psycopg_binary-3.2.9-cp313-cp313-manylinux_2_17_ppc64le.manylinux2014_ppc64le.whl", hash = "sha256:b7e4e4dd177a8665c9ce86bc9caae2ab3aa9360b7ce7ec01827ea1baea9ff748", size = 4737693, upload-time = "2025-05-13T16:08:34.625Z" }, + { url = "https://files.pythonhosted.org/packages/3b/95/7325a8550e3388b00b5e54f4ced5e7346b531eb4573bf054c3dbbfdc14fe/psycopg_binary-3.2.9-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl", hash = "sha256:7fc2915949e5c1ea27a851f7a472a7da7d0a40d679f0a31e42f1022f3c562e87", size = 4437423, upload-time = "2025-05-13T16:08:37.444Z" }, + { url = "https://files.pythonhosted.org/packages/1a/db/cef77d08e59910d483df4ee6da8af51c03bb597f500f1fe818f0f3b925d3/psycopg_binary-3.2.9-cp313-cp313-musllinux_1_2_aarch64.whl", hash = "sha256:a1fa38a4687b14f517f049477178093c39c2a10fdcced21116f47c017516498f", size = 3758667, upload-time = "2025-05-13T16:08:40.116Z" }, + { url = "https://files.pythonhosted.org/packages/95/3e/252fcbffb47189aa84d723b54682e1bb6d05c8875fa50ce1ada914ae6e28/psycopg_binary-3.2.9-cp313-cp313-musllinux_1_2_i686.whl", hash = "sha256:5be8292d07a3ab828dc95b5ee6b69ca0a5b2e579a577b39671f4f5b47116dfd2", size = 3320576, upload-time = "2025-05-13T16:08:43.243Z" }, + { url = "https://files.pythonhosted.org/packages/1c/cd/9b5583936515d085a1bec32b45289ceb53b80d9ce1cea0fef4c782dc41a7/psycopg_binary-3.2.9-cp313-cp313-musllinux_1_2_ppc64le.whl", hash = "sha256:778588ca9897b6c6bab39b0d3034efff4c5438f5e3bd52fda3914175498202f9", size = 3411439, upload-time = "2025-05-13T16:08:47.321Z" }, + { url = "https://files.pythonhosted.org/packages/45/6b/6f1164ea1634c87956cdb6db759e0b8c5827f989ee3cdff0f5c70e8331f2/psycopg_binary-3.2.9-cp313-cp313-musllinux_1_2_x86_64.whl", hash = "sha256:f0d5b3af045a187aedbd7ed5fc513bd933a97aaff78e61c3745b330792c4345b", size = 3477477, upload-time = "2025-05-13T16:08:51.166Z" }, + { url = "https://files.pythonhosted.org/packages/7b/1d/bf54cfec79377929da600c16114f0da77a5f1670f45e0c3af9fcd36879bc/psycopg_binary-3.2.9-cp313-cp313-win_amd64.whl", hash = "sha256:2290bc146a1b6a9730350f695e8b670e1d1feb8446597bed0bbe7c3c30e0abcb", size = 2928009, upload-time = "2025-05-13T16:08:53.67Z" }, +] + +[[package]] +name = "psycopg-pool" +version = "3.2.6" +source = { registry = "https://pypi.org/simple" } +dependencies = [ + { name = "typing-extensions" }, +] +sdist = { url = "https://files.pythonhosted.org/packages/cf/13/1e7850bb2c69a63267c3dbf37387d3f71a00fd0e2fa55c5db14d64ba1af4/psycopg_pool-3.2.6.tar.gz", hash = "sha256:0f92a7817719517212fbfe2fd58b8c35c1850cdd2a80d36b581ba2085d9148e5", size = 29770, upload-time = "2025-02-26T12:03:47.129Z" } +wheels = [ + { url = "https://files.pythonhosted.org/packages/47/fd/4feb52a55c1a4bd748f2acaed1903ab54a723c47f6d0242780f4d97104d4/psycopg_pool-3.2.6-py3-none-any.whl", hash = "sha256:5887318a9f6af906d041a0b1dc1c60f8f0dda8340c2572b74e10907b51ed5da7", size = 38252, upload-time = "2025-02-26T12:03:45.073Z" }, +] + +[[package]] +name = "sdnotify" +version = "0.3.2" +source = { registry = "https://pypi.org/simple" } +sdist = { url = "https://files.pythonhosted.org/packages/ce/d8/9fdc36b2a912bf78106de4b3f0de3891ff8f369e7a6f80be842b8b0b6bd5/sdnotify-0.3.2.tar.gz", hash = "sha256:73977fc746b36cc41184dd43c3fe81323e7b8b06c2bb0826c4f59a20c56bb9f1", size = 2459, upload-time = "2017-08-02T20:03:44.395Z" } + +[[package]] +name = "systemd-python" +version = "235" +source = { registry = "https://pypi.org/simple" } +sdist = { url = "https://files.pythonhosted.org/packages/10/9e/ab4458e00367223bda2dd7ccf0849a72235ee3e29b36dce732685d9b7ad9/systemd-python-235.tar.gz", hash = "sha256:4e57f39797fd5d9e2d22b8806a252d7c0106c936039d1e71c8c6b8008e695c0a", size = 61677, upload-time = "2023-02-11T13:42:16.588Z" } + +[[package]] +name = "systemd-socketserver" +version = "1.0" +source = { registry = "https://pypi.org/simple" } +dependencies = [ + { name = "systemd-python" }, +] +wheels = [ + { url = "https://files.pythonhosted.org/packages/d8/4f/b28b7f08880120a26669b080ca74487c8c67e8b54dcb0467a8f0c9f38ed6/systemd_socketserver-1.0-py3-none-any.whl", hash = "sha256:987a8bfbf28d959e7c2966c742ad7bad482f05e121077defcf95bb38267db9a8", size = 3248, upload-time = "2020-04-26T05:26:40.661Z" }, +] + +[[package]] +name = "typing-extensions" +version = "4.13.2" +source = { registry = "https://pypi.org/simple" } +sdist = { url = "https://files.pythonhosted.org/packages/f6/37/23083fcd6e35492953e8d2aaaa68b860eb422b34627b13f2ce3eb6106061/typing_extensions-4.13.2.tar.gz", hash = "sha256:e6c81219bd689f51865d9e372991c540bda33a0379d5573cddb9a3a23f7caaef", size = 106967, upload-time = "2025-04-10T14:19:05.416Z" } +wheels = [ + { url = "https://files.pythonhosted.org/packages/8b/54/b1ae86c0973cc6f0210b53d508ca3641fb6d0c56823f288d108bc7ab3cc8/typing_extensions-4.13.2-py3-none-any.whl", hash = "sha256:a439e7c04b49fec3e5d3e2beaa21755cadbbdc391694e28ccdd36ca4a1408f8c", size = 45806, upload-time = "2025-04-10T14:19:03.967Z" }, +] + +[[package]] +name = "tzdata" +version = "2025.2" +source = { registry = "https://pypi.org/simple" } +sdist = { url = "https://files.pythonhosted.org/packages/95/32/1a225d6164441be760d75c2c42e2780dc0873fe382da3e98a2e1e48361e5/tzdata-2025.2.tar.gz", hash = "sha256:b60a638fcc0daffadf82fe0f57e53d06bdec2f36c4df66280ae79bce6bd6f2b9", size = 196380, upload-time = "2025-03-23T13:54:43.652Z" } +wheels = [ + { url = "https://files.pythonhosted.org/packages/5c/23/c7abc0ca0a1526a0774eca151daeb8de62ec457e77262b66b359c3c7679e/tzdata-2025.2-py2.py3-none-any.whl", hash = "sha256:1a403fada01ff9221ca8044d701868fa132215d84beb92242d9acd2147f667a8", size = 347839, upload-time = "2025-03-23T13:54:41.845Z" }, +] diff --git a/hosts/surtr/postgresql/default.nix b/hosts/surtr/postgresql/default.nix index 059f4088..0ae29058 100644 --- a/hosts/surtr/postgresql/default.nix +++ b/hosts/surtr/postgresql/default.nix @@ -280,6 +280,23 @@ in { CREATE VIEW imap_user ("user", "password", quota_rule) AS SELECT mailbox.mailbox AS "user", "password", quota_rule FROM mailbox_quota_rule INNER JOIN mailbox ON mailbox_quota_rule.mailbox = mailbox.mailbox; COMMIT; + + BEGIN; + SELECT _v.register_patch('013-internal', ARRAY['000-base'], null); + + ALTER TABLE mailbox_mapping ADD COLUMN internal bool NOT NULL DEFAULT false; + CREATE TABLE mailbox_mapping_access ( + id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), + mailbox_mapping uuid REFERENCES mailbox_mapping(id), + mailbox uuid REFERENCES mailbox(id) + ); + CREATE USER "postfix-internal-policy"; + GRANT CONNECT ON DATABASE "email" TO "postfix-internal-policy"; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-internal-policy"; + GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-internal-policy"; + + COMMIT; + ''} psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" '' -- cgit v1.2.3