Official NixOS Wiki

PostgreSQL: Difference between revisions

← Older edit
Philipwilk (talk | contribs)
4 edits
VisualWikitext
Klinger (talk | contribs)
trusted
946 edits
added link to nixos manual
Add subsection in upgrade instructions noting that checksumming is now enabled by default from v>18
 
(11 intermediate revisions by 9 users not shown)
Line 21: Line 21:
</syntaxhighlight>
</syntaxhighlight>


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).
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/run/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]
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.
=== Upgrading versions <18 to >=18 ===
Starting with v18, <code>initdb</code> defaults to enabling data checksums. This will prevent you from being able to upgrade from previous versions that did not have checksumming enabled, as <code>pg_upgrade</code> requires matching cluster checksum settings<ref>https://www.postgresql.org/docs/release/18.0/</ref>.
To maintain compatibility with a previous database, you may disable checksumming when creating the new database by using the appropriate option, <code>initdb --no-data-checksums ...</code>.
Alternatively, you can enable checksumming on the previous database with relatively little effort using <code>pg_checksums --pgdata={OLD_DATA_DIR} --enable --progress</code>. This must be done using the binary from the current postgres version, not from the version you wish to upgrade to.
== See also ==
== See also ==


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