]> Git — Sourcephile - sourcephile-nix.git/blob - servers/losurdo/postgresql/openconcerto.nix
postgresql: fix pg_adduser
[sourcephile-nix.git] / servers / losurdo / postgresql / openconcerto.nix
1 { db, owner ? db, ... }:
2 { pkgs, lib, config, ... }:
3 let
4 sql = pkgs.fetchzip {
5 url = "https://www.openconcerto.org/fr/telechargement/1.6/OpenConcerto-1.6.3.sql.zip";
6 sha256 = "02h35ni9xknzrjsra56c3zhlhs0ji9qc61kcgi7vgcpylqjw0s6n";
7 };
8 inherit (config) networking;
9 # Example of ~/.config/OpenConcerto/main.properties
10 # DOC: https://code.openconcerto.org/filedetails.php?repname=OpenConcerto&path=%2Ftrunk%2FOpenConcerto%2Fsrc%2Forg%2Fopenconcerto%2Fsql%2FPropsConfiguration.java
11 # DOC: https://jdbc.postgresql.org/documentation/head/connect.html
12 "main.properties" = ''
13 base.root=Common
14 customer=Gestion_Default
15 jdbc.connection.ssl=true
16 jdbc.connection.sslmode=require
17 server.driver=postgresql
18 server.ip=openconcerto.${networking.domain}\:5432
19 server.login=${owner}
20 server.password=TheSecretPassword
21 systemRoot=${db}
22 '';
23 # To be used in postStart when resetting the database
24 drop = ''
25 $PSQL -d template1 -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
26 DROP OWNED BY ${owner};
27 DROP DATABASE ${db};
28 DROP ROLE ${owner};
29 EOF
30 '';
31 in
32 {
33 systemd.services.postgresql = {
34 postStart = lib.mkAfter ''
35 sed -e 's/ \(TO\|FROM\) \+openconcerto/ \1 ${owner}/g' \
36 ${sql}/OpenConcerto-1.6.3.sql |
37 connection_limit=64 \
38 encoding=UTF8 \
39 lc_collate=fr_FR.UTF-8 \
40 lc_type=fr_FR.UTF-8 \
41 owner=${owner} \
42 pass=$(cat /run/keys/postgresql_pass_${owner}) \
43 pg_createdb ${db} >/dev/null
44
45 $PSQL -d "${db}" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
46 -- Reallow this to avoid the error:
47 -- "Couldn't refresh the graph"
48 -- when testing the connexion to the database
49 -- in OpenConcerto-Configuration.sh
50 GRANT SELECT ON pg_catalog.pg_settings TO ${owner};
51
52 -- Enable PL/PGSQL
53 CREATE OR REPLACE LANGUAGE plpgsql;
54 EOF
55 '';
56 };
57 services.postgresql = {
58 authentication = lib.mkForce ''
59 # CONNECTION DATABASE USER AUTH OPTIONS
60 # FIXME: using scram-sha-256 instead of md5 requires postfix >= 11
61 hostssl ${db} ${owner} all md5
62 '';
63 identMap = ''
64 # MAPNAME SYSTEM-USERNAME PG-USERNAME
65 user root ${owner}
66 '';
67 };
68 security.install.shellHook = ''
69 pass "servers/losurdo/postgresql/pass/${owner}" |
70 ssh "$target" install -D -m 0400 -o root -g root /dev/stdin \
71 /run/keys/postgresql_pass_${owner}
72 '';
73 }