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 }