PostgreSQL: Difference between revisions

From NixOS Wiki
imported>Malteneuss
Add simplified getting started section
imported>Malteneuss
m Fix indentations
Line 13: Line 13:
     authentication = pkgs.lib.mkOverride 10 ''
     authentication = pkgs.lib.mkOverride 10 ''
       #type database  DBuser  auth-method
       #type database  DBuser  auth-method
       local all             all           trust
       local all       all     trust
     '';
     '';
   };
   };
Line 19: Line 19:
</syntaxhighlight>
</syntaxhighlight>


This will setup Postgresql with a database "mydatabase" and let every local Linux user have access to it without a password through a "local" Unix socket "/var/lib/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).
This will setup Postgresql with a default DB superuser/admin "postgres", a database "mydatabase" and let every DB user have access to it without a password through a "local" Unix socket "/var/lib/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).


* [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options]
* [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service options]
Line 35: Line 35:
   authentication = pkgs.lib.mkOverride 10 ''
   authentication = pkgs.lib.mkOverride 10 ''
     #...
     #...
     #type database DBuser origin-address auth-method
     #type database DBuser origin-address auth-method
     # ipv4
     # ipv4
     host   all             all         127.0.0.1/32   trust
     host all     all     127.0.0.1/32   trust
     # ipv4
     # ipv6
     host   all             all           ::1/128           trust
     host all       all     ::1/128       trust
   '';
   '';
   initialScript = pkgs.writeText "backend-initScript" ''
   initialScript = pkgs.writeText "backend-initScript" ''

Revision as of 10:50, 15 July 2023

Getting started

To try out Postgresql add the following minimal example to your NixOS module:

{
  # ...
  config.services.postgresql = {
    enable = true;
    ensureDatabases = [ "mydatabase" ];
    authentication = pkgs.lib.mkOverride 10 ''
      #type database  DBuser  auth-method
      local all       all     trust
    '';
  };
}

This will setup Postgresql with a default DB superuser/admin "postgres", a database "mydatabase" and let every DB user have access to it without a password through a "local" Unix socket "/var/lib/postgresql" (TCP/IP is disabled by default because it's less performant and less secure).

Allow TCP/IP connections

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

services.postgresql = {
  enable = true;
  ensureDatabases = [ "mydatabase" ];
  enableTCPIP = true;
  # port = 5432;
  authentication = pkgs.lib.mkOverride 10 ''
    #...
    #type database DBuser origin-address auth-method
    # ipv4
    host  all      all     127.0.0.1/32   trust
    # ipv6
    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;
  '';
};

This will allow "host" based authentification only from other webservices on the same computer ("127.0.0.1"), although any user will have access to any database.

Set the Postgresql versions

By default, NixOS uses whatever Postgres version that comes with the "pkgs.postgresql" package. To avoid sudden breaking changes you can fix the Postgres version by using a more specific Nix package:

services.postgresql = {
  enable = true;
  package = pkgs.postgresql_15;
  # ...
};

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.

Examples for a 17.09 stateVersion and later:

 $ psql -U postgres

and

 $ psql -U postgres -l


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;
                                                                ^

See also