]> Git — Sourcephile - sourcephile-nix.git/blob - install/logical/friot/postgresql.nix
pass: fix paths
[sourcephile-nix.git] / install / logical / friot / postgresql.nix
1 {pkgs, lib, config, ...}:
2 let inherit (lib) types;
3 inherit (pkgs.lib) unlines unlinesAttrs;
4 inherit (config) networking users;
5 inherit (config.services) postgresql;
6 psql = "${pkgs.postgresql}/bin/psql --set ON_ERROR_STOP=1 -U ${postgresql.superUser}";
7 psqlRun = args: sql: "${psql} ${args} -f - <<EOF\n" + sql + "\nEOF\n";
8 initLanguages = initLanguagePLPGSQL;
9 initLanguagePLPGSQL =
10 psqlRun "template1" ''
11 CREATE OR REPLACE FUNCTION create_language_plpgsql()
12 RETURNS BOOLEAN AS \$\$
13 CREATE LANGUAGE plpgsql;
14 SELECT TRUE;
15 \$\$ LANGUAGE SQL;
16 SELECT CASE WHEN NOT (
17 SELECT TRUE AS exists
18 FROM pg_language
19 WHERE lanname = 'plpgsql'
20 UNION
21 SELECT FALSE AS exists
22 ORDER BY exists DESC
23 LIMIT 1
24 )
25 THEN
26 create_language_plpgsql()
27 ELSE
28 FALSE
29 END AS plpgsql_created;
30 DROP FUNCTION create_language_plpgsql();
31 '';
32 initSchemas =
33 initSchemaPublic +
34 psqlRun "" (unlinesAttrs (schema: {owner, extraConfig, ...}: ''
35 DO LANGUAGE plpgsql \$\$
36 BEGIN
37 IF NOT EXISTS (SELECT * FROM pg_catalog.pg_namespace WHERE nspname = "${schema}" LIMIT 1) THEN
38 CREATE SCHEMA ${schema}
39 AUTHORIZATION ${owner};
40 END IF;
41 END;
42 \$\$;
43 ${extraConfig}
44 '') postgresql.schemas);
45 initSchemaPublic =
46 psqlRun "template1" ''
47 -- NOTE: deny access to public schema,
48 -- so that users do not see others' databases
49 REVOKE ALL ON DATABASE template1 FROM public;
50 REVOKE ALL ON SCHEMA public FROM public;
51 GRANT ALL ON SCHEMA public TO ${postgresql.superUser};
52 '' +
53 psqlRun "template1" ''
54 -- NOTE: deny public access to all tables
55 REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public;
56 REVOKE ALL ON SCHEMA pg_catalog FROM public;
57 '';
58 initUsers = psqlRun ""
59 (unlinesAttrs (user: {auth, extraConfig, ...}: ''
60 DO LANGUAGE plpgsql \$\$
61 BEGIN
62 IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE usename = '${user}' LIMIT 1) THEN
63 CREATE ROLE ${user}
64 LOGIN
65 NOCREATEDB
66 NOCREATEROLE
67 NOINHERIT
68 NOSUPERUSER;
69 END IF;
70 END;
71 \$\$;
72 GRANT USAGE ON SCHEMA public TO ${user};
73 ${extraConfig}
74 '') postgresql.users);
75 initRoles = psqlRun ""
76 (unlinesAttrs (role: {...}: ''
77 DO LANGUAGE plpgsql \$\$
78 BEGIN
79 IF NOT EXISTS (SELECT * FROM pg_catalog.pg_roles WHERE rolname = "${role}" LIMIT 1) THEN
80 CREATE ROLE ${role}
81 NOLOGIN
82 NOCREATEDB
83 NOCREATEROLE
84 NOINHERIT
85 NOSUPERUSER;
86 END IF;
87 END;
88 \$\$;
89 '') postgresql.roles);
90 initDatabases =
91 unlinesAttrs (db: {owner ? db, users, extraConfig, ...}: ''
92 # NOTE: CREATE DATABASE cannot be done inside a function or multi-lines input.
93 exists="$(${psql} template1 -t -c "SELECT datname FROM pg_catalog.pg_database WHERE datname = '${db}' LIMIT 1")"
94 test -n "$exists" ||
95 ${psql} template1 -c "CREATE DATABASE ${db} WITH OWNER=${owner};"
96 '' + psqlRun "template1" ''
97 REVOKE ALL ON DATABASE ${db} FROM public;
98 ${unlines (map (user: "GRANT CONNECT,TEMPORARY ON DATABASE ${db} TO ${user};") users)}
99 '' +
100 psqlRun db ''
101 GRANT ALL ON SCHEMA public TO ${owner} WITH GRANT OPTION;
102 ${extraConfig}
103 ''
104 ) postgresql.databases;
105 in
106 {
107 options = {
108 services.postgresql.databases = lib.mkOption {
109 default = {};
110 type = types.attrsOf (types.submodule ({name, options, config, ...}: {
111 options = {
112 data = lib.mkOption {
113 type = types.lines;
114 description = "The database's data in SQL.";
115 };
116 owner = lib.mkOption {
117 type = types.str;
118 description = "The database's owner.";
119 default = name;
120 };
121 users = lib.mkOption {
122 type = types.listOf types.str;
123 description = "Databases' users.";
124 default = [];
125 };
126 resetData = lib.mkOption {
127 type = types.bool;
128 description = "Whether to reset the data at each start of the postgresql service.";
129 default = false;
130 };
131 extraConfig = lib.mkOption {
132 type = types.lines;
133 description = "Extra SQL config code (run on the database).";
134 default = "";
135 };
136 };
137 }));
138 };
139 services.postgresql.schemas = lib.mkOption {
140 default = {};
141 type = types.attrsOf (types.submodule ({name, options, config, ...}: {
142 options = {
143 data = lib.mkOption {
144 type = types.lines;
145 description = "The schema's data in SQL.";
146 };
147 resetData = lib.mkOption {
148 type = types.bool;
149 description = "Whether to reset the data at each start of the postgresql service.";
150 default = false;
151 };
152 extraConfig = lib.mkOption {
153 type = types.lines;
154 description = "Extra SQL config code.";
155 default = "";
156 };
157 };
158 }));
159 };
160 services.postgresql.users = lib.mkOption {
161 default = {};
162 type = types.attrsOf (types.submodule ({name, options, config, ...}: {
163 options = {
164 auth = lib.mkOption {
165 type = types.enum [ "unix" "password" ];
166 description = "Authentification method.";
167 default = "unix";
168 };
169 extraConfig = lib.mkOption {
170 type = types.lines;
171 description = "Extra SQL config code.";
172 default = "";
173 };
174 };
175 }));
176 };
177 services.postgresql.roles = lib.mkOption {
178 default = {};
179 type = types.attrsOf (types.submodule ({name, options, config, ...}: {
180 options = {
181 };
182 }));
183 };
184 };
185 config = {
186 users.users = lib.mapAttrs (user: {auth, ...}:
187 { extraGroups = lib.optionals (auth == "unix")
188 [ users.users.postgres.group ];
189 }
190 ) postgresql.users;
191 systemd.services.postgresql = {
192 postStart = unlines [
193 "set -x"
194 initLanguages
195 initSchemas
196 initUsers
197 initRoles
198 initDatabases
199 ];
200 #serviceConfig = {
201 # RuntimeDirectory = [ "postgresql" ];
202 # RuntimeDirectoryMode = "0775";
203 #};
204 };
205 services = {
206 postgresql = {
207 enable = true;
208 extraConfig = ''
209 #unix_socket_directories = '/run/postgresql'
210 #unix_socket_group = '${postgresql.superUser}-data'
211 unix_socket_permissions = 0770 # begin with 0 to use octal notation
212 '';
213 identMap = lib.mkForce ''
214 # MAPNAME SYSTEM-USERNAME PG-USERNAME
215 admin ${postgresql.superUser} ${postgresql.superUser}
216 admin root ${postgresql.superUser}
217 ${unlinesAttrs (user: {...}: ''user root ${user}'') postgresql.users}
218 user /^(.*)$ \1
219 '';
220 authentication = lib.mkForce ''
221 # CONNECTION DATABASE USER AUTH OPTIONS
222 local all postgres peer map=admin
223 local all backup peer
224 local sameuser all peer map=user
225 local samerole all peer map=role
226 '';
227 };
228 postgresqlBackup = {
229 enable = true;
230 };
231 };
232 #users.groups."${postgresql.superUser}-data" = {
233 # name = "${postgresql.superUser}-data";
234 #};
235 };
236 }