PostgreSQL Rollenverwaltung & Berechtigungen konfigurieren

Einführung

In diesem Lab werden Sie die Rollen- und Berechtigungsverwaltung von PostgreSQL erkunden. Das Hauptziel ist es, zu lernen, wie man Rollen mit spezifischen Attributen erstellt und Datenbankprivilegien verwaltet.

Sie beginnen mit der Erstellung einer Rolle namens reporting_role mit dem Attribut NOLOGIN, wodurch verhindert wird, dass sie sich direkt mit der Datenbank verbindet. Anschließend werden Sie die Erstellung der Rolle überprüfen. Die folgenden Schritte umfassen das Erteilen und Testen von Datenbankprivilegien, das Wechseln von Benutzern zur Überprüfung des Zugriffs und das Entziehen von Privilegien von einer Rolle.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/user_setup("Create User Account") postgresql/PostgreSQLGroup -.-> postgresql/perm_read("Grant Table Read") postgresql/PostgreSQLGroup -.-> postgresql/perm_write("Grant Table Write") postgresql/PostgreSQLGroup -.-> postgresql/perm_remove("Revoke User Privilege") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") subgraph Lab Skills postgresql/db_setup -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} postgresql/db_access -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} postgresql/user_setup -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} postgresql/perm_read -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} postgresql/perm_write -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} postgresql/perm_remove -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} postgresql/table_init -.-> lab-550960{{"PostgreSQL Rollen- und Berechtigungsverwaltung"}} end

Eine Rolle erstellen

In diesem Schritt lernen Sie, wie Sie eine Rolle in PostgreSQL erstellen. Rollen werden verwendet, um den Datenbankzugriff und die Berechtigungen zu verwalten.

Verbinden Sie sich mit dem PostgreSQL-Server:

Öffnen Sie ein Terminal und verbinden Sie sich mit dem PostgreSQL-Server mit dem psql Kommandozeilen-Tool als Benutzer postgres.
```
sudo -u postgres psql
```
Sie sollten eine ähnliche Eingabeaufforderung sehen:
```
postgres=#
```
Erstellen Sie die Rolle reporting_role:

Erstellen wir nun die reporting_role mit dem Befehl CREATE ROLE. Wir werden das Attribut NOLOGIN angeben, was bedeutet, dass diese Rolle nicht verwendet werden kann, um sich direkt mit der Datenbank zu verbinden. Sie wird nur verwendet, um anderen Rollen oder Benutzern Berechtigungen zu erteilen.
```
CREATE ROLE reporting_role NOLOGIN;
```
Dieser Befehl erstellt die Rolle. Sie sollten die folgende Ausgabe sehen:
```
CREATE ROLE
```

Überprüfen Sie die Erstellung der Rolle:

Um zu überprüfen, ob die Rolle erstellt wurde, können Sie den Befehl \du (kurz für \list users) verwenden. Dieser Befehl listet alle Rollen in der Datenbank auf.

\du

Sie sollten reporting_role in der Liste der Rollen sehen. Die Ausgabe wird ungefähr so aussehen:

                                 List of roles
   Role name    |                         Attributes                         | Member of
----------------+------------------------------------------------------------+-----------
 labex          | Superuser, Create role, Create DB, Replication, Bypass RLS| {}
 postgres       | Superuser, Create role, Create DB                         | {}
 reporting_role | Cannot login                                               | {}

Verlassen Sie die psql-Shell.

\q

Datenbankprivilegien erteilen

In diesem Schritt lernen Sie, wie Sie einer Rolle Datenbankprivilegien erteilen. Wir bauen auf der im vorherigen Schritt erstellten reporting_role auf.

Verbinden Sie sich mit dem PostgreSQL-Server:

Verbinden Sie sich mit dem PostgreSQL-Server mit dem psql Kommandozeilen-Tool als Benutzer postgres.
```
sudo -u postgres psql
```
Erstellen Sie eine Datenbank:

Erstellen wir eine Datenbank namens reporting_db, auf die unsere reporting_role Zugriff haben soll.
```
CREATE DATABASE reporting_db;
```
Sie sollten die folgende Ausgabe sehen:
```
CREATE DATABASE
```
Verbinden Sie sich mit der Datenbank reporting_db:

Verbinden Sie sich mit der neu erstellten Datenbank:
```
\c reporting_db
```
Die Eingabeaufforderung sollte sich in reporting_db=# ändern.
Erstellen Sie eine Tabelle:

Erstellen wir eine einfache Tabelle namens sales_data innerhalb der Datenbank reporting_db.
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
Sie sollten die folgende Ausgabe sehen:
```
CREATE TABLE
```
Erteilen Sie das SELECT-Privileg für die Tabelle sales_data an die reporting_role:

