Official NixOS Wiki

PostgreSQL: Difference between revisions

← Older edit
Patryk27 (talk | contribs)
5 edits
VisualWikitext
No edit summary
 
(2 intermediate revisions by 2 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 190: Line 200:
</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]
</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]


== TLS ==
== 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 205: 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 220: 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 227: 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 298: Line 338:
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:
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>The [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual] also has more detailed information about major upgrades.
<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"