Cette page explique comment créer, gérer et supprimer des rôles, des utilisateurs et des groupes PostgreSQL et AlloyDB pour PostgreSQL à l'aide des méthodes d'authentification intégrées PostgreSQL basées sur un mot de passe.
Créer des utilisateurs PostgreSQL dans AlloyDB
Lorsque vous créez des utilisateurs PostgreSQL, ils ne disposent que du privilège LOGIN.
Pour en savoir plus sur ces droits, consultez CREATE USER.
Vous pouvez modifier les droits de n'importe quel utilisateur à l'aide de la commande ALTER ROLE. Si vous créez un nouvel utilisateur à l'aide du client psql, vous pouvez l'associer à un autre rôle ou lui accorder des privilèges différents.
Gérer les utilisateurs
Vous pouvez créer et gérer des utilisateurs AlloyDB avec la Google Cloud CLI ou à l'aide de commandes PostgreSQL. Les sections suivantes présentent des tâches courantes de gestion des utilisateurs à l'aide de l'une ou l'autre méthode.
Avant de commencer
Pour utiliser les commandes PostgreSQL afin de gérer les utilisateurs sur un cluster, vous avez besoin des éléments suivants :
- Accès au client
psql - Accès à l'utilisateur de base de données
postgresou à un autre rôle d'utilisateur disposant des droits d'administrateur appropriés
Pour utiliser la console Google Cloud ou Google Cloud CLI afin de gérer les utilisateurs sur un cluster, ce cluster doit disposer d'une instance principale. Si votre cluster ne dispose pas d'instance principale, vous devez en créer une avant de pouvoir gérer les utilisateurs.
Créer un utilisateur de base de données
Pour créer un utilisateur de base de données qui s'authentifie directement auprès de la base de données à l'aide d'un nom d'utilisateur et d'un mot de passe (processus également appelé authentification intégrée), suivez les étapes décrites dans cette section.
Pour créer un utilisateur de base de données qui utilise Identity and Access Management (IAM) pour s'authentifier, consultez Gérer l'authentification IAM.
Console
Accédez à la page Clusters.
Cliquez sur le nom du cluster auquel vous souhaitez ajouter un utilisateur.
Cliquez sur Utilisateurs.
Cliquez sur Ajouter un compte utilisateur.
Laissez l'option Authentification intégrée sélectionnée.
Si vous souhaitez utiliser IAM pour vous authentifier et vous connecter à vos clusters de bases de données, vous devez effectuer des étapes supplémentaires pour préparer votre projet et accorder les autorisations appropriées à votre nouvel utilisateur. Pour en savoir plus, consultez Gérer l'authentification IAM.
Saisissez un nom d'utilisateur et un mot de passe pour le nouvel utilisateur.
Pour attribuer à l'utilisateur des rôles autres que le rôle
alloydbsuperuserpar défaut, procédez comme suit :- Saisissez le nom du rôle dans Rôles.
- Pour ajouter le rôle, appuyez sur Entrée ou sur Tabulation.
Sélectionnez x pour chaque rôle que vous souhaitez supprimer pour cet utilisateur.
Pour en savoir plus, consultez Rôles prédéfinis.
Cliquez sur Ajouter.
psql
CREATE USER USERNAME WITH PASSWORD PASSWORD;
Pour savoir comment créer et définir des utilisateurs de base de données, consultez CREATE USER.
Après avoir créé un utilisateur, vous pouvez modifier ses droits à l'aide de la commande ALTER ROLE dans le client psql.
gcloud
Exécutez la commande gcloud alloydb users create suivante :
gcloud alloydb users create USERNAME \
--password=PASSWORD \
--cluster=CLUSTER_ID \
--region=REGION_IDRemplacez les variables suivantes :
USERNAME: nom d'utilisateur.USERNAMEdoit respecter les règles concernant les identifiants SQL : sans caractères spéciaux ou entre guillemets doubles. Par exemple,db_user_1.PASSWORD: mot de passe de l'utilisateur. Pour créer l'utilisateur sans mot de passe, omettez cet argument.CLUSTER_ID: ID du cluster auquel ajouter l'utilisateur.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1
Après avoir créé un utilisateur, vous pouvez modifier ses droits à l'aide de la commande gcloud alloydb users set-roles.
Vous pouvez également attribuer des rôles ou des droits de super-utilisateur à un utilisateur lors de sa création. Pour ce faire, incluez les arguments --db-roles ou --superuser, comme décrit dans la section suivante.
Attribuer des rôles à un utilisateur de base de données
Console
Pour attribuer des rôles à un utilisateur de base de données, utilisez psql ou la gcloud CLI, au lieu de la console Google Cloud .
psql
GRANT ROLE to USERNAME;
Remplacez les variables suivantes :
USERNAME: nom d'utilisateur.ROLE: rôle à accorder à l'utilisateur. Par exemple, pour accorder à l'utilisateur des droits de super-utilisateur, attribuez-lui le rôlealloydbsuperuser.
gcloud
Utilisez la commande gcloud alloydb users set-roles suivante pour spécifier tous les rôles que vous souhaitez attribuer à l'utilisateur :
gcloud alloydb users set-roles USERNAME \
--db-roles=ROLES \
--cluster=CLUSTER_ID \
--region=REGION_IDRemplacez les variables suivantes :
USERNAME: nom d'utilisateur.ROLES: liste de tous les rôles que vous souhaitez attribuer à l'utilisateur, séparés par une virgule. Assurez-vous de lister tous les rôles, y compris les rôles existants et les nouveaux.Pour afficher la liste des rôles existants attribués à l'utilisateur, exécutez la commande suivante :
gcloud alloydb users list --cluster=CLUSTER_ID --region=REGION_IDRemplacez les variables suivantes :
CLUSTER_ID: ID du cluster contenant l'utilisateur.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1
Pour accorder à un utilisateur des droits de super-utilisateur, vous pouvez soit lui attribuer manuellement le rôle
alloydbsuperuserà l'aide depsql, soit exécuter la commandegcloud alloydb users set-superuseravec l'argument--superuser=true:gcloud alloydb users set-superuser USERNAME \ --superuser=true \ --cluster=CLUSTER_ID \ --region=REGION_IDRemplacez les variables suivantes :
CLUSTER_ID: ID du cluster contenant l'utilisateur.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1
Modifier le mot de passe d'un utilisateur de base de données
Pour définir un nouveau mot de passe pour un utilisateur de base de données PostgreSQL standard, suivez les étapes décrites dans cette section.
Vous ne pouvez pas modifier ni définir le mot de passe d'un utilisateur basé sur IAM, car les utilisateurs IAM n'utilisent pas de mot de passe pour s'authentifier. Pour en savoir plus, consultez Se connecter à l'aide d'un compte IAM.
Console
Accédez à la page Clusters.
Cliquez sur le nom du cluster contenant l'utilisateur de la base de données.
Cliquez sur Utilisateurs.
Sur la ligne correspondant à l'utilisateur, cliquez sur Ouvrir les actions pour cet utilisateur.
Sélectionnez Modifier le mot de passe.
Spécifiez un mot de passe :
Pour définir un nouveau mot de passe pour cet utilisateur, saisissez-le dans le champ Mot de passe.
Pour ne définir aucun mot de passe pour cet utilisateur, cochez la case Aucun mot de passe.
Cliquez sur OK.
psql
ALTER USER USERNAME WITH PASSWORD PASSWORD;
Pour savoir comment modifier le mot de passe d'un utilisateur, consultez ALTER ROLE.
gcloud
Exécutez la commande gcloud alloydb users set-password suivante :
gcloud alloydb users set-password USERNAME \
--password=PASSWORD \
--cluster=CLUSTER_ID \
--region=REGION_IDRemplacez les variables suivantes :
USERNAME: nom d'utilisateur.PASSWORD: nouveau mot de passe à attribuer à l'utilisateur.CLUSTER_ID: ID du cluster auquel appartient l'utilisateur.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1
Révoquer un rôle attribué à un utilisateur de base de données
Console
Pour révoquer des rôles d'un utilisateur, utilisez psql ou la gcloud CLI, au lieu de la console Google Cloud .
psql
Vous pouvez révoquer des droits précédemment accordés à un ou plusieurs rôles, ou révoquer l'appartenance d'un utilisateur à un rôle, comme indiqué ci-dessous :
REVOKE ROLE FROM USERNAME;
Pour supprimer les droits de super-utilisateur d'un utilisateur, révoquez le rôle alloydbsuperuser de cet utilisateur.
gcloud
Pour révoquer un rôle avec Google Cloud CLI, utilisez la commande gcloud alloydb users set-roles suivante pour spécifier tous les rôles que vous souhaitez attribuer à l'utilisateur et omettre les rôles à révoquer :
gcloud alloydb users set-roles USERNAME \
--db-roles=ROLES \
--cluster=CLUSTER_ID \
--region=REGION_IDRemplacez les variables suivantes :
USERNAME: nom d'utilisateur.ROLES: liste de tous les rôles que vous souhaitez attribuer à l'utilisateur, séparés par une virgule. Veillez à lister tous les rôles, y compris ceux existants et ceux que vous souhaitez ajouter.Pour afficher la liste des rôles existants attribués à l'utilisateur, exécutez la commande suivante :
gcloud alloydb users list --cluster=CLUSTER_ID --region=REGION_IDCLUSTER_ID: ID du cluster contenant l'utilisateur.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1
Pour révoquer les droits de super-utilisateur, vous pouvez supprimer manuellement le rôle alloydbsuperuser d'un utilisateur à l'aide de psql, ou exécuter la commande gcloud alloydb users set-superuser avec l'argument --superuser=false.
Afficher la liste des utilisateurs de base de données
Console
Accédez à la page Clusters.
Cliquez sur le nom du cluster dont vous souhaitez afficher les utilisateurs.
Cliquez sur Utilisateurs.
psql
Utilisez la commande \du suivante pour imprimer un tableau de tous les utilisateurs de base de données, y compris leurs appartenances à des groupes :
\du
gcloud
Exécutez la commande gcloud alloydb users list suivante :
gcloud alloydb users list \
--cluster=CLUSTER_ID \
--region=REGION_IDRemplacez les éléments suivants :
CLUSTER_ID: ID du cluster contenant les utilisateurs.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1
Supprimer les dépendances utilisateur
Avant de pouvoir supprimer un utilisateur de base de données, vous devez supprimer tous les objets de base de données qu'il possède et révoquer tous les droits qui lui ont été accordés. Sinon, la tentative de suppression échoue et renvoie une erreur (par exemple, ERROR: role "username" cannot
be dropped because some objects depend on it).
Pour identifier et supprimer ces dépendances, connectez-vous à votre base de données à l'aide du client psql en tant qu'utilisateur postgres ou avec n'importe quel compte disposant du rôle alloydbsuperuser, puis procédez comme suit pour chaque base de données de votre cluster :
Exécutez la requête suivante pour lister les objets (tables, vues, schémas et bases de données, par exemple) appartenant à l'utilisateur que vous souhaitez supprimer :
SELECT n.nspname AS schema_name, c.relname AS object_name, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as object_type FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_get_userbyid(c.relowner) = 'USERNAME' AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast';Remplacez
USERNAMEpar le nom de l'utilisateur que vous souhaitez supprimer.Pour les objets identifiés, vous devez modifier le propriétaire de chacun d'eux.
Pour modifier le propriétaire d'une table ou d'une vue :
ALTER TABLE SCHEMA_NAME.OBJECT_NAME OWNER TO NEW_OWNER;Remplacez les éléments suivants :
NEW_OWNER: nom d'utilisateur de la personne à laquelle vous souhaitez transférer la propriété.
Pour modifier le propriétaire d'une base de données :
ALTER DATABASE DATABASE_NAME OWNER TO NEW_OWNER;
Identifier et révoquer les droits d'accès. Il est également possible que l'utilisateur dispose de droits accordés sur d'autres objets ou d'appartenances à des rôles qui empêchent la suppression.
Pour lister les appartenances aux rôles de l'utilisateur :
WITH RECURSIVE cte AS ( SELECT oid ,oid as member_of,1 as d,''::name as path FROM pg_roles r where r.rolname !~ '^pg_' UNION SELECT m.roleid,m.member as member_of ,d+1,path||'->'||pg_get_userbyid(cte.oid) as path FROM cte JOIN pg_auth_members m ON m.member = cte.oid where d<15 ) SELECT DISTINCT pg_get_userbyid(member_of)::varchar as USERNAME, pg_get_userbyid(oid)::varchar as parent_role, d::int as depth, substr(path::text||'->'||pg_get_userbyid(oid),3) as path FROM cte WHERE d > 1 ORDER BY depth;Pour révoquer l'appartenance à un rôle :
REVOKE ROLE_NAME FROM USERNAME;Remplacez ROLE_NAME par l'appartenance au rôle que vous souhaitez révoquer.
Pour révoquer des droits sur une table :
Attribuez le rôle de propriétaire de la table à l'utilisateur exécutant la requête :
GRANT TABLE_OWNER TO EXECUTOR_USER;Remplacez les éléments suivants :
TABLE_OWNER: nom d'utilisateur ou rôle propriétaire de la table.EXECUTOR_USER: nom d'utilisateur de l'utilisateur exécutant, par exemplepostgres.
Révoquez les droits d'accès à la table :
REVOKE ALL PRIVILEGES ON TABLE SCHEMA_NAME.TABLE_NAME FROM USERNAME;Remplacez les éléments suivants :
SCHEMA_NAME: nom du schéma contenant la table.TABLE_NAME: nom de la table.USERNAME: nom de l'utilisateur dont vous souhaitez révoquer les droits d'accès.
Supprimer un utilisateur de base de données
Avant de supprimer un utilisateur, vous devez supprimer ou réaffecter tous les objets qu'il possède, et révoquer les droits qui lui ont été accordés sur d'autres objets. Pour en savoir plus, consultez Supprimer les dépendances utilisateur.
Console
Accédez à la page Clusters.
Cliquez sur le nom du cluster contenant l'utilisateur.
Cliquez sur Utilisateurs.
Sur la ligne correspondant à l'utilisateur, cliquez sur Ouvrir les actions pour cet utilisateur.
Sélectionnez Supprimer.
Dans la boîte de dialogue Supprimer le compte utilisateur ?, cliquez sur Supprimer.
psql
Utilisez les ressources suivantes :
DROP ROLE USERNAME;
gcloud
Exécutez la commande gcloud alloydb users delete suivante :
gcloud alloydb users delete USERNAME \
--cluster=CLUSTER_ID \
--region=REGION_IDRemplacez les éléments suivants :
USERNAME: nom d'utilisateur.CLUSTER_ID: ID du cluster auquel appartient l'utilisateur.REGION_ID: ID de la région dans laquelle se trouve le cluster. Exemple :us-central1