PostgreSQL: Difference between revisions

From NixOS Wiki
imported>Samueldr
m Squashed a typo
imported>Samueldr
m Adds citation/source for ident authentication use.
Line 8: Line 8:
* 17.09 and later: the superuser is <code>postgres</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] is used. This means that you can access the database using a system user named like the database user.
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.
Example for a 17.03 stateVersion.

Revision as of 21:25, 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.