I am currently following the NixOS manual to setup a reproducible and idempotent setup of a PostgreSQL database. I was able to create tables, views, users, etc. but I am not sure how to execute
ALTER ROLE dbuser WITH ENCRYPTED PASSWORD 'my-secret-password';
without letting the password leak into the nix store. I use agenix and have the password ready to be read from a file.
I think that solution is somewhat problematic, since the password will be passed in plaintext through the command line args, which in turn are world-readable while the command is running. This gives a short window during which an attacker could in theory intercept your database password (though this is unlikely to happen in practice), or some kind of logging or monitoring software could capture it.
I don’t think there’s a much better way either, though. Support for supplying passwords must always be implemented by the application in question, and postgres’ documentation doesn’t list any support for reading query data from files or environment variables.
I think the best possible solution is to use replace-secret to write an sql script and to execute that with your systemd unit, making sure the file never ends up world-readable and deleting all traces of the file after init is done.
Hard to declare that “idiomatic”, the idiomatic approach appears to be using local socket auth, but this approach is used elsewhere when passwords must be used and the application doesn’t natively support reading them from files.
To my knowledge the password is not leaked in the process view. Which method of password interception did you have in mind? I also did a quick test with sleep 10 <<< "my-secret" and in a parallel terminal
Just to be clear, by “local socket auth” you mean PostgreSQL’s peer authentication? This does not work for me unfortunately, since I need to have two users with different access to the same table and can’t have the user name be identical to the table name. This is a requirement for peer AFAIK.
3. replace-string
Thank you for the suggestion. I will add it to my toolbox.
Yeah, you’re passing a file with a bash heredoc to stdin there. That’s not an argument and is never passed through the kernel’s command line handling, it’s a bash feature.
Note this also applies when you use echo or printf (because those are bash built-in functions, rather than binaries executed by the kernel).
This however will run a subshell, and then pass the resulting output from stdout as a plaintext string through the kernel’s commad line handling:
Not the end of the world for short-term processes like this, but hilariously inappropriate for long-running daemons and whatnot, and kind of an issue if you have anything recording your process args.
Honestly this kernel feature is a massive issue, I doubt 99% of software engineers ever think about this, there’s probably tons of systems vulnerable to privilege escalation because of it.
Yep. There are lots of reasons why this might not be an option, but it’s the most common solution for single-host NixOS systems, hence I’d consider it “idiomatic”. idiomatic approaches aren’t always possible or the best, though.
Using TLATER’s suggestions and some further research I have created the following service declaration:
systemd.services."postgresql-declarative-db-setup" = {
serviceConfig = {
Type = "oneshot";
User = "postgres";
};
requiredBy = "service-that-uses-db-name.service";
after = ["postgresql.service"];
path = with pkgs; [ postgresql_16 replace-secret];
script = ''
# set bash options for early fail and error output
set -o errexit -o pipefail -o nounset -o errtrace
shopt -s inherit_errexit
# define a trap so that modified SQL file is deleted even if this script fails
trap '[ -n "$tmp_file" ] && rm -rf "$tmp_file"' EXIT
# create empty file with random name in /tmp
tmp_file=$(mktemp)
# copy SQL template into created file
install --mode 600 ${./db-init.sql} ''${tmp_file}
# fill SQL template with passwords
${pkgs.replace-secret}/bin/replace-secret @DB_ADMIN_PASSWORD@ /var/run/agenix/postgresql-db-admin-password ''${tmp_file}
${pkgs.replace-secret}/bin/replace-secret @DB_USER_PASSWORD@ /var/run/agenix/postgresql-db-user-password ''${tmp_file}
# run filled SQL template
psql db-name --file "''${tmp_file}"
'';
};
This requires a db-init.sql file which is in the same folder and contains the entire db setup with something like
CREATE TABLE IF NOT EXISTS ...
CREATE OR REPLACE VIEW ...
CREATE OR REPLACE FUNCTION ...
GRANT ...
ALTER ROLE db_admin WITH ENCRYPTED PASSWORD '@DB_ADMIN_PASSWORD@';
ALTER ROLE db_user WITH ENCRYPTED PASSWORD '@DB_USER_PASSWORD@';
Looks ok to me. Rather than mktemp and the awkward trap I would use systemd’s RuntimeDirectory, though. Also consider what happens if the service runs twice.
I incorporated RuntimeDirectory, as suggested, which removes the trap and mktemp. Also changed the paths to use agenix variables. I will mark this as the solution, but still welcome further critique.
systemd.services."postgresql-db-name-setup" = {
serviceConfig = {
Type = "oneshot";
User = "postgres";
};
requiredBy = "service-that-uses-db-name.service";
after = ["postgresql.service"];
path = with pkgs; [ postgresql_16 replace-secret];
serviceConfig = {
RuntimeDirectory = "postgresql-setup";
RuntimeDirectoryMode = "700";
};
script = ''
# set bash options for early fail and error output
set -o errexit -o pipefail -o nounset -o errtrace -o xtrace
shopt -s inherit_errexit
# Copy SQL template into temporary folder. The value of RuntimeDirectory is written into
# environment variable RUNTIME_DIRECTORY by systemd.
install --mode 600 ${./db-name.sql} ''$RUNTIME_DIRECTORY/init.sql
# fill SQL template with passwords
${pkgs.replace-secret}/bin/replace-secret @DB_ADMIN_PASSWORD@ ${config.age.secrets.postgresql-db-admin-password.path} ''$RUNTIME_DIRECTORY/init.sql
${pkgs.replace-secret}/bin/replace-secret @DB_USER_PASSWORD@ ${config.age.secrets.postgresql-db-user-password.path} ''$RUNTIME_DIRECTORY/init.sql
# run filled SQL template
psql db-name --file "''$RUNTIME_DIRECTORY/init.sql"
'';
};
That won’t leak it to the nix store. ${config.sops.secrets.stocky_db_password.path} will resolve to something like /run/secrets/stocky_db_password, which in turn will be stored as part of the script in the nix store. That’s not a secret, it’s just a file path which can only be accessed at runtime and by the user/group set up to be allowed to access it at runtime.
The only other part of that script that will be evaluated by nix and therefore end up in the nix store is ${dbUser}, which is also not a secret.
It also won’t leak via /proc, because the secret doesn’t become an argument to anything, it’s stored in a variable which is entirely bash-internal and therefore doesn’t hit the kernel.