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