PostgreSQL: Difference between revisions

From NixOS Wiki
imported>Malteneuss
m Fix typo
imported>Malteneuss
Add verification with psql section
Line 22: Line 22:


* [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]
=== Verify  setup ===
You can use <code>psql</code> that comes with Postgres in the terminal to verify that the DB setup is as expected:
<syntaxhighlight lang="bash">
$ sudo -u postgres psql
psql
Type "help" for help.
postgres=#
</syntaxhighlight>
We have to switch to a system user like "postgres" with <code>sudo -u postgres</code, because by default <code>psql</code> 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 <code>postgres=# </code> shows that we are now logged-in as DB user "postgres".
Inside <code>psql</code> here the most frequent commands are:
List all databases running on this Postgres instance with <code>\l</code>:
<syntaxhighlight lang="bash">
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)
</syntaxhighlight>
List all DB users (also called "roles" in Postgres) with <code>\du</code>:
<syntaxhighlight lang="bash">
postgres=# \du
                                  List of roles
Role name |                        Attributes                        | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
</syntaxhighlight>
List all authentication rules (called an "pg_hba.conf" file in Postgres ) with <code>table pg_hba_file_rules;</code>:
<syntaxhighlight lang="bash">
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)
</syntaxhighlight>
* [https://www.postgresql.org/docs/current/auth-pg-hba-conf.html Official Postgres authentication pg_hba.conf documentation]


=== Allow TCP/IP connections ===
=== Allow TCP/IP connections ===
Line 65: Line 121:
* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]
* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]


== Using <code>psql</code> ==
== Debugging with <code>psql</code> ==
 
Depending on the <code>system.stateVersion</code> of your system, the [https://github.com/NixOS/nixpkgs/blob/release-17.09/nixos/modules/services/databases/postgresql.nix#L151 default super-user username] will change:
 
* 17.03 and earlier: the superuser is <code>root</code>
* 17.09 and later: the superuser is <code>postgres</code>
 
There is no password, [https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-IDENT Ident Authentication] [https://github.com/NixOS/nixpkgs/blob/release-17.09/nixos/modules/services/databases/postgresql.nix#L182 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:
 
<syntaxhighlight lang="commands">
$ 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)
</syntaxhighlight>
 
The first error is not an error with the credentials, but an error coming from the default behaviour of <code>psql</code> 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:
 
<syntaxhighlight lang="commands">
$ psql -U postgres
</syntaxhighlight>
 
and
 
<syntaxhighlight lang="commands">
$ psql -U postgres -l
</syntaxhighlight>
 
 


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.
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.
Line 122: Line 138:
                                                                 ^
                                                                 ^
</pre>
</pre>
== FAQ ==
=== Connection rejected with "Role does not exist" ===
<syntaxhighlight lang="nix">
$ psql
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist
</syntaxhighlight>
You are trying to login as a system user ("root" in this example) that has no DB user of the same name. Try <code>psql -U postgres</code> or <code>sudo -u postgres psql</code> to log in as a different DB user.


== See also ==
== See also ==

Revision as of 11:31, 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).

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</code, 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;
  # ...
};

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.

See also