Make it possible to define PostgreSQL versions in this repository

[thoughtpolice]

When making changes to upstream nixpkgs in order to update Postgres, this is a perfect example of how to do so:

• postgresql: 15.4, 14.9, 13.12, 12.16 & 11.21 NixOS/nixpkgs#248373

This is a PR that:

• Updates hashes and versions for every point release of Postgres,
• Is made against the staging branch in the nixpkgs repository, and
• Was merged quickly

Point 2 is the most important here, and the center of discussion for the rest of this issue.

What is staging?

The staging branch in nixpkgs is used for "updates that will cause a lot of packages to be rebuilt or may change core functionality significantly." These changes are separated from the main master branch so that there can be some level of resource isolation between the two; we often want to prioritize packages on master being built, while staging often is unstable or breaks or a change might require follow up changes to fully stabilize. Typically, staging is merged back to master once every few weeks or very quickly in the case of critical, dire security vulnerabilities or major updates that are needed for the userbase.

In our case, staging is relevant to us because it is where updates to PostgreSQL will appear. The reason for this is quite simple: updating PostgreSQL means updating the default libpq library, which has many, many, many dependent packages. If you think of the set of all packages as a tree, an update to a package causes all packages "below" it (dependent on it, directly or indirectly) to be rebuilt. Therefore, packages that are very "high up" and would cause significant proportions of the overall tree to change are isolated to staging.

What does staging mean for this repository?

This repository tracks the nixpkgs-unstable branch of the nixpkgs repository, which is a lagging variant of the master branch that simply has binary packages built for it, i.e. when CI complets on a commit on master, the CI system fast forwards nixpkgs-unstable to that commit. It's typically sub-24hrs behind master.

Importantly, this isn't staging! So that means that the update for PostgreSQL in the above PR will likely not appear for the next few days. That could be OK or not good in the case of a bad vulnerability.

Solution 1: Expose APIs from upstream to build PostgreSQL versions

One solution to this is to expose the needed APIs upstream to build PostgreSQL packages. This hasn't been done because currently the internals are a bit private; I suspect it wouldn't be that hard to refactor, but it would probably break some client functionality. I'll need to think about this

Solution 2: Use overrideAttrs in an overlay

We can possibly apply an overlay on a given postgresql version; for example, something like this (totally untested)

final: prev:

{
    postgresql_14 = prev.postgresql.overrideAttrs (oldAttrs: {
      pname = oldAttrs.pname;
      src = final.pkgs.fetchurl {
        ....
      };
    });
}

in an overlay under ./overlays and imported in flake.nix might do the trick.

I tried this on Friday but ran into an issue; it might have just been PEBKAC.

Solution 3: Do nothing, coordinate upstream on serious vulnerabilities

In the event of serious security vulnerabilities, staging is often bypassed and commits to master are made directly, and thus appear in binary builds relatively "soon." Therefore, in some sense, doing nothing may be an acceptable option, as long as the issue is considered relatively OK. Updates and staging merges can be done on a more aggressive basis too, so this is something that can be coordinated upstream.