PostgreSQL: Difference between revisions

(14 intermediate revisions by 10 users not shown)

Line 1:

~~{{expansion}}~~

[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 ===

To try out Postgresql add the following minimal example to your [~~https://nixos.wiki/wiki/NixOS_modules~~ NixOS module]:

To try out Postgresql add the following minimal example to your [[NixOS modules | NixOS module]]:

Line 19:

Line 21:

</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]

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 ===

Line 34:

Line 37:

</syntaxhighlight>

We have to switch to a system user like "postgres" with <code>sudo -u postgres</code, because by default <code>psql</code> logs you into the DB user of the same name as the current Linux/system user. By default, NixOS creates a system and DB user names "postgres".

We have to switch to a system user like "postgres" with <code>sudo -u postgres</code>, because by default <code>psql</code> logs you into the DB user of the same name as the current Linux/system user. By default, NixOS creates a system and DB user names "postgres".

So the line <code>postgres=# </code> shows that we are now logged-in as DB user "postgres".

Line 109:

Line 112:

=== Set the Postgresql versions ===

By default, NixOS uses whatever Postgres version ~~that comes with the~~ [https://search.nixos.org/~~packages~~?~~query~~=~~postgresql "pkgs~~.~~postgresql" package~~]. To ~~avoid sudden breaking changes~~ you can ~~fix the Postgres version by using a more specific Nix package~~:

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:

Line 118:

Line 121:

};

</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]

Line 155:

Line 160:

</syntaxhighlight>

==== ~~Harden authentication~~ ====

==== Limit Access ====

Once logged-in we can '''limit what DB users can access'''. With the <code>authentication</code> we can limit what

Line 166:

Line 171:

authentication = pkgs.lib.mkOverride 10 ''

#type database DBuser auth-method optional_ident_map

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.

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:

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:

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:

CREATE USER tootieapp WITH PASSWORD 'BIGLONGRANDOMSTRINGHERE';

GRANT ALL PRIVILEGES ON DATABASE tootieapp TO tootieapp;

</syntaxhighlight>

== Debugging with <code>psql</code> ==

Line 192:

Line 242:

</pre>

== ~~FAQ~~ ==

== Troubleshooting ==

=== Connection rejected with "Role does not exist" ===

Line 211:

Line 261:

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

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

sudo -u postgres psql

postgres=# \c outline;

outline=# REINDEX DATABASE outline;

outline=# ALTER DATABASE outline REFRESH COLLATION VERSION;

</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:

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>

== See also ==

Line 218:

Line 297:

[[Category:Applications]]

[[Category:Database]]

[[Category:NixOS Manual]]