Enrich PostgreSQL ensure users and databases support by rycee · Pull Request #203474 · NixOS/nixpkgs

rycee · 2022-11-28T16:43:49Z

Description of changes

This PR includes a general cleanup of the generation of the "ensure users and databases" script.

It also makes it possible to create users with password.

Finally, it also makes it possible to create databases with additional parameters such as DB owner and encoding.

The matrix-synapse test is updated to use the new capabilities.

The changes should be backwards compatible.

Things done

Specifically, this commit makes it possible to set various database parameters beside just the name.

This reorganizes the ensure code generation to make the code a bit less dense and also do general cleanups. Most importantly, the order is changed to first create users, then databases, and finally grants. This allows the database creation to refer to new users as owners.

This replaces the explicit database setup script to instead use the PostgreSQL `ensureUsers` and `ensureDatabases` options.

RaitoBezarius

Amazing! Do you think it would be possible to have a bit of more NixOS tests for exercising this? Do you know if this can affect existing setups, as in, have you tried to run other tests? Can you add release notes information on this?

rycee · 2022-12-08T22:45:49Z

@RaitoBezarius Thanks for having a look! Yeah, more tests would be good. Shouldn't be too tricky.

The change should be entirely backwards compatible.

I can add a bullet point in the release notes.

nixos-discourse · 2023-02-16T21:41:42Z

This pull request has been mentioned on NixOS Discourse. There might be relevant details there:

https://discourse.nixos.org/t/anybody-wanna-adopt-203474/25524/1

SuperSandro2000

We are missing migrations from the old settings.

SuperSandro2000 · 2023-02-18T19:01:27Z

nixos/modules/services/databases/postgresql.nix

