Passer au contenu principal

Gérer les utilisateurs et les rôles PostgreSQL

Le service managé PostgreSQL fournit un utilisateur principal lors de la création du cluster PostgreSQL. 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​

OptionDescription
CREATEDBPermet à l'utilisateur de créer des bases de données
NOCREATEDBEmpêche la création de bases de données (par défaut)
CREATEROLEPermet de créer d'autres rôles
NOCREATEROLEEmpêche la création de rôles (par défaut)
LOGINAutorise la connexion (pour les utilisateurs)
NOLOGINEmpê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.

Configurer l'authentification SSO​

L'authentification unique (SSO, Single Sign-On) peut être activée ou modifiée sur un cluster PostgreSQL déjà instancié. Elle permet de centraliser la gestion des utilisateurs et des rôles au sein de votre infrastructure d'entreprise.

Le support des protocoles LDAP (Lightweight Directory Access Protocol) et OAuth 2.0 / OpenID Connect est disponible.

Configurer l'authentification via LDAP​

Pour synchroniser les utilisateurs et les rôles PostgreSQL avec votre annuaire LDAP, l'outil ldap2pg doit être installé sur le cluster.

avertissement

Vous n'avez pas accès au terminal du cluster pour installer des composants. Pour activer l'authentification LDAP et installer ldap2pg, vous devez adresser une demande à l'équipe support.

L'équipe support réalise l'installation et la configuration de ldap2pg sur le cluster suite à votre demande. Vous devez fournir les paramètres de connexion à votre annuaire LDAP (URI, base DN, filtres de recherche, etc.) ainsi que la correspondance souhaitée entre les groupes LDAP et les rôles PostgreSQL.

Une fois la configuration appliquée, les utilisateurs présents dans l'annuaire LDAP peuvent se connecter au cluster et se voir attribuer automatiquement les rôles correspondants.

Configurer l'authentification via OAuth 2.0 / OpenID Connect​

L'authentification par OAuth 2.0 et OpenID Connect (OIDC) permet aux utilisateurs de se connecter au cluster PostgreSQL en utilisant leur fournisseur d'identité (IdP) externe (par exemple : Keycloak, Okta, Microsoft Entra ID, etc.).

avertissement

Vous n'avez pas accès au terminal du cluster pour installer des composants. Pour activer l'authentification OAuth/OIDC, vous devez adresser une demande à l'équipe support.

L'équipe support configure le fournisseur d'identité OAuth/OIDC sur le cluster. Vous devez fournir les informations suivantes :

  • Issuer URL : l'URL de l'émetteur de votre IdP (par exemple : https://keycloak.example.com/realms/monrealm) ;
  • Client ID : l'identifiant client OAuth ;
  • Client Secret : le secret client (transmis de manière sécurisée) ;
  • Scopes : les scopes demandés, par défaut openid, profile et email ;
  • Attribut utilisateur : le claim utilisé pour identifier l'utilisateur (par exemple : sub, email, preferred_username) ;
  • Mappage des rôles : la correspondance entre les rôles ou groupes retournés par l'IdP et les rôles PostgreSQL.

Une fois le fournisseur configuré, les utilisateurs authentifiés via l'IdP peuvent accéder au cluster avec les permissions associées à leur identité.

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​

ActionSupport
Créer un utilisateur principalVia l'API ou la console à la création du cluster uniquement
Réinitialiser le mot de passe principalVia l'API (voir Réinitialiser le mot de passe)
Créer des utilisateurs supplémentairesVia SQL uniquement
Gérer les rôlesVia SQL uniquement
Configurer les permissionsVia 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.