I’m creating PostgreSQL user declaratively, using services.postgresql.ensureUsers
, but due to security reasons, I need this user to be authenticated with password. I have looked through Postgres module inside Nixpkgs repo(https://github.com/NixOS/nixpkgs/blob/f155651d3f137d802d6b0a94bcb331a6ad0f00a6/nixos/modules/services/databases/postgresql.nix ), but haven’t found any configuration options, related to password.
So, I’m wondering, is there’s any way to define password to postgresql user declaratively?
You could use initialScript
I suppose. Not a great idea, though. Creating databases and database users isn’t reproducible… so you probably shouldn’t rely too heavily on these options for production environments.
1 Like
I am not sure how great of an idea this is, but it has been working for me for awhile.
Basically I just reset the password on every postgresql
start by adding to the systemd service’s postStart
. Here is an example of reading the password from a file using agenix .
# Set the authentik postgresql password
systemd.services.postgresql.postStart = let
password_file_path = config.age.secrets.service_authentik_postgres_password.path;
in ''
$PSQL -tA <<'EOF'
DO $$
DECLARE password TEXT;
BEGIN
password := trim(both from replace(pg_read_file('${password_file_path}'), E'\n', '''));
EXECUTE format('ALTER ROLE authentik WITH PASSWORD '''%s''';', password);
END $$;
EOF
'';
EDIT: Just saw the date of the original post, sorry for the necro.
6 Likes
Atemu
July 12, 2024, 12:35pm
4
I have taken your code (hope you don’t mind), adapted it a little and integrated it with the postgres module:
NixOS:master
← Atemu:nixos/postgresql-user-password
opened 12:52PM - 11 Jul 24 UTC
Previously you had to roll your own systemd service that runs some SQL and risk … inadvertently exposing your password to the Nix store by doing it wrong.
This provides a standard method to set up user passwords via password files which can safely be deployed via secrets management solutions for those who care about security or alternatively also simply generated for those who don't.
Adapted from https://discourse.nixos.org/t/assign-password-to-postgres-user-declaratively/9726/3
## Description of changes
<!--
For package updates please link to a changelog or describe changes, this helps your fellow maintainers discover breaking updates.
For new packages please briefly describe the package or provide a link to its homepage.
-->
## Things done
- Built on platform(s)
- [x] x86_64-linux
- [ ] aarch64-linux
- [ ] x86_64-darwin
- [ ] aarch64-darwin
- For non-Linux: Is sandboxing enabled in `nix.conf`? (See [Nix manual](https://nixos.org/manual/nix/stable/command-ref/conf-file.html))
- [ ] `sandbox = relaxed`
- [ ] `sandbox = true`
- [x] Tested, as applicable:
- [NixOS test(s)](https://nixos.org/manual/nixos/unstable/index.html#sec-nixos-tests) (look inside [nixos/tests](https://github.com/NixOS/nixpkgs/blob/master/nixos/tests))
- and/or [package tests](https://github.com/NixOS/nixpkgs/blob/master/pkgs/README.md#package-tests)
- or, for functions and "core" functionality, tests in [lib/tests](https://github.com/NixOS/nixpkgs/blob/master/lib/tests) or [pkgs/test](https://github.com/NixOS/nixpkgs/blob/master/pkgs/test)
- made sure NixOS tests are [linked](https://nixos.org/manual/nixpkgs/unstable/#ssec-nixos-tests-linking) to the relevant packages
- [ ] Tested compilation of all packages that depend on this change using `nix-shell -p nixpkgs-review --run "nixpkgs-review rev HEAD"`. Note: all changes have to be committed, also see [nixpkgs-review usage](https://github.com/Mic92/nixpkgs-review#usage)
- [x] Tested basic functionality of all binary files (usually in `./result/bin/`)
- [24.11 Release Notes](https://github.com/NixOS/nixpkgs/blob/master/nixos/doc/manual/release-notes/rl-2411.section.md) (or backporting [23.11](https://github.com/NixOS/nixpkgs/blob/master/nixos/doc/manual/release-notes/rl-2311.section.md) and [24.05](https://github.com/NixOS/nixpkgs/blob/master/nixos/doc/manual/release-notes/rl-2405.section.md) Release notes)
- [ ] (Package updates) Added a release notes entry if the change is major or breaking
- [ ] (Module updates) Added a release notes entry if the change is significant
- [ ] (Module addition) Added a release notes entry if adding a new NixOS module
- [x] Fits [CONTRIBUTING.md](https://github.com/NixOS/nixpkgs/blob/master/CONTRIBUTING.md).
<!--
To help with the large amounts of pull requests, we would appreciate your
reviews of other pull requests, especially simple package updates. Just leave a
comment describing what you have tested in the relevant package/service.
Reviewing helps to reduce the average time-to-merge for everyone.
Thanks a lot if you do!
List of open PRs: https://github.com/NixOS/nixpkgs/pulls
Reviewing guidelines: https://github.com/NixOS/nixpkgs/blob/master/pkgs/README.md#reviewing-contributions
-->
---
Add a :+1: [reaction] to [pull requests you find important].
[reaction]: https://github.blog/2016-03-10-add-reactions-to-pull-requests-issues-and-comments/
[pull requests you find important]: https://github.com/NixOS/nixpkgs/pulls?q=is%3Aopen+sort%3Areactions-%2B1-desc
4 Likes