PostgreSQL
 |
This article or section needs expansion.
Reason: This article is a stub. (Discuss in Talk:PostgreSQL#)
Please consult the pedia article metapage for guidelines on contributing. |
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 DB 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 postgresand
$ 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;
^