Gestion des rôles et permissions PostgreSQL

Introduction

Dans ce laboratoire, vous allez explorer la gestion des rôles et des permissions dans PostgreSQL. L'objectif principal est d'apprendre à créer des rôles avec des attributs spécifiques et à gérer les privilèges de la base de données.

Vous commencerez par créer un rôle nommé reporting_role avec l'attribut NOLOGIN, l'empêchant de se connecter directement à la base de données. Ensuite, vous vérifierez la création du rôle. Les étapes suivantes couvriront l'octroi et le test des privilèges de base de données, le changement d'utilisateur pour vérifier l'accès, et la révocation des privilèges d'un rôle.

Créer un rôle

Dans cette étape, vous apprendrez comment créer un rôle dans PostgreSQL. Les rôles sont utilisés pour gérer l'accès et les permissions de la base de données.

Se connecter au serveur PostgreSQL :

Ouvrez un terminal et connectez-vous au serveur PostgreSQL en utilisant l'outil en ligne de commande psql en tant qu'utilisateur postgres.
```
sudo -u postgres psql
```
Vous devriez voir une invite similaire à :
```
postgres=#
```
Créer le rôle reporting_role :

Maintenant, créons le rôle reporting_role en utilisant la commande CREATE ROLE. Nous spécifierons l'attribut NOLOGIN, ce qui signifie que ce rôle ne pourra pas être utilisé pour se connecter directement à la base de données. Il sera uniquement utilisé pour accorder des permissions à d'autres rôles ou utilisateurs.
```
CREATE ROLE reporting_role NOLOGIN;
```
Cette commande crée le rôle. Vous devriez voir la sortie suivante :
```
CREATE ROLE
```
Vérifier la création du rôle :

Pour vérifier que le rôle a été créé, vous pouvez utiliser la commande \du (raccourci pour \list users). Cette commande liste tous les rôles de la base de données.
```
\du
```
Vous devriez voir reporting_role dans la liste des rôles. La sortie ressemblera à ceci :
```
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+-----------
labex | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}
```
Quittez le shell psql.
```
\q
```

Accorder les privilèges de base de données

Dans cette étape, vous apprendrez comment accorder des privilèges de base de données à un rôle. Nous allons nous baser sur le rôle reporting_role créé à l'étape précédente.

Se connecter au serveur PostgreSQL :

Connectez-vous au serveur PostgreSQL en utilisant l'outil en ligne de commande psql en tant qu'utilisateur postgres.
```
sudo -u postgres psql
```
Créer une base de données :

Créons une base de données nommée reporting_db à laquelle notre rôle reporting_role aura accès.
```
CREATE DATABASE reporting_db;
```
Vous devriez voir la sortie suivante :
```
CREATE DATABASE
```
Se connecter à la base de données reporting_db :

Connectez-vous à la base de données nouvellement créée :
```
\c reporting_db
```
L'invite devrait changer en reporting_db=#.
Créer une table :

Créons une table simple nommée sales_data dans la base de données reporting_db.
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
Vous devriez voir la sortie suivante :
```
CREATE TABLE
```
Accorder le privilège SELECT sur la table sales_data au rôle reporting_role :

Maintenant, nous allons accorder le privilège SELECT sur la table sales_data au rôle reporting_role. Cela permettra à tout rôle ou utilisateur membre de reporting_role de lire les données de la table sales_data.
```
GRANT SELECT ON sales_data TO reporting_role;
```
Vous devriez voir la sortie suivante :
```
GRANT
```
Quittez le shell psql.
```
\q
```

Créer l'utilisateur et tester les privilèges

Dans cette étape, vous allez créer un utilisateur et lui accorder l'appartenance au rôle reporting_role pour tester les privilèges.

Se connecter au serveur PostgreSQL :

Connectez-vous au serveur PostgreSQL en utilisant l'outil en ligne de commande psql en tant qu'utilisateur postgres.
```
sudo -u postgres psql
```
Se connecter à la base de données reporting_db :

Connectez-vous à la base de données reporting_db :
```
\c reporting_db
```
Créer un utilisateur et lui accorder l'appartenance au rôle reporting_role :

