Postgresql

Login

root@2864b08356b9:/# psql -U <USERNAME>

Databases, tables and users

Show existant databases

postgres=# \l
                                                      Liste der Datenbanken
       Name       | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | ICU-Locale | Locale-Provider |   Zugriffsprivilegien
------------------+------------+-----------+--------------+------------+------------+-----------------+--------------------------
 database_1       | username   | UTF8      | de_DE.utf8   | de_DE.utf8 |            | libc            |
 database_2       | username   | UTF8      | de_DE.utf8   | de_DE.utf8 |            | libc            | =Tc/username            +
                  |            |           |              |            |            |                 | username=CTc/username   +
                  |            |           |              |            |            |                 | username_1=CTc/username
 database_3       | username   | UTF8      | de_DE.utf8   | de_DE.utf8 |            | libc            | =Tc/username            +
                  |            |           |              |            |            |                 | username=CTc/username   +
                  |            |           |              |            |            |                 | username_2=CTc/username
(3 Zeilen)

Change database

postgres=# \c <DATABASENAME>
Sie sind jetzt verbunden mit der Datenbank »<DATABASENAME>« als Benutzer »<USERNAME>«.
<DATABASENAME>=#

Show relations (tables) of current database

<DATABASENAME>=# \dt
            Liste der Relationen
 Schema |    Name    |   Typ   | Eigentümer
--------+------------+---------+------------
 public | table_1    | Tabelle | username
 public | table_2    | Tabelle | username
 public | table_3    | Tabelle | username
(3 Zeilen)

Show users/roles

postgres=# \du;
                                         Liste der Rollen
    Rollenname    |                            Attribute                            | Mitglied von
------------------+-----------------------------------------------------------------+--------------
 username_1       | Superuser, DB erzeugen                                          | {}
 username_2       |                                                                 | {}
 username_3       | Superuser, Rolle erzeugen, DB erzeugen, Replikation, Bypass RLS | {}
 username_4       | Superuser, DB erzeugen                                          | {}

Create new database

Create database (default)

postgres=# CREATE DATABASE <DATABASENAME>;
CREATE DATABASE

Create new user

First: Change to the database

postgres=# \c <DATABASENAME>;

Create a new user

<DATABASENAME>=# CREATE ROLE <USERNAME> LOGIN PASSWORD 'SECURE_PASSWORD';
CREATE ROLE

Grant privileges

Grant all privileges of a database to a user

postgres=# GRANT ALL PRIVILEGES ON DATABASE <DATABASE> TO <USERNAME>;
GRANT

Grant all privileges to all tables of a schema (of connected database)

GRANT ALL ON ALL TABLES IN SCHEMA "public" TO "<USERNAME>";
GRANT

Change user to a superuser

postgres=# ALTER USER <USERNAME> WITH SUPERUSER;