Official NixOS Wiki

PostgreSQL: Difference between revisions

← Older edit
Patryk27 (talk | contribs)
5 edits
VisualWikitext
Myypo (talk | contribs)
1 edit
m Changed /var/lib/postgresql to /var/run/postgresql because this is the actual location of the Unix socket.
No edit summary
 
(9 intermediate revisions by 7 users not shown)
Line 25: Line 25:
* [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]
It's also possible to setup PostgreSQL with [[Nix Darwin]] similar to how you would on NixOS, see the [https://daiderd.com/nix-darwin/manual/index.html#opt-services.postgresql.enable options].
It's also possible to setup PostgreSQL with [[Nix Darwin]] similar to how you would on NixOS, see the [https://daiderd.com/nix-darwin/manual/index.html#opt-services.postgresql.enable options].
====== Beginner's Note: ======
If you are studying Postgres by following its [https://www.postgresql.org/docs/current/tutorial.html official tutorial], you would find that the <code>pg_config</code> executable is missing [https://github.com/NixOS/nixpkgs/issues/408785]. To obtain <code>pg_config</code>, use the <code>pg_config</code> attribute of the <code>postgresql</code> package as in:
<syntaxhighlight lang="nix">
{
  # ...
  config.services.postgresql.package = pkgs.postgresql.pg_config;
} 
</syntaxhighlight>


=== Verify  setup ===
=== Verify  setup ===
Line 93: Line 103:
   # port = 5432;
   # port = 5432;
   authentication = pkgs.lib.mkOverride 10 ''
   authentication = pkgs.lib.mkOverride 10 ''
    #...
     #type database DBuser origin-address auth-method
     #type database DBuser origin-address auth-method
    local all      all    trust
    # ... other auth rules ...
     # ipv4
     # ipv4
     host  all      all    127.0.0.1/32  trust
     host  all      all    127.0.0.1/32  trust
     # ipv6
     # ipv6
     host all       all    ::1/128        trust
     host all     all    ::1/128        trust
   '';
   '';
   initialScript = pkgs.writeText "backend-initScript" ''
   initialScript = pkgs.writeText "backend-initScript" ''
Line 112: Line 124:
=== Set the Postgresql versions ===
=== Set the Postgresql versions ===


By default, NixOS uses whatever Postgres version shipped as default for your [https://search.nixos.org/options?show=system.stateVersion system.stateVersion]. To use a different or more recent version, you can set it manually:
By default, NixOS uses whatever Postgres version shipped as default for your [https://search.nixos.org/options?show=system.stateVersion system.stateVersion].  
 
To use a different or more recent version, you can manually set one of the [https://search.nixos.org/packages?query=postgresql_ available Nixpkgs Postgresql versions]:


<syntaxhighlight lang="nix">
<syntaxhighlight lang="nix">
Line 122: Line 136:
</syntaxhighlight>
</syntaxhighlight>


Note that changing the package version does not trigger any automatic migrations of your existing databases: if you update Postgres you should be ready to migrate any existing databases manually.
Note that changing the package version does not trigger any automatic migrations of your existing databases — follow [[#Major upgrades]] to migrate existing databases.
 
* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]


=== Security ===
=== Security ===
Line 179: Line 191:
One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.
One exception is the DB user "postgres", which by default is a superuser/admin with access to everything.


== TLS ==
=== Monitoring ===
A [[Prometheus]] exporter is available to export metrics to Prometheus-compatible storage.<syntaxhighlight lang="nix">
services.prometheus.exporters.postgres = {
    enable = true;
    listenAddress = "0.0.0.0";
    port = 9187;
};
</syntaxhighlight>[https://search.nixos.org/options?show=services.prometheus.exporters.postgres.dataSourceName&from=0&size=50&sort=relevance&type=packages&query=services.prometheus.exporters.postgres See all available options for services.prometheus.exporters.postgres]
 
== Remote Access ==


=== TLS ===
To turn TLS on in recent versions of postgres it's pretty easy. Their [https://www.postgresql.org/docs/current/ssl-tcp.html docs] are pretty good.
To turn TLS on in recent versions of postgres it's pretty easy. Their [https://www.postgresql.org/docs/current/ssl-tcp.html docs] are pretty good.


Line 194: Line 216:
Then in your nix configuration:
Then in your nix configuration:


<syntaxhighlight lang="nix">
<syntaxhighlight lang="nix">services.postgresql = {
  services.postgresql = {
     enable = true;
     enable = true;
     package = pkgs.postgresql_16;
     package = pkgs.postgresql_16;
Line 209: Line 230:
       host  sameuser    all    ::1/128 scram-sha-256
       host  sameuser    all    ::1/128 scram-sha-256
     '';
     '';
   };
   };</syntaxhighlight>
</syntaxhighlight>


the `sameuser` mentioned in the authentication section requires the database name be the same as the username, which you may not want, you can change that to `all` to allow an authenticated user the ability to connect to any database.
the `sameuser` mentioned in the authentication section requires the database name be the same as the username, which you may not want, you can change that to `all` to allow an authenticated user the ability to connect to any database.
Line 216: Line 236:
`scram-sha-256` is the require a password option, but you can authenticate a variety of different ways, see the official docs for other options as part of pg_hba.conf.
`scram-sha-256` is the require a password option, but you can authenticate a variety of different ways, see the official docs for other options as part of pg_hba.conf.


user creation and permissions are best described in the PG manual under `CREATE ROLE` and `GRANT`
=== User creation ===
for example:
NixOS can declaratively create or manage Postgres users with passwords.
<syntaxhighlight lang="sql">
 
CREATE USER tootieapp WITH PASSWORD 'BIGLONGRANDOMSTRINGHERE';
Postgres allows specifying the password as plaintext, MD5 hash or SCRAM-SHA-256 hash. For declarative configuration, the recommended format is SCRAM-SHA-256 and a hash can be obtained from a valid password for example using the following script:<syntaxhighlight lang="python3" line="1" start="1">
GRANT ALL PRIVILEGES ON DATABASE tootieapp TO tootieapp;
#!/usr/bin/env nix-shell
</syntaxhighlight>
#! nix-shell -i python3 -p "python3.withPackages (ps: [ps.scramp])"
import scramp, base64
 
PASSWORD = 'secure_password123!'


m = scramp.ScramMechanism()
salt, stored_key, server_key, iteration_count = m.make_auth_info(PASSWORD)
print(f"SCRAM-SHA-256${iteration_count}:{base64.b64encode(salt).decode()}${base64.b64encode(stored_key).decode()}:{base64.b64encode(server_key).decode()}")
</syntaxhighlight>Using this hash, we can then extend the above configuration as follows:<syntaxhighlight lang="nix">services.postgresql = {
  enable = true;
  package = pkgs.postgresql_16;
  enableTCPIP = true;
  ensureDatabases = [ "tootieapp" ];
  settings = {
      ssl = true;
  };
  authentication = pkgs.lib.mkOverride 10 ''
    #type database  DBuser  auth-method
    local all      all    trust
    host  sameuser    all    127.0.0.1/32 scram-sha-256
    host  sameuser    all    ::1/128 scram-sha-256
  '';
  ensureUsers = [
    {
      name = "tootieapp";
      ensureDBOwnership = true;
      ensureClauses = {
        login = true;
        password = "SCRAM-SHA-256$4096:lB4tguN+gvNVSqk0zGRPHQ==$zh48o1bb9tuRjvGQHh/CeobEyUI4u91rp0K9who8m3I=:mHxc6obGad8/g65+V3C84UQGHIK41Gfx32+xXSZiOss=";
      };
    }
  ];
};</syntaxhighlight>For more details on how to create hashes from valid passwords also refer to https://gist.github.com/jkatz/e0a1f52f66fa03b732945f6eb94d9c21


== Debugging with <code>psql</code> ==
== Debugging with <code>psql</code> ==
Line 257: Line 308:
<syntaxhighlight lang="nix">
<syntaxhighlight lang="nix">
root$ psql -U postgres
root$ psql -U postgres
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgress"
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"
</syntaxhighlight>
</syntaxhighlight>


Line 280: Line 331:
outline=# ALTER DATABASE outline REFRESH COLLATION VERSION;
outline=# ALTER DATABASE outline REFRESH COLLATION VERSION;
</syntaxhighlight>
</syntaxhighlight>
== Major upgrades ==
If you're using NixOS' modules for PostgreSQL and find yourself in a boot/switch after a major bump of it, you'll need to upgrade your cluster.
Let the service successfully start once, and then stop it. Upon completion, proceed with the following command, substituting the numbers 15 and 16 with the respective versions you previously used and the more recent one:
<syntaxhighlight>sudo -u postgres pg_upgrade -b "$(nix build --no-link --print-out-paths nixpkgs#postgresql_15.out)/bin" -B /run/current-system/sw/bin -d /var/lib/postgresql/15 -D /var/lib/postgresql/16</syntaxhighlight>
If this fails with the <code>Only the install user can be defined in the new cluster</code> message, you might have some luck using <code>initdb</code> to create the data directory by hand instead of relying on the <code>postgresql</code> systemd service to do that.
Following the example above - upgrading to Postgresql 16 - you'd do:
<syntaxhighlight>rm -rf /var/lib/postgresql/16 
sudo -u postgres initdb -D /var/lib/postgresql/16
sudo -u postgres pg_upgrade ...</syntaxhighlight>
Triple check you're not actually <code>rm -rf</code>'ing your actual (previous) database! This is meant to remove only the empty database created by the newer Postgresql version, so that <code>initdb</code> starts with a clean slate.
[https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also contains useful information about this kind of upgrades.
== See also ==
== See also ==


Retrieved from "https://wiki.nixos.org/wiki/PostgreSQL"