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).
Verify setup
You can use psql that comes with Postgres in the terminal to verify that the DB setup is as expected:
$ sudo -u postgres psql
psql
Type "help" for help.
postgres=#
We have to switch to a system user like "postgres" with sudo -u postgres, because by default psql logs you into the DB user of the same name as the current Linux/system user. By default, NixOS creates a system and DB user names "postgres".
So the line postgres=# shows that we are now logged-in as DB user "postgres".
Inside psql here the most frequent commands are:
List all databases running on this Postgres instance with \l:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+-------------+-------------+------------------------
mydatabase | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | rustnixos=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
List all DB users (also called "roles" in Postgres) with \du:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
List all authentication rules (called an "pg_hba.conf" file in Postgres ) with table pg_hba_file_rules;:
postgres=# table pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+----------+-----------+---------+---------+-------------+---------+-------
1 | local | {all} | {all} | | | peer | |
(1 row)
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;
# ...
};
Security
Letting every system and DB user have access to all data is dangerous. Postgres supports several layers of protection. One is to prefer "local" connections using Unix sockets, that aren't accessible from the internet, whenever Postgres and your client app run on the same server.
Harden authentication
We can limit what system user can connect.
Postgres supports "user name maps", which limit which system users can log in as which DB user:
services.postgresql = {
enable = true;
ensureDatabases = [ "mydatabase" ];
identMap = ''
# ArbitraryMapName systemUser DBUser
superuser_map root postgres
superuser_map postgres postgres
# Let other names login as themselves
superuser_map /^(.*)$ \1
'';
};
This map can have an arbitrary name and defines which system user can login as which DB user. Every other user and combination is rejected. For example, with the above mapping if we are logged-in as system user "root" but want enter the DB as DB user "postgres" we would be allowed:
root$ psql -U postgres
# ok
Limit Access
Once logged-in we can limit what DB users can access. With the authentication we can limit what
DB user can access which databases. A good default setting is as follows:
services.postgresql = {
enable = true;
ensureDatabases = [ "mydatabase" ];
authentication = pkgs.lib.mkOverride 10 ''
#type database DBuser auth-method optional_ident_map
local sameuser all peer map=superuser_map
'';
};
With "sameuser" Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part map=superuser_map is optional.
One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.
Debugging with psql
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;
^
FAQ
Connection rejected with "Role does not exist"
$ psql
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist
You are trying to login as a system user ("root" in this example) that has no DB user of the same name. Try psql -U postgres or sudo -u postgres psql to log in as a different DB user.
Connection rejected with "Peer authentication failed"
root$ psql -U postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgress"
You are trying to login as a DB user ("postgres" in this example) for which your current system user ("root" in this example) has no permission to switch to. Check your "user name map" in the identMap section.