postgresql_12: remove backport-353158-to-release-24.11

This will be EOL at the end of November, so there's little reason to
keep it in 24.11[1]. As discussed, we'd like to keep it for as long as
possible to make sure there's a state in nixpkgs that has the latest
minor of postgresql_12 available with the most recent CVEs fixed for
people who cannot upgrade[2].

This aspect has been made explicit in the manual now for the next .11
release.

During the discussions it has been brought up that if people just do
`services.postgresql.enable = true;` and let the code decide the
postgresql version based on `system.stateVersion`, there's a chance that
such EOL dates will be missed. To make this harder, a warning will now
be raised when using the stateVersion-condition and the oldest still
available major is selected.

Additionally regrouped the postgresql things in the release notes to
make sure these are all shown consecutively. Otherwise it's a little
hard to keep track of all the changes made to postgresql in 24.11.

[1] https://endoflife.date/postgresql
[2] https://github.com/NixOS/nixpkgs/pull/353158#issuecomment-2453056692

(cherry picked from commit 0b3eef7441c4c8becc61d67004bd8d05c33922a1)

11 files changed, 32 insertions, 80 deletions

diff --git a/nixos/doc/manual/release-notes/rl-2411.section.md b/nixos/doc/manual/release-notes/rl-2411.section.md
index 6d1878a43576..ab841cbe2984 100644
--- a/nixos/doc/manual/release-notes/rl-2411.section.md
+++ b/nixos/doc/manual/release-notes/rl-2411.section.md
@@ -316,8 +316,16 @@
 
 - The nvidia driver no longer defaults to the proprietary driver starting with version 560. You will need to manually set `hardware.nvidia.open` to select the proprietary or open driver.
 
+- `postgresql_12` has been removed since it reached its end of life.
+
 - `postgresql` no longer accepts the `enableSystemd` override. Use `systemdSupport` instead.
 
