Gérer les utilisateurs et les rôles PostgreSQL

Le service managé PostgreSQL fournit un utilisateur principal lors de la création du cluster. Pour ajouter des utilisateurs supplémentaires ou gérer les permissions, vous devez vous connecter directement à la base de données.

note

L'API Numspot permet uniquement de gérer l'utilisateur principal du cluster. La création d'utilisateurs et de rôles supplémentaires s'effectue via des commandes SQL.

Se connecter au cluster PostgreSQL

Avant de créer des utilisateurs ou des rôles, connectez-vous à votre cluster avec l'utilisateur principal :

psql "host=<hôte> port=5432 dbname=<base_de_données> user=<utilisateur_principal> sslmode=require"

Pour récupérer les identifiants de connexion, consultez Récupérer le mot de passe de l'utilisateur principal.

Créer un utilisateur

Pour créer un utilisateur avec un mot de passe :

CREATE USER <nom_utilisateur> WITH PASSWORD '<mot_de_passe>';

Options de création

Option	Description
CREATEDB	Permet à l'utilisateur de créer des bases de données
NOCREATEDB	Empêche la création de bases de données (par défaut)
CREATEROLE	Permet de créer d'autres rôles
NOCREATEROLE	Empêche la création de rôles (par défaut)
LOGIN	Autorise la connexion (pour les utilisateurs)
NOLOGIN	Empêche la connexion (pour les rôles système)

Exemple

Créer un utilisateur applicatif avec des droits limités :

CREATE USER app_user WITH PASSWORD '<mot_de_passe_sécurisé>' NOCREATEDB NOCREATEROLE;

Créer un rôle

Les rôles permettent de grouper des permissions et de les attribuer à plusieurs utilisateurs.

CREATE ROLE <nom_rôle>;

Exemple de structuration par rôles

-- Rôle lecture seule
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE <base_de_données> TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Rôle lecture-écriture
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE <base_de_données> TO readwrite;
GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;

-- Rôle administrateur
CREATE ROLE db_admin;
GRANT CONNECT ON DATABASE <base_de_données> TO db_admin;
GRANT ALL PRIVILEGES ON DATABASE <base_de_données> TO db_admin;
GRANT ALL PRIVILEGES ON SCHEMA public TO db_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin;

Attribuer un rôle à un utilisateur

Associez un rôle à un utilisateur existant :

GRANT <nom_rôle> TO <nom_utilisateur>;

Exemple

GRANT readonly TO app_user_read;
GRANT readwrite TO app_user_write;

Modifier un utilisateur

Changer le mot de passe

ALTER USER <nom_utilisateur> WITH PASSWORD '<nouveau_mot_de_passe>';

Définir une date d'expiration

ALTER USER <nom_utilisateur> WITH VALID UNTIL '<date_expiration>';

Exemple avec une date d'expiration :

ALTER USER temp_user WITH VALID UNTIL '2026-12-31 23:59:59';

Gérer les permissions

Accorder des permissions sur une table

GRANT SELECT ON <table> TO <nom_utilisateur>;
GRANT SELECT, INSERT, UPDATE ON <table> TO <nom_utilisateur>;
GRANT ALL PRIVILEGES ON <table> TO <nom_utilisateur>;

Accorder des permissions sur un schéma

GRANT USAGE ON SCHEMA <schéma> TO <nom_utilisateur>;
GRANT CREATE ON SCHEMA <schéma> TO <nom_utilisateur>;

Révoquer des permissions

REVOKE SELECT ON <table> FROM <nom_utilisateur>;
REVOKE ALL PRIVILEGES ON SCHEMA <schéma> FROM <nom_utilisateur>;

Supprimer un utilisateur ou un rôle

Supprimer un utilisateur

DROP USER <nom_utilisateur>;

Supprimer un rôle

DROP ROLE <nom_rôle>;

avertissement

La suppression d'un utilisateur ou d'un rôle est irréversible. Vérifiez qu'aucun objet ne dépend de cet utilisateur avant la suppression.

Bonnes pratiques

Principe du moindre privilège

Accordez uniquement les permissions nécessaires au fonctionnement de l'application :

• Utilisez des rôles pour regrouper les permissions ;
• Attribuez les rôles aux utilisateurs selon leurs besoins ;
• Préférez SELECT à ALL PRIVILEGES pour les utilisateurs en lecture seule.

Rotation des mots de passe

Changez régulièrement les mots de passe des utilisateurs applicatifs :

ALTER USER <nom_utilisateur> WITH PASSWORD '<nouveau_mot_de_passe>';

Audit des accès

Utilisez les vues système pour vérifier les permissions existantes :

-- Lister les rôles
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole FROM pg_roles;

-- Lister les permissions sur les tables
SELECT grantee, table_schema, table_name, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_schema = 'public';

Limitations

Action	Support
Créer un utilisateur principal	Via l'API ou la console à la création du cluster uniquement
Réinitialiser le mot de passe principal	Via l'API (voir Réinitialiser le mot de passe)
Créer des utilisateurs supplémentaires	Via SQL uniquement
Gérer les rôles	Via SQL uniquement
Configurer les permissions	Via SQL uniquement

astuce

Automatisez la gestion des utilisateurs avec des scripts SQL versionnés dans votre système de contrôle de source. Cela facilite la traçabilité et la reproduction des configurations.