Gestion des rôles et des permissions PostgreSQL

Introduction

Dans ce laboratoire, vous explorerez 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 la 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.

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{{"Gestion des rôles et des permissions dans PostgreSQL"}} postgresql/db_access -.-> lab-550960{{"Gestion des rôles et des permissions dans PostgreSQL"}} postgresql/user_setup -.-> lab-550960{{"Gestion des rôles et des permissions dans PostgreSQL"}} postgresql/perm_read -.-> lab-550960{{"Gestion des rôles et des permissions dans PostgreSQL"}} postgresql/perm_write -.-> lab-550960{{"Gestion des rôles et des permissions dans PostgreSQL"}} postgresql/perm_remove -.-> lab-550960{{"Gestion des rôles et des permissions dans PostgreSQL"}} postgresql/table_init -.-> lab-550960{{"Gestion des rôles et des permissions dans PostgreSQL"}} end

Créer un rôle

Dans cette étape, vous apprendrez à 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 reporting_role en utilisant la commande CREATE ROLE. Nous spécifierons l'attribut NOLOGIN, ce qui signifie que ce rôle ne peut pas être utilisé pour se connecter directement à la base de données. Il ne sera utilisé que 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 (abréviation de \list users). Cette commande liste tous les rôles dans 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, Create role, Create DB, Replication, Bypass RLS| {}
 postgres       | Superuser, Create role, Create DB                         | {}
 reporting_role | Cannot login                                               | {}

Quittez l'interpréteur psql (psql shell).

\q

Accorder des privilèges de base de données

Dans cette étape, vous apprendrez à accorder des privilèges de base de données à un rôle. Nous nous appuierons sur 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 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 reporting_role :

Maintenant, nous allons accorder le privilège SELECT sur la table sales_data au 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 l'interpréteur psql (psql shell).
```
\q
```

Créer un utilisateur et tester les privilèges

Dans cette étape, vous allez créer un utilisateur et lui accorder l'appartenance au 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 reporting_role :

Créons un nouvel utilisateur nommé report_user avec un mot de passe (remplacez your_password par un mot de passe fort). Ensuite, nous accorderons à cet utilisateur l'appartenance au reporting_role. Cela signifie que report_user héritera des privilèges accordés à reporting_role.
```
CREATE USER report_user WITH PASSWORD 'your_password';
GRANT reporting_role TO report_user;
```
Vous devriez voir la sortie suivante :
```
CREATE ROLE
GRANT
```
Insérer des données dans la table sales_data :

Insérez des exemples de données 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 l'interpréteur psql (psql shell).
```
\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 -W
```
Vous serez invité à entrer le mot de passe que vous avez défini pour report_user. Entrez-le.
Tester les privilèges :

Maintenant, essayez de sélectionner des données dans 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 a le privilège SELECT sur la table sales_data, qui a été accordé via le reporting_role.

Quittez l'interpréteur psql (psql shell).
```
\q
```

Révoquer les privilèges

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

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

Revenez 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 reporting_role :

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

Revenez au terminal où vous êtes connecté en tant que report_user.
```
psql -d reporting_db -U report_user -W
```
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 le report_user n'a plus les privilèges SELECT sur la table sales_data car le privilège a été révoqué du reporting_role.

Quittez l'interpréteur psql (psql shell).
```
\q
```

Résumé

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