Créons un nouvel utilisateur nommé report_user. Ensuite, nous accorderons à cet utilisateur l'appartenance au rôle reporting_role. Cela signifie que report_user héritera des privilèges accordés à reporting_role.
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
Vous devriez voir la sortie suivante :
```
CREATE ROLE
GRANT ROLE
```
Configurer PostgreSQL pour l'authentification sans mot de passe :

Par défaut, PostgreSQL utilise l'authentification peer, ce qui peut causer des problèmes de connexion dans ce laboratoire. Nous allons la changer en trust pour simplifier l'accès pour report_user.

Tout d'abord, quittez le shell psql :
```
\q
```
Maintenant, modifiez le fichier pg_hba.conf pour changer la méthode d'authentification. La commande sed ci-dessous sauvegardera le fichier original sous le nom pg_hba.conf.bak et remplacera peer par trust pour les connexions locales.
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
Rechargez PostgreSQL pour appliquer les modifications :
```
sudo service postgresql reload
```
Insérer des données dans la table sales_data :

Reconnectez-vous à la base de données en tant qu'utilisateur postgres.
```
sudo -u postgres psql -d reporting_db
```
Insérez quelques données d'exemple dans la table sales_data à des fins de test.
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
Vous devriez voir la sortie suivante :
```
INSERT 0 3
```
Quittez le shell psql.
```
\q
```
Se connecter à la base de données reporting_db en tant que report_user :

Ouvrez une nouvelle fenêtre de terminal et utilisez la commande suivante :
```
psql -d reporting_db -U report_user
```
Vous serez connecté à la base de données en tant que report_user sans invite de mot de passe.
Tester les privilèges :

Maintenant, essayez de sélectionner des données de la table sales_data :
```
SELECT * FROM sales_data;
```
Vous devriez voir les données que vous avez insérées :
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 rows)
```
Si vous voyez cette sortie, cela signifie que report_user possède le privilège SELECT sur la table sales_data, qui a été accordé via le rôle reporting_role.

Quittez le shell psql.
```
\q
```

Révoquer les privilèges

Dans cette étape, vous allez révoquer le privilège SELECT du rôle reporting_role et vérifier que report_user n'a plus accès à la table sales_data.

Se connecter au serveur PostgreSQL en tant qu'utilisateur postgres :

Retournez au terminal où vous êtes connecté en tant qu'utilisateur postgres. Connectez-vous au serveur PostgreSQL en utilisant l'outil en ligne de commande psql en tant qu'utilisateur postgres.
```
sudo -u postgres psql
```
Se connecter à la base de données reporting_db :

Connectez-vous à la base de données reporting_db :
```
\c reporting_db
```
Révoquer le privilège SELECT du rôle reporting_role :

Maintenant, nous allons révoquer le privilège SELECT sur la table sales_data du rôle reporting_role.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
Vous devriez voir la sortie suivante :
```
REVOKE
```
Quittez le shell psql.
```
\q
```
Se connecter à la base de données reporting_db en tant que report_user :

Retournez au terminal où vous êtes connecté en tant que report_user.
```
psql -d reporting_db -U report_user
```
Vérifier le refus d'accès en tant que report_user :

En tant que report_user, exécutez à nouveau la requête SELECT pour vérifier que vous n'avez plus accès aux données de la table sales_data :
```
SELECT * FROM sales_data;
```
Vous devriez voir un message d'erreur similaire à :
```
ERROR:  permission denied for table sales_data
```
Cela confirme que report_user n'a plus les privilèges SELECT sur la table sales_data car le privilège a été révoqué du rôle reporting_role.

Quittez le shell psql.
```
\q
```

Résumé

Dans ce laboratoire, vous avez appris à créer des rôles, à accorder des privilèges, à créer des utilisateurs, à attribuer des rôles à des utilisateurs et à révoquer des privilèges dans PostgreSQL. Ce sont des concepts fondamentaux pour la gestion de la sécurité de la base de données et du contrôle d'accès.

Gestion des rôles et des permissions PostgreSQL