+- `postgresql` was split into default and -dev outputs. To make this work without circular dependencies, the output of the `pg_config` system view has been removed. The `pg_config` binary is provided in the -dev output and still works as expected.
+
+- The arguments from [](#opt-services.postgresql.initdbArgs) now get shell-escaped.
+
+- `postgresql` is now [hardened by default](#module-services-postgres-hardening) using the common `systemd` settings for that.
+
 - The dhcpcd service (`networking.useDHCP`) has been hardened and now runs exclusively as the "dhcpcd" user.
   Users that were relying on the root privileges in `networking.dhcpcd.runHook` will have to write specific [sudo](security.sudo.extraRules) or [polkit](security.polkit.extraConfig) rules to allow dhcpcd to perform privileged actions.
 
@@ -583,8 +591,6 @@
 
 - Docker now defaults to 27.x, as version 24.x stopped receiving security updates and bug fixes after [February 1, 2024](https://github.com/moby/moby/pull/46772#discussion_r1686464084).
 
-- `postgresql` was split into default and -dev outputs. To make this work without circular dependencies, the output of the `pg_config` system view has been removed. The `pg_config` binary is provided in the -dev output and still works as expected.
-
 - `keycloak` was updated to version 25, which introduces new hostname related options.
   See [Upgrading Guide](https://www.keycloak.org/docs/25.0.1/upgrading/#migrating-to-25-0-0) for instructions.
 
@@ -828,8 +834,6 @@
 
 - `restic` module now has an option for inhibiting system sleep while backups are running, defaulting to off (not inhibiting sleep). Available as [`services.restic.backups.<name>.inhibitsSleep`](#opt-services.restic.backups._name_.inhibitsSleep).
 
-- The arguments from [](#opt-services.postgresql.initdbArgs) now get shell-escaped.
-
 - Mattermost has been updated from 9.5 to 9.11 ESR. See the [changelog](https://docs.mattermost.com/about/mattermost-v9-changelog.html#release-v9-11-extended-support-release) for more details.
 
 - `cargo-tauri.hook` was introduced to help users build [Tauri](https://tauri.app/) projects. It is meant to be used alongside
@@ -849,8 +853,6 @@
 
 - `iproute2` now has libbpf support.
 
-- `postgresql` is now [hardened by default](#module-services-postgres-hardening) using the common `systemd` settings for that.
-
   If you use extensions that are not packaged in nixpkgs, please review whether it still works
   with the current settings and adjust accordingly if needed.
 
diff --git a/nixos/modules/services/databases/postgresql.md b/nixos/modules/services/databases/postgresql.md
index 6cd3defb24a3..41b2e70f334e 100644
--- a/nixos/modules/services/databases/postgresql.md
+++ b/nixos/modules/services/databases/postgresql.md
@@ -261,8 +261,9 @@ Technically, we'd not want to have EOL'ed packages in a stable NixOS release, wh
 Thus:
 - In September/October the new major version will be released and added to nixos-unstable.
 - In November the last minor version for the oldest major will be released.
-- Both the current stable .05 release and nixos-unstable should be updated to the latest minor.
-- In November, before branch-off for the .11 release, the EOL-ed major will be removed from nixos-unstable.
+- Both the current stable .05 release and nixos-unstable should be updated to the latest minor that will usually be released in November.
+  - This is relevant for people who need to use this major for as long as possible. In that case its desirable to be able to pin nixpkgs to a commit that still has it, at the latest minor available.
+- In November, before branch-off for the .11 release and after the update to the latest minor, the EOL-ed major will be removed from nixos-unstable.
 
 This leaves a small gap of a couple of weeks after the latest minor release and the end of our support window for the .05 release, in which there could be an emergency release to other major versions of PostgreSQL - but not the oldest major we have in that branch. In that case: If we can't trivially patch the issue, we will mark the package/version as insecure **immediately**.
 
@@ -292,7 +293,7 @@ postgresql_15.pkgs.pg_partman        postgresql_15.pkgs.pgroonga
 To add plugins via NixOS configuration, set `services.postgresql.extraPlugins`:
 ```nix
 {
-  services.postgresql.package = pkgs.postgresql_12;
+  services.postgresql.package = pkgs.postgresql_17;
   services.postgresql.extraPlugins = ps: with ps; [
     pg_repack
     postgis
@@ -303,7 +304,7 @@ To add plugins via NixOS configuration, set `services.postgresql.extraPlugins`:
 You can build custom PostgreSQL-with-plugins (to be used outside of NixOS) using function `.withPackages`. For example, creating a custom PostgreSQL package in an overlay can look like:
 ```nix
 self: super: {
-  postgresql_custom = self.postgresql_12.withPackages (ps: [
+  postgresql_custom = self.postgresql_17.withPackages (ps: [
     ps.pg_repack
     ps.postgis
   ]);
diff --git a/nixos/modules/services/databases/postgresql.nix b/nixos/modules/services/databases/postgresql.nix
index 876969ef9bb5..22ff29fd95be 100644
--- a/nixos/modules/services/databases/postgresql.nix
+++ b/nixos/modules/services/databases/postgresql.nix
@@ -26,6 +26,7 @@ let
     optionalString
     types
     versionAtLeast
+    warn
     ;
 
   cfg = config.services.postgresql;
@@ -484,10 +485,18 @@ in
 
     services.postgresql.package = let
         mkThrow = ver: throw "postgresql_${ver} was removed, please upgrade your postgresql version.";
+        mkWarn = ver: warn ''
+          The postgresql package is not pinned and selected automatically by
+          `systemd.stateVersion`. Right now this is `pkgs.postgresql_${ver}`, the
+          oldest postgresql version available and thus the next that will be
+          removed when EOL on the next stable cycle.
+
+          See also https://endoflife.date/postgresql
+        '';
         base = if versionAtLeast config.system.stateVersion "24.11" then pkgs.postgresql_16
             else if versionAtLeast config.system.stateVersion "23.11" then pkgs.postgresql_15
             else if versionAtLeast config.system.stateVersion "22.05" then pkgs.postgresql_14
-            else if versionAtLeast config.system.stateVersion "21.11" then pkgs.postgresql_13
+            else if versionAtLeast config.system.stateVersion "21.11" then mkWarn "13" pkgs.postgresql_13
             else if versionAtLeast config.system.stateVersion "20.03" then mkThrow "11"
             else if versionAtLeast config.system.stateVersion "17.09" then mkThrow "9_6"
             else mkThrow "9_5";
diff --git a/nixos/modules/services/web-apps/outline.nix b/nixos/modules/services/web-apps/outline.nix
index 4c1de579ecc5..ba426ce89bbf 100644
--- a/nixos/modules/services/web-apps/outline.nix
+++ b/nixos/modules/services/web-apps/outline.nix
@@ -586,37 +586,6 @@ in
       ensureDatabases = [ "outline" ];
     };
 
-    # Outline is unable to create the uuid-ossp extension when using postgresql 12, in later version this
-    # extension can be created without superuser permission. This services therefor this extension before
-    # outline starts and postgresql 12 is using on the host.
-    #
-    # Can be removed after postgresql 12 is dropped from nixos.
-    systemd.services.outline-postgresql =
-      let
-        pgsql = config.services.postgresql;
-      in
-        lib.mkIf (cfg.databaseUrl == "local" && pgsql.package == pkgs.postgresql_12) {
-          after = [ "postgresql.service" ];
-          bindsTo = [ "postgresql.service" ];
-          wantedBy = [ "outline.service" ];
-          partOf = [ "outline.service" ];
-          path = [
-            pgsql.package
-          ];
-          script = ''
-            set -o errexit -o pipefail -o nounset -o errtrace
-            shopt -s inherit_errexit
-
-            psql outline -tAc 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'
-          '';
-
-          serviceConfig = {
-            User = pgsql.superUser;
-            Type = "oneshot";
-            RemainAfterExit = true;
-          };
-        };
-
     services.redis.servers.outline = lib.mkIf (cfg.redisUrl == "local") {
       enable = true;
       user = config.services.outline.user;
diff --git a/nixos/tests/pleroma.nix b/nixos/tests/pleroma.nix
index 9e1bc1ccefbf..8a765c919329 100644
--- a/nixos/tests/pleroma.nix
+++ b/nixos/tests/pleroma.nix
@@ -205,7 +205,7 @@ import ./make-test-python.nix ({ pkgs, ... }:
         };
         postgresql = {
           enable = true;
-          package = pkgs.postgresql_12;
+          package = pkgs.postgresql_13;
         };
         nginx = {
           enable = true;
diff --git a/pkgs/servers/sql/postgresql/12.nix b/pkgs/servers/sql/postgresql/12.nix
deleted file mode 100644
index 3b40e5cca47e..000000000000
--- a/pkgs/servers/sql/postgresql/12.nix
+++ /dev/null
@@ -1,10 +0,0 @@
-import ./generic.nix {
-  version = "12.21";
-  hash = "sha256-bHEVUKwcx4KIZeWCPZ9Ffjva1vQyAXcWn5DkGb4MJ/I=";
-  muslPatches = {
-    dont-use-locale-a = {
-      url = "https://git.alpinelinux.org/aports/plain/testing/postgresql12/dont-use-locale-a-on-musl.patch?id=d5227c91adda59d4e7f55f13468f0314e8869174";
-      hash = "sha256-fk+y/SvyA4Tt8OIvDl7rje5dLs3Zw+Ln1oddyYzerOo=";
-    };
-  };
-}
diff --git a/pkgs/servers/sql/postgresql/default.nix b/pkgs/servers/sql/postgresql/default.nix
index 8eb20188651f..56c0158eca6d 100644
--- a/pkgs/servers/sql/postgresql/default.nix
+++ b/pkgs/servers/sql/postgresql/default.nix
@@ -9,7 +9,6 @@ let
   # version. In other words: Do not remove the second-to-last minor version from nixpkgs,
   # yet. Update first.
   versions = {
-    postgresql_12 = ./12.nix;
     postgresql_13 = ./13.nix;
     postgresql_14 = ./14.nix;
     postgresql_15 = ./15.nix;
diff --git a/pkgs/servers/sql/postgresql/generic.nix b/pkgs/servers/sql/postgresql/generic.nix
index 546dbf1e284c..6ed9b843cf5a 100644
--- a/pkgs/servers/sql/postgresql/generic.nix
+++ b/pkgs/servers/sql/postgresql/generic.nix
@@ -29,10 +29,6 @@ let
       # PL/Python
       , pythonSupport ? false
       , python3
-
-      # detection of crypt fails when using llvm stdenv, so we add it manually
-      # for <13 (where it got removed: https://github.com/postgres/postgres/commit/c45643d618e35ec2fe91438df15abd4f3c0d85ca)
-      , libxcrypt
     } @args:
   let
     atLeast = lib.versionAtLeast version;
@@ -100,7 +96,6 @@ let
       icu
       libuuid
     ]
-      ++ lib.optionals (olderThan "13") [ libxcrypt ]
       ++ lib.optionals jitSupport [ llvmPackages.llvm ]
       ++ lib.optionals lz4Enabled [ lz4 ]
       ++ lib.optionals zstdEnabled [ zstd ]
@@ -129,10 +124,7 @@ let
     # those paths. This avoids a lot of circular dependency problems with different outputs,
     # and allows splitting them cleanly.
     env.CFLAGS = "-fdata-sections -ffunction-sections"
-      + (if stdenv'.cc.isClang then " -flto" else " -fmerge-constants -Wl,--gc-sections")
-      # Makes cross-compiling work when xml2-config can't be executed on the host.
-      # Fixed upstream in https://github.com/postgres/postgres/commit/0bc8cebdb889368abdf224aeac8bc197fe4c9ae6
-      + lib.optionalString (olderThan "13") " -I${libxml2.dev}/include/libxml2";
+      + (if stdenv'.cc.isClang then " -flto" else " -fmerge-constants -Wl,--gc-sections");
 
     configureFlags = [
       "--with-openssl"
@@ -175,8 +167,8 @@ let
     ] ++ lib.optionals stdenv'.hostPlatform.isMusl (
       # Using fetchurl instead of fetchpatch on purpose: https://github.com/NixOS/nixpkgs/issues/240141
       map fetchurl (lib.attrValues muslPatches)
-    ) ++ lib.optionals stdenv'.hostPlatform.isLinux  [
-      (if atLeast "13" then ./patches/socketdir-in-run-13+.patch else ./patches/socketdir-in-run.patch)
+    ) ++ lib.optionals stdenv'.hostPlatform.isLinux [
+    ./patches/socketdir-in-run-13+.patch
     ] ++ lib.optionals (stdenv'.hostPlatform.isDarwin && olderThan "16") [
       ./patches/export-dynamic-darwin-15-.patch
     ];
diff --git a/pkgs/servers/sql/postgresql/patches/socketdir-in-run.patch b/pkgs/servers/sql/postgresql/patches/socketdir-in-run.patch
deleted file mode 100644
index 4932ef69ee36..000000000000
--- a/pkgs/servers/sql/postgresql/patches/socketdir-in-run.patch
+++ /dev/null
@@ -1,11 +0,0 @@
---- a/src/include/pg_config_manual.h
-+++ b/src/include/pg_config_manual.h
-@@ -179,7 +179,7 @@
-  * here's where to twiddle it.  You can also override this at runtime
-  * with the postmaster's -k switch.
-  */
--#define DEFAULT_PGSOCKET_DIR  "/tmp"
-+#define DEFAULT_PGSOCKET_DIR  "/run/postgresql"
- 
- /*
-  * This is the default event source for Windows event log.
diff --git a/pkgs/top-level/aliases.nix b/pkgs/top-level/aliases.nix
index 2b156761aa47..7ab8cfafb5ea 100644
--- a/pkgs/top-level/aliases.nix
+++ b/pkgs/top-level/aliases.nix
@@ -971,6 +971,11 @@ mapAliases {
   timescaledb = postgresqlPackages.timescaledb;
   tsearch_extras = postgresqlPackages.tsearch_extras;
 
+  postgresql_12 = throw "postgresql_12 has been removed since it reached its EOL upstream"; # Added 2024-11-14
+  postgresql_12_jit = throw "postgresql_12 has been removed since it reached its EOL upstream"; # Added 2024-11-14
+  postgresql12Packages = throw "postgresql_12 has been removed since it reached its EOL upstream"; # Added 2024-11-14
+  postgresql12JitPackages = throw "postgresql_12 has been removed since it reached its EOL upstream"; # Added 2024-11-14
+
   # pinentry was using multiple outputs, this emulates the old interface for i.e. home-manager
   # soon: throw "'pinentry' has been removed. Pick an appropriate variant like 'pinentry-curses' or 'pinentry-gnome3'";
   pinentry = pinentry-all // {
diff --git a/pkgs/top-level/all-packages.nix b/pkgs/top-level/all-packages.nix
index 0519ee38b00f..a339b32eb3e1 100644
--- a/pkgs/top-level/all-packages.nix
+++ b/pkgs/top-level/all-packages.nix
@@ -12148,14 +12148,12 @@ with pkgs;
 
   postgresqlVersions = import ../servers/sql/postgresql pkgs;
   inherit (postgresqlVersions)
-    postgresql_12
     postgresql_13
     postgresql_14
     postgresql_15
     postgresql_16
     postgresql_17
 
-    postgresql_12_jit
     postgresql_13_jit
     postgresql_14_jit
     postgresql_15_jit
@@ -12166,13 +12164,11 @@ with pkgs;
   postgresql_jit = postgresql_16_jit;
   postgresqlPackages = recurseIntoAttrs postgresql.pkgs;
   postgresqlJitPackages = recurseIntoAttrs postgresql_jit.pkgs;
-  postgresql12Packages = recurseIntoAttrs postgresql_12.pkgs;
   postgresql13Packages = recurseIntoAttrs postgresql_13.pkgs;
   postgresql14Packages = recurseIntoAttrs postgresql_14.pkgs;
   postgresql15Packages = recurseIntoAttrs postgresql_15.pkgs;
   postgresql16Packages = recurseIntoAttrs postgresql_16.pkgs;
   postgresql17Packages = recurseIntoAttrs postgresql_17.pkgs;
-  postgresql12JitPackages = recurseIntoAttrs postgresql_12_jit.pkgs;
   postgresql13JitPackages = recurseIntoAttrs postgresql_13_jit.pkgs;
   postgresql14JitPackages = recurseIntoAttrs postgresql_14_jit.pkgs;
   postgresql15JitPackages = recurseIntoAttrs postgresql_15_jit.pkgs;