PostgreSQL: Difference between revisions
added link to nixos manual
imported>Hofsiedge m (Added a missing > in a </code> tag in the "Verify setup" section) |
(added link to nixos manual) |
||
| (10 intermediate revisions by 7 users not shown) | |||
| Line 1: | Line 1: | ||
[https://www.postgresql.org/ PostgreSQL] also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. | |||
This article extends the documentation in the [https://nixos.org/manual/nixos/stable/#module-postgresql NixOS manual]. | |||
=== Getting started === | === Getting started === | ||
To try out Postgresql add the following minimal example to your [ | To try out Postgresql add the following minimal example to your [[NixOS modules | NixOS module]]: | ||
<syntaxhighlight lang="nix"> | <syntaxhighlight lang="nix"> | ||
| Line 22: | Line 24: | ||
* [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]. | |||
=== Verify setup === | === Verify setup === | ||
| Line 109: | Line 112: | ||
=== Set the Postgresql versions === | === Set the Postgresql versions === | ||
By default, NixOS uses whatever Postgres version | 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: | ||
<syntaxhighlight lang="nix"> | <syntaxhighlight lang="nix"> | ||
| Line 118: | Line 121: | ||
}; | }; | ||
</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. | |||
* [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions] | * [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions] | ||
| Line 166: | Line 171: | ||
authentication = pkgs.lib.mkOverride 10 '' | authentication = pkgs.lib.mkOverride 10 '' | ||
#type database DBuser auth-method optional_ident_map | #type database DBuser auth-method optional_ident_map | ||
local sameuser | local sameuser all peer map=superuser_map | ||
''; | ''; | ||
}; | }; | ||
| Line 173: | Line 178: | ||
With "sameuser" Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part <code>map=superuser_map</code> is optional. | With "sameuser" Postgres will allow DB user access only to databases of the same name. E.g. DB user "mydatabase" will get access to database "mydatabase" and nothing else. The part <code>map=superuser_map</code> is optional. | ||
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 == | |||
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. | |||
Create a simple cert just to make it work. If you are doing this in production, you need to provide your own server.crt and server.key in the main PGDATA dir (~postgres). | |||
In a shell: | |||
<syntaxhighlight lang="nix"> | |||
cd ~postgres | |||
sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=dbhost.yourdomain.com" | |||
chmod og-rwx server.key | |||
</syntaxhighlight> | |||
Then in your nix configuration: | |||
<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 | |||
''; | |||
}; | |||
</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. | |||
`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` | |||
for example: | |||
<syntaxhighlight lang="sql"> | |||
CREATE USER tootieapp WITH PASSWORD 'BIGLONGRANDOMSTRINGHERE'; | |||
GRANT ALL PRIVILEGES ON DATABASE tootieapp TO tootieapp; | |||
</syntaxhighlight> | |||
== Debugging with <code>psql</code> == | == Debugging with <code>psql</code> == | ||
| Line 192: | Line 242: | ||
</pre> | </pre> | ||
== | == Troubleshooting == | ||
=== Connection rejected with "Role does not exist" === | === Connection rejected with "Role does not exist" === | ||
| Line 212: | Line 262: | ||
You are trying to login as a DB user ("postgres" in this example) for which your current system user ("root" in this example) has no permission to switch to. Check your "user name map" in the <code>identMap</code> section. | You are trying to login as a DB user ("postgres" in this example) for which your current system user ("root" in this example) has no permission to switch to. Check your "user name map" in the <code>identMap</code> section. | ||
=== WARNING: database "XXX" has a collation version mismatch === | |||
The complete error which appears in the system log might look similar to this | |||
<syntaxhighlight> | |||
WARNING: database "outline" has a collation version mismatch | |||
DETAIL: The database was created using collation version 2.35, but the operating system provides version 2.38. | |||
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE outline REFRESH COLLATION VERSION, or build PostgreSQL with the right library version. | |||
</syntaxhighlight> | |||
To fix it, run following commands in the psql console. Replace the database name <code>outline</code> with the name of the database which you want to migrate | |||
<syntaxhighlight> | |||
sudo -u postgres psql | |||
postgres=# \c outline; | |||
outline=# REINDEX DATABASE outline; | |||
outline=# ALTER DATABASE outline REFRESH COLLATION VERSION; | |||
</syntaxhighlight> | |||
== See also == | == See also == | ||
| Line 218: | Line 286: | ||
[[Category:Applications]] | [[Category:Applications]] | ||
[[Category:Database]] | |||
[[Category:NixOS Manual]] | |||