Nun erteilen wir das SELECT-Privileg für die Tabelle sales_data an die reporting_role. Dies ermöglicht jeder Rolle oder jedem Benutzer, der Mitglied der reporting_role ist, Daten aus der Tabelle sales_data zu lesen.
```
GRANT SELECT ON sales_data TO reporting_role;
```
Sie sollten die folgende Ausgabe sehen:
```
GRANT
```
Verlassen Sie die psql-Shell.
```
\q
```

Benutzer erstellen und Berechtigungen testen

In diesem Schritt erstellen Sie einen Benutzer und gewähren ihm die Mitgliedschaft in der reporting_role, um die Berechtigungen zu testen.

Verbinden Sie sich mit dem PostgreSQL-Server:

Verbinden Sie sich mit dem PostgreSQL-Server mit dem psql Kommandozeilen-Tool als Benutzer postgres.
```
sudo -u postgres psql
```
Verbinden Sie sich mit der Datenbank reporting_db:

Verbinden Sie sich mit der Datenbank reporting_db:
```
\c reporting_db
```
Erstellen Sie einen Benutzer und gewähren Sie die Mitgliedschaft in der reporting_role:

Erstellen wir einen neuen Benutzer namens report_user mit einem Passwort (ersetzen Sie your_password durch ein sicheres Passwort). Dann gewähren wir diesem Benutzer die Mitgliedschaft in der reporting_role. Dies bedeutet, dass report_user die Berechtigungen erbt, die der reporting_role gewährt wurden.
```
CREATE USER report_user WITH PASSWORD 'your_password';
GRANT reporting_role TO report_user;
```
Sie sollten die folgende Ausgabe sehen:
```
CREATE ROLE
GRANT
```
Daten in die Tabelle sales_data einfügen:

Fügen Sie einige Beispieldaten in die Tabelle sales_data ein, um sie zu testen.
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
Sie sollten die folgende Ausgabe sehen:
```
INSERT 0 3
```
Verlassen Sie die psql-Shell.
```
\q
```
Verbinden Sie sich als report_user mit der Datenbank reporting_db:

Öffnen Sie ein neues Terminalfenster und verwenden Sie den folgenden Befehl:
```
psql -d reporting_db -U report_user -W
```
Sie werden nach dem Passwort gefragt, das Sie für report_user festgelegt haben. Geben Sie es ein.
Testen Sie die Berechtigungen:

Versuchen Sie nun, Daten aus der Tabelle sales_data auszuwählen:
```
SELECT * FROM sales_data;
```
Sie sollten die von Ihnen eingefügten Daten sehen:
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 rows)
```
Wenn Sie diese Ausgabe sehen, bedeutet dies, dass der report_user das SELECT-Privileg für die Tabelle sales_data hat, das über die reporting_role gewährt wurde.

Verlassen Sie die psql-Shell.
```
\q
```

Berechtigungen widerrufen

In diesem Schritt widerrufen Sie das SELECT-Privileg von der reporting_role und überprüfen, ob der report_user keinen Zugriff mehr auf die Tabelle sales_data hat.

Verbinden Sie sich als Benutzer postgres mit dem PostgreSQL-Server:

Wechseln Sie zurück zu dem Terminal, in dem Sie als Benutzer postgres verbunden sind. Verbinden Sie sich mit dem PostgreSQL-Server mit dem psql Kommandozeilen-Tool als Benutzer postgres.
```
sudo -u postgres psql
```
Verbinden Sie sich mit der Datenbank reporting_db:

Verbinden Sie sich mit der Datenbank reporting_db:
```
\c reporting_db
```
Widerrufen Sie das SELECT-Privileg von der reporting_role:

Nun widerrufen wir das SELECT-Privileg für die Tabelle sales_data von der reporting_role.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
Sie sollten die folgende Ausgabe sehen:
```
REVOKE
```
Verlassen Sie die psql-Shell.
```
\q
```
Verbinden Sie sich als report_user mit der Datenbank reporting_db:

Wechseln Sie zurück zu dem Terminal, in dem Sie als report_user verbunden sind.
```
psql -d reporting_db -U report_user -W
```
Überprüfen Sie die Zugriffsverweigerung als report_user:

Führen Sie als report_user die SELECT-Abfrage erneut aus, um zu überprüfen, ob Sie keinen Zugriff mehr auf die Daten in der Tabelle sales_data haben:
```
SELECT * FROM sales_data;
```
Sie sollten eine Fehlermeldung ähnlich der folgenden sehen:
```
ERROR:  permission denied for table sales_data
```
Dies bestätigt, dass der report_user keine SELECT-Berechtigungen mehr für die Tabelle sales_data hat, da das Privileg von der reporting_role widerrufen wurde.

Verlassen Sie die psql-Shell.
```
\q
```

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Rollen erstellt, Berechtigungen (Privileges) erteilt, Benutzer erstellt, Rollen Benutzern zuweist und Berechtigungen in PostgreSQL widerruft. Dies sind grundlegende Konzepte für die Verwaltung der Datenbanksicherheit und Zugriffskontrolle (Access Control).