Anonymous user
PostgreSQL: Difference between revisions
added some documentation how to debug the SQL statements
imported>Samueldr m (Adds citation/source for ident authentication use.) |
imported>Qknight (added some documentation how to debug the SQL statements) |
||
| Line 28: | Line 28: | ||
The first error is not an error with the credentials, but an error coming from the default behaviour of <code>psql</code> that is trying to use a database name named like the user logging-in. The second command lists tables available. | The first error is not an error with the credentials, but an error coming from the default behaviour of <code>psql</code> that is trying to use a database name named like the user logging-in. The second command lists tables available. | ||
=== Deploying databases === | |||
This example shows how to roll out a database with a default user and password: | |||
<pre> | |||
services.postgresql = { | |||
enable = true; | |||
package = pkgs.postgresql100; | |||
enableTCPIP = true; | |||
authentication = pkgs.lib.mkOverride 10 '' | |||
local all all trust | |||
host all all ::1/128 trust | |||
''; | |||
initialScript = pkgs.writeText "backend-initScript" '' | |||
CREATE ROLE nixcloud WITH LOGIN PASSWORD 'nixcloud' CREATEDB; | |||
CREATE DATABASE nixcloud; | |||
GRANT ALL PRIVILEGES ON DATABASE nixcloud TO nixcloud; | |||
''; | |||
</pre> | |||
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. | |||
Then execute the complete statement on the shell, as: | |||
<pre> | |||
/nix/store/3mqha1naji34i6iv78i90hc20dx0hld9-sudo-1.8.20p2/bin/sudo -u postgres psql -f "/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript" --port=5432 -d postgres | |||
psql:/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript:1: ERROR: syntax error at or near "-" | |||
LINE 1: CREATE ROLE nixcloud-admin WITH LOGIN PASSWORD 'nixcloud' CR... | |||
^ | |||
psql:/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript:2: ERROR: database "nixcloud-db1" already exists | |||
psql:/nix/store/az5nglyw7j94blxwkn2rmpi2p6z9fbmy-backend-initScript:3: ERROR: syntax error at or near "-" | |||
LINE 1: ...ALL PRIVILEGES ON DATABASE "nixcloud-db1" TO nixcloud-admin; | |||
^ | |||
</pre> | |||