PostgreSQL - initialScript script not working

1

Hi,

I would like to install a PostgreSQL database and create a user at the same time. This is my configuration:

mypostgresql = {
  db = "SUPER";
  user = "SUPER";
  pass = "SUPER";
};

...

services.postgresql = {
  enable = true;
  package = pkgs.postgresql_11;
  initialScript = pkgs.writeText "Initial-PostgreSQL-database" ''
    CREATE DATABASE "${mypostgresql.db}";
    CREATE USER "${mypostgresql.user}" WITH ENCRYPTED PASSWORD "${mypostgresql.pass}";
    GRANT ALL PRIVILEGES ON DATABASE ${mypostgresql.db} TO ${mypostgresql.user};
  '';
};

I think the problem is the apostrophe:

CREATE USER "SUPER" WITH ENCRYPTED PASSWORD "SUPER";
ERROR:  syntax error at or near ""SUPER""
LINE 1: CREATE USER "SUPER" WITH ENCRYPTED PASSWORD "SUPER";

CREATE USER "SUPER" WITH ENCRYPTED PASSWORD 'SUPER';
CREATE ROLE

How can I solve this problem?

Thanks!

2

Strings in postgresql are quoted using ', not ".

1 Like
3

Thanks for your input.

This is working:

  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_11;
    initialScript = pkgs.writeText "Initial-PostgreSQL-Database" ''
      CREATE DATABASE ${mypostgresql.db};
      CREATE USER ${mypostgresql.user} WITH ENCRYPTED PASSWORD '${mypostgresql.pass}';
      GRANT ALL PRIVILEGES ON DATABASE ${mypostgresql.db} TO ${mypostgresql.user};
    '';

Perfect! Next… :slight_smile:

4

Or if you were using the created user and database locally you could do this:

services.postgresql.ensureDatabases = [ mypostgresql.db ];
services.postgresql.ensureUsers = [
  { name = mypostgresql.user;
    ensurePermissions = {
      "DATABASE ${mypostgresql.db}" = "ALL PRIVILEGES";
    };
  }
];

Please keep in mind the documentation for initialScript states the script will only be run the very first time postgresql starts. The script will have no impact on existing installs.

5

Very good input! But if I try your example, I get the following message:

error: The option `services.postgresql.ensureDatabases' defined in `/etc/nixos/configuration.nix' does not exist.

Can’t find it here either:
https://nixos.org/nixos/options.html#services.postgresql

6

Sorry, I should have mentioned this feature is currently only available in nixos-unstable. It will hit nixos stable in the 19.09 release.

1 Like