PostgreSQL

☶︎

This article or section needs to be expanded. Further information may be found in the related discussion page. Please consult the pedia article metapage for guidelines on contributing.

Using psql

Depending on the system.stateVersion of your system, the default super-user username will change:

• 17.03 and earlier: the superuser is root
• 17.09 and later: the superuser is postgres

There is no password, Ident Authentication is used. This means that you can access the database using a system user named like the database user.

Example for a 17.03 stateVersion.

 $ sudo -u root psql
psql: FATAL:  database "root" does not exist
 $ sudo -u root psql -l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | root  | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 |
 template0 | root  | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/root          +
           |       |          |             |             | root=CTc/root
 template1 | root  | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/root          +
           |       |          |             |             | root=CTc/root
(4 rows)

The first error is not an error with the credentials, but an error coming from the default behaviour of psql that is trying to use a database name named like the user logging-in. The second command lists tables available.

Deploying databases

This example shows how to roll out a database with a default user and password:

  services.postgresql = {
    enable = true;
    package = pkgs.postgresql100;
    enableTCPIP = true;
    authentication = pkgs.lib.mkOverride 10 ''
      local all all trust
      host all all ::1/128 trust
    '';
    initialScript = pkgs.writeText "backend-initScript" ''
      CREATE ROLE nixcloud WITH LOGIN PASSWORD 'nixcloud' CREATEDB;
      CREATE DATABASE nixcloud;
      GRANT ALL PRIVILEGES ON DATABASE nixcloud TO nixcloud;
    '';

To debug the SQL statements futher, one can use systemctl cat postgresql and see the ExecStartPost=/nix/store/rnv1v95bbf2lsy9ncwg7jdwj2s71sqra-unit-script/bin/postgresql-post-start line. Then open it with `cat` on the shell and see the psql command.

Then execute the complete statement on the shell, as:

/nix/store/3mqha1naji34i6iv78i90hc20dx0hld9-sudo-1.8.20p2/bin/sudo -u postgres psql -f "/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript" --port=5432 -d postgres
psql:/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript:1: ERROR:  syntax error at or near "-"
LINE 1: CREATE ROLE nixcloud-admin WITH LOGIN PASSWORD 'nixcloud' CR...
                            ^
psql:/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript:2: ERROR:  database "nixcloud-db1" already exists
psql:/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript:3: ERROR:  syntax error at or near "-"
LINE 1: ...ALL PRIVILEGES ON DATABASE "nixcloud-db1" TO nixcloud-admin;
                                                                ^