PostgreSQL Rollen- und Berechtigungsverwaltung | Datenbank-Sicherheit meistern

Einführung

In diesem Lab werden Sie die Verwaltung von PostgreSQL-Rollen und -Berechtigungen 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, das verhindert, dass sie sich direkt mit der Datenbank verbinden kann. Anschließend überprüfen Sie die Erstellung der Rolle. Nachfolgende Schritte umfassen das Gewähren und Testen von Datenbankprivilegien, das Wechseln von Benutzern zur Überprüfung des Zugriffs und das Entziehen von Privilegien von einer Rolle.

Rolle erstellen

In diesem Schritt lernen Sie, wie Sie eine Rolle in PostgreSQL erstellen. Rollen werden zur Verwaltung von Datenbankzugriffen und Berechtigungen verwendet.

Verbindung zum PostgreSQL-Server herstellen:

Öffnen Sie ein Terminal und verbinden Sie sich mit dem PostgreSQL-Server über das psql-Kommandozeilen-Tool als postgres-Benutzer.
```
sudo -u postgres psql
```
Sie sollten eine Eingabeaufforderung sehen, die ähnlich wie folgt aussieht:
```
postgres=#
```
Die Rolle reporting_role erstellen:

Nun erstellen wir die reporting_role mit dem Befehl CREATE ROLE. Wir geben das Attribut NOLOGIN an, was bedeutet, dass diese Rolle nicht zur direkten Verbindung mit der Datenbank verwendet werden kann. Sie wird nur zum Gewähren von Berechtigungen an andere Rollen oder Benutzer verwendet.
```
CREATE ROLE reporting_role NOLOGIN;
```
Dieser Befehl erstellt die Rolle. Sie sollten die folgende Ausgabe sehen:
```
CREATE ROLE
```
Die Erstellung der Rolle überprüfen:

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 | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}
```
Verlassen Sie die psql-Shell.
```
\q
```

Datenbankberechtigungen gewähren

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

Verbindung zum PostgreSQL-Server herstellen:

Verbinden Sie sich mit dem PostgreSQL-Server über das psql-Kommandozeilen-Tool als postgres-Benutzer.
```
sudo -u postgres psql
```
Eine Datenbank erstellen:

Erstellen wir eine Datenbank namens reporting_db, auf die unsere reporting_role Zugriff haben wird.
```
CREATE DATABASE reporting_db;
```
Sie sollten die folgende Ausgabe sehen:
```
CREATE DATABASE
```
Verbindung zur Datenbank reporting_db herstellen:

Verbinden Sie sich mit der neu erstellten Datenbank:
```
\c reporting_db
```
Die Eingabeaufforderung sollte sich zu reporting_db=# ändern.
Eine Tabelle erstellen:

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
```
Der Rolle reporting_role die SELECT-Berechtigung für die Tabelle sales_data gewähren:

Nun gewähren wir der reporting_role die SELECT-Berechtigung für die Tabelle sales_data. 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 Privilegien zu testen.

Verbindung zum PostgreSQL-Server herstellen:

Verbinden Sie sich mit dem PostgreSQL-Server über das psql-Kommandozeilen-Tool als postgres-Benutzer.
```
sudo -u postgres psql
```
Verbindung zur Datenbank reporting_db herstellen:

Verbinden Sie sich mit der Datenbank reporting_db:
```
\c reporting_db
```
Einen Benutzer erstellen und die Mitgliedschaft zur reporting_role gewähren:

Erstellen wir einen neuen Benutzer namens report_user. Anschließend gewähren wir diesem Benutzer die Mitgliedschaft in der reporting_role. Das bedeutet, dass report_user die Privilegien erbt, die der reporting_role gewährt wurden.
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
Sie sollten die folgende Ausgabe sehen:
```
CREATE ROLE
GRANT ROLE
```
PostgreSQL für passwortlose Authentifizierung konfigurieren:

Standardmäßig verwendet PostgreSQL die peer-Authentifizierung, was in diesem Lab zu Verbindungsproblemen führen kann. Wir ändern sie zu trust, um den Zugriff für den report_user zu vereinfachen.

Beenden Sie zuerst die psql-Shell:
```
\q
```
Ändern Sie nun die Datei pg_hba.conf, um die Authentifizierungsmethode zu ändern. Der untenstehende sed-Befehl sichert die Originaldatei als pg_hba.conf.bak und ersetzt peer durch trust für lokale Verbindungen.
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
Laden Sie PostgreSQL neu, um die Änderungen anzuwenden:
```
sudo service postgresql reload
```
Daten in die Tabelle sales_data einfügen:

Verbinden Sie sich erneut als postgres-Benutzer mit der Datenbank.
```
sudo -u postgres psql -d reporting_db
```
Fügen Sie einige Beispieldaten in die Tabelle sales_data zu Testzwecken ein.
```
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
```
Verbindung zur Datenbank reporting_db als report_user herstellen:

Öffnen Sie ein neues Terminalfenster und verwenden Sie den folgenden Befehl:
```
psql -d reporting_db -U report_user
```
Sie werden ohne Passwortabfrage als report_user mit der Datenbank verbunden.
Privilegien testen:

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 die SELECT-Berechtigung für die Tabelle sales_data besitzt, die über die reporting_role gewährt wurde.

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

Berechtigungen entziehen

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

Verbindung zum PostgreSQL-Server als postgres-Benutzer herstellen:

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

Verbinden Sie sich mit der Datenbank reporting_db:
```
\c reporting_db
```
Der reporting_role die SELECT-Berechtigung entziehen:

Nun entziehen wir der reporting_role die SELECT-Berechtigung für die Tabelle sales_data.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
Sie sollten die folgende Ausgabe sehen:
```
REVOKE
```
Verlassen Sie die psql-Shell.
```
\q
```
Verbindung zur Datenbank reporting_db als report_user herstellen:

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

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 besitzt, da die Berechtigung von der reporting_role entzogen wurde.

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

Zusammenfassung

In diesem Lab haben Sie gelernt, wie man Rollen erstellt, Berechtigungen gewährt, Benutzer erstellt, Rollen Benutzern zuweist und Berechtigungen in PostgreSQL entzieht. Dies sind grundlegende Konzepte für die Verwaltung von Datenbanksicherheit und Zugriffskontrolle.