Describe the bug

I was trying to set up Nextcloud on 22.11 through NixOS modules, and failed with pkgs.postgres_15. It was trivial after downgrading to pkgs.postgres_14.

Steps To Reproduce

Steps to reproduce the behavior:

1. Remove any existing databases and Nextcloud data to have a clean slate.
2. Enable postgresql and set services.postgresql.package = pkgs.postgresql_15;
3. Enable Nextcloud and set it up as documented in the manual.
4. Run nixos-rebuild and observe the following error in the journal:

nextcloud-setup-start[15572]: Error while trying to initialise the database: An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema public

You can reproduce this with psql if you prefer, but the above are steps from the manual, using the latest available postgres.

Expected behavior

nixos-rebuild succeeds, the Nextcloud database is initialized properly. This does work with services.postgresql.package = pkgs.postgresql_14;

Additional context

To quote the postgresql docs:

A user can also be allowed to create objects in someone else's schema. To allow that, the CREATE privilege on the schema needs to be granted. In databases upgraded from PostgreSQL 14 or earlier, everyone has that privilege on the schema public.

It appears this only affects fresh databases.

If I understand correctly, to support the services.nextcloud module, the services.postgresql module needs to provide a way to either set the owner of the database or to grant permissions on a schema. Neither seems to be available, although, naturally, it's possible I missed something. It's almost possible to use ensureUsers to do this: the syntax for the GRANT does the right thing, but the problem is that you need to be connected to the database in question, and the postgresql-post-start script does not do that.

Notify maintainers

@thoughtpolice @danbst @globin @marsam @ivan

Metadata

• system: "x86_64-linux"
• host os: Linux 5.15.92, NixOS, 22.11 (Raccoon), 22.11.20230207.af96094
• multi-user?: yes
• sandbox: yes
• version: nix-env (Nix) 2.11.1
• channels(root): "nixos-22.11"
• nixpkgs: /nix/var/nix/profiles/per-user/root/channels/nixos