PostgreSQL: Difference between revisions
imported>Malteneuss m Fix indentations |
added link to nixos manual |
||
(15 intermediate revisions by 8 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 === | |||
You can use <code>psql</code> that comes with Postgres in the terminal to verify that the DB setup is as expected: | |||
<syntaxhighlight lang="bash"> | |||
$ sudo -u postgres psql | |||
psql | |||
Type "help" for help. | |||
postgres=# | |||
</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". | |||
So the line <code>postgres=# </code> shows that we are now logged-in as DB user "postgres". | |||
Inside <code>psql</code> here the most frequent commands are: | |||
List all databases running on this Postgres instance with <code>\l</code>: | |||
<syntaxhighlight lang="bash"> | |||
postgres=# \l | |||
List of databases | |||
Name | Owner | Encoding | Collate | Ctype | Access privileges | |||
------------+----------+----------+-------------+-------------+------------------------ | |||
mydatabase | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | |||
| | | | | postgres=CTc/postgres + | |||
| | | | | rustnixos=CTc/postgres | |||
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | |||
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | |||
| | | | | postgres=CTc/postgres | |||
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | |||
| | | | | postgres=CTc/postgres | |||
(4 rows) | |||
</syntaxhighlight> | |||
List all DB users (also called "roles" in Postgres) with <code>\du</code>: | |||
<syntaxhighlight lang="bash"> | |||
postgres=# \du | |||
List of roles | |||
Role name | Attributes | Member of | |||
-----------+------------------------------------------------------------+----------- | |||
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | |||
</syntaxhighlight> | |||
List all authentication rules (called an "pg_hba.conf" file in Postgres ) with <code>table pg_hba_file_rules;</code>: | |||
<syntaxhighlight lang="bash"> | |||
postgres=# table pg_hba_file_rules; | |||
line_number | type | database | user_name | address | netmask | auth_method | options | error | |||
-------------+-------+----------+-----------+---------+---------+-------------+---------+------- | |||
1 | local | {all} | {all} | | | peer | | | |||
(1 row) | |||
</syntaxhighlight> | |||
* [https://www.postgresql.org/docs/current/auth-pg-hba-conf.html Official Postgres authentication pg_hba.conf documentation] | |||
=== Allow TCP/IP connections === | === Allow TCP/IP connections === | ||
Line 49: | Line 108: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
This will allow "host" based authentification only from other webservices on the same computer ("127.0.0.1"), although any user will have access to any database. | This will allow "host" based authentification only from other webservices on the same computer ("127.0.0.1"), although any DB user will have access to any database. | ||
=== 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 62: | 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] | ||
== | === Security === | ||
Letting every system and DB user have access to all data is dangerous. Postgres supports several layers of protection. | |||
One is to '''prefer "local" connections using Unix sockets''', that aren't accessible from the internet, whenever Postgres and your client app run on the same server. | |||
==== Harden authentication ==== | |||
We can '''limit what system user can connect'''. | |||
Postgres supports [https://www.postgresql.org/docs/current/auth-username-maps.html "user name maps"], which limit which system users can log in as which DB user: | |||
<syntaxhighlight lang="nix"> | |||
services.postgresql = { | |||
enable = true; | |||
ensureDatabases = [ "mydatabase" ]; | |||
identMap = '' | |||
# ArbitraryMapName systemUser DBUser | |||
superuser_map root postgres | |||
superuser_map postgres postgres | |||
# Let other names login as themselves | |||
superuser_map /^(.*)$ \1 | |||
''; | |||
}; | |||
</syntaxhighlight> | |||
This map can have an arbitrary name and defines which system user can login as which DB user. Every other user and combination is rejected. | |||
For example, with the above mapping if we are logged-in as system user "root" but want enter the DB as DB user "postgres" we would be allowed: | |||
<syntaxhighlight lang="nix"> | |||
root$ psql -U postgres | |||
# ok | |||
</syntaxhighlight> | |||
==== Limit Access ==== | |||
Once logged-in we can '''limit what DB users can access'''. With the <code>authentication</code> we can limit what | |||
DB user can access which databases. A good default setting is as follows: | |||
<syntaxhighlight lang=" | <syntaxhighlight lang="nix"> | ||
services.postgresql = { | |||
enable = true; | |||
ensureDatabases = [ "mydatabase" ]; | |||
authentication = pkgs.lib.mkOverride 10 '' | |||
#type database DBuser auth-method optional_ident_map | |||
local sameuser all peer map=superuser_map | |||
''; | |||
}; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
The | 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: | |||
<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=" | <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> | </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. | |||
<syntaxhighlight lang=" | 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> | </syntaxhighlight> | ||
== Debugging with <code>psql</code> == | |||
To debug the SQL statements futher, one can use '''systemctl cat postgresql''' and see the '''ExecStartPost=/nix/store/rnv1v95bbf2lsy9ncwg7jdwj2s71sqra-unit-script/bin/postgresql-post-start''' line. Then open it with `cat` on the shell and see the '''psql''' command. | To debug the SQL statements futher, one can use '''systemctl cat postgresql''' and see the '''ExecStartPost=/nix/store/rnv1v95bbf2lsy9ncwg7jdwj2s71sqra-unit-script/bin/postgresql-post-start''' line. Then open it with `cat` on the shell and see the '''psql''' command. | ||
Line 123: | Line 242: | ||
</pre> | </pre> | ||
== Troubleshooting == | |||
=== Connection rejected with "Role does not exist" === | |||
<syntaxhighlight lang="nix"> | |||
$ psql | |||
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not exist | |||
</syntaxhighlight> | |||
You are trying to login as a system user ("root" in this example) that has no DB user of the same name. Try <code>psql -U postgres</code> or <code>sudo -u postgres psql</code> to log in as a different DB user. | |||
=== Connection rejected with "Peer authentication failed" === | |||
<syntaxhighlight lang="nix"> | |||
root$ psql -U postgres | |||
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgress" | |||
</syntaxhighlight> | |||
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 129: | Line 286: | ||
[[Category:Applications]] | [[Category:Applications]] | ||
[[Category:Database]] | |||
[[Category:NixOS Manual]] |