1 { lib, pkgs, config, ... }:
3 cfg = config.myServices.databases.postgresql;
5 options.myServices.databases = {
7 enable = lib.mkOption {
10 description = "Whether to enable postgresql database";
11 type = lib.types.bool;
13 package = lib.mkOption {
14 type = lib.types.package;
15 default = pkgs.postgresql;
17 Postgresql package to use.
20 ldapConfig = lib.mkOption {
21 description = "LDAP configuration to allow PAM identification via LDAP";
22 type = lib.types.submodule {
24 host = lib.mkOption { type = lib.types.str; };
25 base = lib.mkOption { type = lib.types.str; };
26 dn = lib.mkOption { type = lib.types.str; };
27 password = lib.mkOption { type = lib.types.str; };
28 filter = lib.mkOption { type = lib.types.str; };
32 replicationLdapConfig = lib.mkOption {
33 description = "LDAP configuration to allow replication";
34 type = lib.types.submodule {
36 host = lib.mkOption { type = lib.types.str; };
37 base = lib.mkOption { type = lib.types.str; };
38 dn = lib.mkOption { type = lib.types.str; };
39 password = lib.mkOption { type = lib.types.str; };
43 authorizedHosts = lib.mkOption {
45 description = "Hosts to allow connections from";
46 type = lib.types.attrsOf (lib.types.listOf (lib.types.submodule {
48 method = lib.mkOption {
52 username = lib.mkOption {
56 database = lib.mkOption {
62 type = lib.types.listOf lib.types.str;
66 type = lib.types.listOf lib.types.str;
71 replicationHosts = lib.mkOption {
73 description = "Hosts to allow replication from";
74 type = lib.types.attrsOf (lib.types.submodule {
77 type = lib.types.listOf lib.types.str;
80 type = lib.types.listOf lib.types.str;
86 socketsDir = lib.mkOption {
87 type = lib.types.path;
88 default = "/run/postgresql";
90 The directory where Postgresql puts sockets.
97 config = lib.mkIf cfg.enable {
98 networking.firewall.allowedTCPPorts = [ 5432 ];
100 security.acme.certs."postgresql" = config.myServices.databasesCerts // {
103 domain = "db-1.immae.eu";
105 systemctl reload postgresql.service
109 systemd.services.postgresql.serviceConfig = {
110 SupplementaryGroups = "keys";
112 systemd.services.postgresql.postStart = lib.mkAfter ''
113 # This line is already defined in 19.09
114 PSQL="${pkgs.sudo}/bin/sudo -u postgres psql --port=5432"
116 ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
117 $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
119 || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
120 '') cfg.replicationHosts)}
122 ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
124 sname = builtins.replaceStrings ["-"] ["_"] role;
127 $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
129 || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
130 '') cfg.replicationHosts)}
133 services.postgresql = {
135 package = cfg.package;
138 max_connections = 100
140 shared_buffers = 512MB
144 log_timezone = 'Europe/Paris'
145 datestyle = 'iso, mdy'
146 timezone = 'Europe/Paris'
147 lc_messages = 'en_US.UTF-8'
148 lc_monetary = 'en_US.UTF-8'
149 lc_numeric = 'en_US.UTF-8'
150 lc_time = 'en_US.UTF-8'
151 default_text_search_config = 'pg_catalog.english'
152 # this introduces a small delay before storing on disk, but
153 # makes it order of magnitudes quicker
154 synchronous_commit = off
156 ssl_cert_file = '${config.security.acme.certs.postgresql.directory}/fullchain.pem'
157 ssl_key_file = '${config.security.acme.certs.postgresql.directory}/key.pem'
160 hosts = builtins.concatStringsSep "\n" (
161 lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
162 map (ip6: "hostssl ${v.database} ${v.username} ${ip6}/128 ${v.method}") v.ip6
163 ++ map (ip4: "hostssl ${v.database} ${v.username} ${ip4}/32 ${v.method}") v.ip4
164 ) vs) cfg.authorizedHosts
166 replication = builtins.concatStringsSep "\n" (
167 lib.lists.flatten (lib.mapAttrsToList (k: v:
168 map (ip6: "hostssl replication ${k} ${ip6}/128 pam pamservice=postgresql_replication") v.ip6
169 ++ map (ip4: "hostssl replication ${k} ${ip4}/32 pam pamservice=postgresql_replication") v.ip4
170 ) cfg.replicationHosts
173 local all postgres ident
176 hostssl all all all pam
183 dest = "postgresql/pam";
184 permissions = "0400";
187 text = with cfg.ldapConfig; ''
197 dest = "postgresql/pam_replication";
198 permissions = "0400";
201 text = with cfg.replicationLdapConfig; ''
206 pam_login_attribute cn
212 security.pam.services = let
213 pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
217 auth required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
218 account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
221 postgresql_replication = {
223 auth required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
224 account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication