Anonymous user
PostgreSQL: Difference between revisions
m
Add a little about enabling TLS.
imported>Onny No edit summary |
imported>Zie m (Add a little about enabling TLS.) |
||
| Line 175: | Line 175: | ||
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> == | ||