+
+              isTemplate = mkOption {
+                type = types.nullOr types.bool;
+                default = null;


Why not default to false here?

SuperSandro2000 · 2023-02-18T19:01:59Z

nixos/modules/services/databases/postgresql.nix

+
+              allowConnections = mkOption {
+                type = types.nullOr types.bool;
+                default = null;


I think I reasonable default would be true

SuperSandro2000 · 2023-02-18T19:02:17Z

nixos/modules/services/databases/postgresql.nix

+                default = null;
+                example = "sv_SE.utf8";
+                description = lib.mdDoc ''
+                  Character classification to use for the new database.


Does this fallback to the current environment set locale?

SuperSandro2000 · 2023-02-18T19:02:47Z

nixos/modules/services/databases/postgresql.nix

+
+              template = mkOption {
+                type = types.nullOr types.str;
+                default = null;


What uses postgres when creating a DB? We should default to that

I suspect that most of these options might have different defaults depending on the postgresql version.

Some of these could also be influenced by options for the daemon itself

exzombie · 2023-06-05T17:05:25Z

I can confirm that the mechanism to set the database owner implemented here solves #216989. Looking forward to seeing this merged! Note that the manual section on Nextcloud should be updated to include setting the owner.

rycee · 2023-06-18T08:46:53Z

@exzombie I will not work more on this PR. I would be happy for you to take it over if you are interested.

exzombie · 2023-06-19T13:45:16Z

@rycee I'm sorry to hear that. I'm afraid I can't take this over right now, too much going on.

nixos-discourse · 2023-12-20T00:25:41Z

This pull request has been mentioned on NixOS Discourse. There might be relevant details there:

https://discourse.nixos.org/t/what-about-state-management/37082/1

rycee requested a review from thoughtpolice as a code owner November 28, 2022 16:43

github-actions bot added 6.topic: nixos Issues or PRs affecting NixOS modules, or package usability issues specific to NixOS 8.has: module (update) This PR changes an existing module in `nixos/` labels Nov 28, 2022

rycee force-pushed the postgresql-ensure branch from 397f1ce to d6512d5 Compare November 28, 2022 16:48

postgresql: make ensureDatabases more complete

baed861

Specifically, this commit makes it possible to set various database parameters beside just the name.

rycee force-pushed the postgresql-ensure branch from d6512d5 to fc51c59 Compare November 28, 2022 16:51

ofborg bot added 10.rebuild-darwin: 0 This PR does not cause any packages to rebuild on Darwin. 10.rebuild-linux: 1-10 This PR causes between 1 and 10 packages to rebuild on Linux. labels Nov 28, 2022

rycee force-pushed the postgresql-ensure branch from fc51c59 to 0135d1c Compare November 28, 2022 18:07

rycee changed the title ~~Enrich PostgreSQL ensure users and databases support~~ WIP: Enrich PostgreSQL ensure users and databases support Nov 28, 2022

rycee added 2 commits November 28, 2022 19:37

postgresql: support setting password for ensured user

8b3dfe7

matrix-synapse: use ensure user and database options

18f3e9d

This replaces the explicit database setup script to instead use the PostgreSQL `ensureUsers` and `ensureDatabases` options.

rycee force-pushed the postgresql-ensure branch from 0135d1c to 18f3e9d Compare November 28, 2022 18:37

rycee changed the title ~~WIP: Enrich PostgreSQL ensure users and databases support~~ Enrich PostgreSQL ensure users and databases support Nov 28, 2022

RaitoBezarius reviewed Dec 1, 2022

View reviewed changes

RaitoBezarius mentioned this pull request Dec 1, 2022

services.postgresql: Allow configuration of user roles in ensureUser #200724

Merged

13 tasks

RaitoBezarius mentioned this pull request Dec 16, 2022

RFC: ensure-style options in NixOS modules #206467

Open

SuperSandro2000 reviewed Feb 18, 2023

View reviewed changes

1sixth mentioned this pull request May 16, 2023

postgresql_15 requires granting permissions on schema public, ensureUsers insufficient #216989

Closed

roberth mentioned this pull request Sep 1, 2023

nixos/postgresql: add ensureRoles option to ensureUsers #252071

Closed

12 tasks

Scrumplex mentioned this pull request Sep 18, 2023

nixos/postgresql: enrich ensure users and databases support #255961

Closed

14 tasks

SuperSandro2000 mentioned this pull request Oct 1, 2023

postgresql: default to v15 in 23.11 #254268

Merged

12 tasks

emilylange mentioned this pull request Oct 22, 2023

nixos/forgejo: work around permissions error on postgresql_15 #262741

Merged

13 tasks

bendlas mentioned this pull request Nov 14, 2023

nixos/postgresql: document psql 15 changes #267238

Merged

13 tasks

wegank added 2.status: stale https://github.com/NixOS/nixpkgs/blob/master/.github/STALE-BOT.md 2.status: merge conflict This PR has merge conflicts with the target branch labels Mar 19, 2024

stale bot removed the 2.status: stale https://github.com/NixOS/nixpkgs/blob/master/.github/STALE-BOT.md label Mar 20, 2024

wegank added the 2.status: stale https://github.com/NixOS/nixpkgs/blob/master/.github/STALE-BOT.md label Jul 4, 2024

Uh oh!

Conversation

rycee commented Nov 28, 2022

Description of changes

Things done

Uh oh!

RaitoBezarius left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

rycee commented Dec 8, 2022

Uh oh!

nixos-discourse commented Feb 16, 2023

Uh oh!

SuperSandro2000 left a comment

Choose a reason for hiding this comment

Uh oh!

SuperSandro2000 Feb 18, 2023

Choose a reason for hiding this comment

Uh oh!

SuperSandro2000 Feb 18, 2023

Choose a reason for hiding this comment

Uh oh!

SuperSandro2000 Feb 18, 2023

Choose a reason for hiding this comment

Uh oh!

SuperSandro2000 Feb 18, 2023

Choose a reason for hiding this comment

Uh oh!

Scrumplex Sep 18, 2023

Choose a reason for hiding this comment

Uh oh!

exzombie commented Jun 5, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

rycee commented Jun 18, 2023

Uh oh!

exzombie commented Jun 19, 2023

Uh oh!

nixos-discourse commented Dec 20, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

RaitoBezarius left a comment •

edited

Loading

exzombie commented Jun 5, 2023 •

edited

Loading