Access Nextcloud DB (postgresql) after using database.createLocally

I set up my Nextcloud with the option database.createLocally = true.
It seems to be working, but I need access to the DB so I can make a dump for backups.

I can see the user nextcloud but when I want to connect to the DB using sudo psql -U nextcloud -d nextcloud I get the following error:

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "nextcloud"

As far as I know, I should change the postgresql config to allow me to log in, but I wouldn’t like to do that in case there is a better way.

Sadly, the option’s description is criminally short and the NixOS manual just says:

{…} you can easily create a local database by setting services.nextcloud.database.createLocally to true, Nextcloud will automatically be configured to connect to it through socket.

I would appreciate any help, especially if anyone can suggest how to figure this out myself, becase even though I love the concept of NixOS, the undescribed, hard-to-find options make my life a bit hard. :slight_smile:

This happens because of the default pg_hba.conf configuration which allows only peer authentication for local (aka socket) connections. To be able to connect you would need to be connecting as the local user nextcloud, not as psql user nextcloud:

(root) ❯ su - nextcloud -s /bin/sh
(nextcloud) ❯ psql
psql (15.5)
Type "help" for help.

nextcloud=>

While this may work for one-off connections, backups are probably better implemented by something like services.postgresqlBackup or some other off-the-shelf solution that would use a separately privileged read-only user. This may or may not need tweaks to pg_hba.conf.

anyone can suggest how to figure this out myself

I usually employ one of the two approaches:

Intense staring method:

The options’ implementation is typically done in the module file (source button next to the option in NixOS search). In this case all logic of services.nextcloud is here.

Hands-on method that is not as scary as it looks:

If you want to iterate on a config but don’t want to touch a live system at all, you can throw together a VM where only the specififed module would get evaluated.

I used this file:

# test.nix
{ testers, ... }:
testers.runNixOSTest {
  name = "Ad hoc interactive test";

  nodes = {
    machine1 =
      { config, pkgs, ... }:
      {
        services.nextcloud = {
          enable = true;
          hostName = "foo";
          config = {
            adminpassFile = toString (pkgs.writeText "adminpass" "hunter2");
            dbtype = "pgsql";
          };
          database.createLocally = true;
        };
      };
  };

  # Source https://nixos.org/manual/nixos/stable/#ssec-machine-objects
  testScript =
    # python
    ''
      assert false # does not matter in this case
    '';
}

You can build a VM script with this file:

nix-build --expr '(with import <nixpkgs> {}; callPackage ./test.nix {}).driverInteractive'

Which produces ./result/bin/nixos-test-driver. nixos-test-driver is basically a way to run an interactive session for the test. start_all() launches a qemu window with a proper login; root without password.

Commands above do not assume use with flakes and resulting version of import <nixpkgs> may differ.

With flakes it’s

# flake.nix
{
  description = "A very basic flake";

  outputs =
    { self, nixpkgs }:
    let
      pkgs = nixpkgs.legacyPackages.x86_64-linux;
    in
    {
      packages.x86_64-linux.testVM = pkgs.testers.runNixOSTest {
        name = "Ad hoc interactive test";
        nodes = {
          machine1 =
            { config, pkgs, ... }:
            {
              services.nextcloud = {
                enable = true;
                hostName = "foo";
                config = {
                  adminpassFile = toString (pkgs.writeText "adminpass" "hunter2");
                  dbtype = "pgsql";
                };
                database.createLocally = true;
              };
            };
        };

        # Source https://nixos.org/manual/nixos/stable/#ssec-machine-objects
        testScript =
          # python
          ''
            assert false # does not matter in this case
          '';
      };
    };
}

and nix build -L .#testVM.driverInteractive