Official NixOS Wiki

PostgreSQL: Difference between revisions

← Older edit
VisualWikitext
Add link to nixos manual for postgres upgrades
→Getting started: Added a note mentioning the pg_config executable
 
(3 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 114: 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 124: 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 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> ==
Retrieved from "https://wiki.nixos.org/wiki/PostgreSQL"