PostgreSQL: Difference between revisions

From NixOS Wiki
imported>Samueldr
Stubbity stub page with psql informations, I answered this twice already on IRC ☺
 
imported>Samueldr
m Squashed a typo
Line 3: Line 3:
== Using <code>psql</code> ==
== Using <code>psql</code> ==


Dependeing 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:
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.03 and earlier: the superuser is <code>root</code>

Revision as of 21:24, 29 November 2017

Using psql

Depending on the system.stateVersion of your system, the default super-user username will change:

  • 17.03 and earlier: the superuser is root
  • 17.09 and later: the superuser is postgres

There is no password, Ident Authentication 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.

 $ 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)

The first error is not an error with the credentials, but an error coming from the default behaviour of psql that is trying to use a database name named like the user logging-in. The second command lists tables available.