Anonymous user
PostgreSQL: Difference between revisions
Add security section
imported>Malteneuss (Add verification with psql section) |
imported>Malteneuss (Add security section) |
||
| Line 120: | Line 120: | ||
* [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> | |||
==== Harden authentication ==== | |||
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="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> | |||
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. | |||
== Debugging with <code>psql</code> == | == Debugging with <code>psql</code> == | ||
| Line 149: | Line 202: | ||
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. | 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. | |||
== See also == | == See also == | ||