Set password for a PostgreSQL user from a file (agenix)

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 came up with the following command which I add to the string in the script key in the attribute list provided in the manual:

psql mydatabase --set=password="$(cat /var/run/agenix/postgresql-dbuser-password)" <<< "ALTER ROLE dbuser WITH ENCRYPTED PASSWORD :'password';"

Is there a better, more idiomatic, way to do this?

1 Like

I’m also interested in doing this. Authelia needs its PostgreSQL user to have a password. Please let me know if you figure something out.

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.

1. password interception

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

$ ps aux | grep sleep
tiez      9151  0.0  0.0  12592  2464 pts/2    S+   21:00   0:00 sleep 10
tiez      9153  0.0  0.0   7024  2824 pts/1    S+   21:00   0:00 grep --color=auto sleep

No password visible here.

2. socket auth

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:

tlater ~ $ echo '10' > /tmp/supersecret
tlater ~ $ sudo sleep $(cat /tmp/supersecret) &
[1] 6237
tlater ~ $ ps a | grep sleep
   6237 pts/1    SN     0:00 sudo sleep 10
   6255 pts/4    SNs+   0:00 sudo sleep 10
   6256 pts/4    SN     0:00 sleep 10
   6288 pts/1    S+     0:00 grep sleep

Note the sleep 10, you should not be able to see how long sleep should be sleeping for if the secret wasn’t exposed.

So the problem in your systemd unit is this:

--set=password="$(cat /var/run/agenix/postgresql-dbuser-password)"

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.

There are at least a handful of NixOS modules which are still vulnerable to this, too, it’s a mixture of people not taking it seriously (or not believing it ;p) and it being the only option for lots of applications: NixOS modules: Secrets provided in arguments are exposed to unprivileged users · Issue #156400 · NixOS/nixpkgs · GitHub

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.

2 Likes

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@'; 

@TLATER, feedback would be very much appreciated.

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.

1 Like

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"
'';
};
2 Likes

How would you do this if you have multiple database users?

i use sops-nix, probably works similarly.

  sops.secrets.stocky_db_password = {
    owner = "postgres";
  };

  systemd.services."postgres-user-setup" = let
    dbUser = "stocky";
    dbPasswordPath = config.sops.secrets.db_password.path;
  in {
    serviceConfig.Type = "oneshot";
    wantedBy = [ "postgresql.service" ];
    after = [ "postgresql.service" ];
    serviceConfig.User = "postgres";
    environment.PSQL = "psql --port=${toString config.services.postgresql.port}";
    path = [
      pkgs.gnugrep
      pkgs.postgresql
    ];
    script = ''
      password=$(cat "${config.sops.secrets.stocky_db_password.path}")

      $PSQL -tXA \
        -c "SELECT 1 FROM pg_roles WHERE rolname=\"${dbUser}\"" | grep -q 1 \
        || $PSQL \
          -c "CREATE ROLE \"${dbUser}\" WITH LOGIN CREATEROLE CREATEDB ENCRYPTED PASSWORD '$password'"
            # ....
    '';
  };

@PopeRigby you could take this and extract out to apply to an attribute set of users and sops-secrets-passwords.

edit:
Some good hints and alternatives in nixpkgs section on postgresql: https://github.com/NixOS/nixpkgs/blob/ced4e31b4a3a96ca7805546c1ffa03f730a356df/nixos/modules/services/databases/postgresql.md

you could take this and extract out to apply to an attribute set of users and sops-secrets-passwords.

I’m pretty new to Nix, how would I do that?

I have been using the following solution. I use sops-nix, but it should work with age-nix, or any other similar alternative:

  sops.secrets.POSTGRES_PASSWORD = {
    owner = config.systemd.services.postgresql.serviceConfig.User;
    restartUnits = [ "postgresql.service" ];
  };

  systemd.services.postgresql.postStart =
    let
      password_file_path = config.sops.secrets.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 ${db-name} WITH PASSWORD '''%s''';', password);
        END $$;
      EOF
    '';