PostgreSQL: Difference between revisions

PostgreSQL (view source)

Revision as of 11:31, 15 July 2023

1,390 bytes added , 15 July 2023

Add verification with psql section

VisualWikitext

Anonymous user

imported>Malteneuss

@@ Line 22: / Line 22: @@
 * [https://search.nixos.org/options?query=services.postgresql Available NixOS Postgresql service 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
+-------------+-------+----------+-----------+---------+---------+-------------+---------+-------
+| 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 ===
@@ Line 65: / Line 121: @@
 * [https://search.nixos.org/packages?query=postgresql_ Available Nixpkgs Postgresql versions]
-== Using <code>psql</code> ==
+== Debugging with <code>psql</code> ==
-Depending on the <code>system.stateVersion</code> of your system, the [https://github.com/NixOS/nixpkgs/blob/release-17.09/nixos/modules/services/databases/postgresql.nix#L151 default super-user username] will change:
-* 17.03 and earlier: the superuser is <code>root</code>
-* 17.09 and later: the superuser is <code>postgres</code>
-There is no password, [https://www.postgresql.org/docs/9.6/static/auth-methods.html#AUTH-IDENT Ident Authentication] [https://github.com/NixOS/nixpkgs/blob/release-17.09/nixos/modules/services/databases/postgresql.nix#L182 is used]. This means that you can access the database using a system user named like the database user.
-Example for a 17.03 stateVersion:
-<syntaxhighlight lang="commands">
- $ sudo -u root psql
-psql: FATAL:  database "root" does not exist
- $ sudo -u root psql -l
-                              List of databases
-   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
------------+-------+----------+-------------+-------------+-------------------
- postgres  | root  | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 |
- template0 | root  | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/root          +
-           |       |          |             |             | root=CTc/root
- template1 | root  | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/root          +
-           |       |          |             |             | root=CTc/root
-(4 rows)
-</syntaxhighlight>
-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.
-Examples for a 17.09 stateVersion and later:
-<syntaxhighlight lang="commands">
- $ psql -U postgres
-</syntaxhighlight>
-and
-<syntaxhighlight lang="commands">
- $ psql -U postgres -l
-</syntaxhighlight>
 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 122: / Line 138: @@
                                                                  ^
 </pre>
+== FAQ ==
+=== 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.
 == See also ==