Gérer les utilisateurs PostgreSQL avec l'authentification intégrée

Pour contrôler l'accès à la base de données à l'aide d'identifiants basés sur un mot de passe, vous pouvez créer, gérer et supprimer des rôles, des utilisateurs et des groupes PostgreSQL et AlloyDB pour PostgreSQL à l'aide de l'authentification intégrée.

Créer des utilisateurs PostgreSQL dans AlloyDB

Lorsque vous créez des utilisateurs PostgreSQL, ils ne disposent initialement que du droit 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 les 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 postgres ou à un autre rôle utilisateur disposant des droits d'administrateur appropriés

Pour utiliser la console Google Cloud ou la Google Cloud CLI afin de gérer les utilisateurs d'un cluster, celui-ci doit disposer d'une instance principale. Si votre cluster ne possède 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 de 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

1. Accédez à la page Clusters.

   accéder aux clusters

2. Cliquez sur le nom du cluster auquel vous souhaitez ajouter un utilisateur.

3. Cliquez sur Utilisateurs.

4. Cliquez sur Ajouter un compte utilisateur.

5. 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.

6. Saisissez un nom d'utilisateur et un mot de passe pour le nouvel utilisateur.

7. Pour attribuer à l'utilisateur des rôles autres que le rôle par défaut alloydbsuperuser, procédez comme suit :

   1. Saisissez le nom du rôle dans Rôles.
   2. Pour ajouter le rôle, appuyez sur Entrée ou Tabulation.

   3. Sélectionnez x pour tout rôle que vous souhaitez supprimer pour cet utilisateur.

      Pour en savoir plus, consultez Rôles prédéfinis.

8. Cliquez sur Ajouter.

psql

Exécutez l'instruction CREATE USER suivante :

CREATE USER USERNAME WITH PASSWORD 'PASSWORD';

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• PASSWORD : mot de passe à attribuer à l'utilisateur.

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_ID

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données. USERNAME doit respecter les règles applicables aux identifiants SQL : sans caractères spéciaux ou entre guillemets doubles. Par exemple, db_user_1.

• PASSWORD : mot de passe de l'utilisateur de la base de données. 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 accorder 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 existant dans la console Google Cloud , connectez-vous à votre base de données à l'aide d'AlloyDB Studio et exécutez l'instruction GRANT suivante :

GRANT ROLE TO USERNAME;

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• ROLE : rôle à accorder à l'utilisateur. Par exemple, pour accorder à l'utilisateur des droits de super-utilisateur, attribuez-lui le rôle alloydbsuperuser.

psql

GRANT ROLE TO USERNAME;

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• ROLE : rôle à accorder à l'utilisateur. Par exemple, pour accorder à l'utilisateur des droits de super-utilisateur, attribuez-lui le rôle alloydbsuperuser.

Remarque : Vous ne pouvez pas utiliser cette méthode pour attribuer le rôle alloydbiamuser aux utilisateurs. Pour en savoir plus, consultez À propos de la gestion des utilisateurs de base de données dans AlloyDB pour PostgreSQL.

gcloud

Exécutez 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_ID

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• ROLES : liste d'éléments séparés par une virgule de tous les rôles que vous souhaitez attribuer à l'utilisateur. Répertoriez tous les rôles, y compris les rôles existants et les nouveaux rôles.

  Pour trouver la liste des rôles existants attribués à l'utilisateur, exécutez la commande suivante :

  gcloud alloydb users list --cluster=CLUSTER_ID --region=REGION_ID

  Remplacez les éléments suivants :

  • CLUSTER_ID : ID du cluster contenant l'utilisateur.

  • REGION_ID : ID de la région où se trouve le cluster. Exemple :us-central1

  Pour accorder à un utilisateur des droits de super-utilisateur, vous pouvez lui attribuer manuellement le rôle alloydbsuperuser à l'aide de psql, ou exécuter la commande gcloud alloydb users set-superuser avec l'argument --superuser=true :

  gcloud alloydb users set-superuser USERNAME \
  --superuser=true \
  --cluster=CLUSTER_ID \
  --region=REGION_ID

  Remplacez les éléments suivants :

  • USERNAME : nom de l'utilisateur de base de données.

  • CLUSTER_ID : ID du cluster contenant l'utilisateur.

  • REGION_ID : ID de la région où 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 de cette section.

Vous ne pouvez pas modifier ni définir le mot de passe d'un utilisateur 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

1. Accédez à la page Clusters.

   accéder aux clusters

2. Cliquez sur le nom du cluster contenant l'utilisateur de la base de données.

3. Cliquez sur Utilisateurs.

4. Sur la ligne représentant l'utilisateur, cliquez sur Ouvrir les actions pour cet utilisateur.

5. Sélectionnez Modifier le mot de passe.

6. 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 pas définir de mot de passe pour cet utilisateur, cochez la case Aucun mot de passe.

7. 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_ID

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• PASSWORD : nouveau mot de passe à attribuer à l'utilisateur.

• CLUSTER_ID : ID du cluster auquel l'utilisateur appartient.

• 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 les rôles d'un utilisateur dans la console Google Cloud , connectez-vous à votre base de données à l'aide d'AlloyDB Studio et exécutez l'instruction REVOKE suivante :

REVOKE ROLE FROM USERNAME;

Remplacez les éléments suivants :

• ROLE : rôle à révoquer pour l'utilisateur. Pour supprimer les droits de super-utilisateur d'un utilisateur, révoquez le rôle alloydbsuperuser de cet utilisateur.

• USERNAME : nom de l'utilisateur de base de données.

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 la 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_ID

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• ROLES : liste d'éléments séparés par une virgule de tous les rôles que vous souhaitez attribuer à l'utilisateur. Répertoriez tous les rôles, y compris les rôles existants et les nouveaux rôles.

  Pour trouver la liste des rôles existants attribués à l'utilisateur, exécutez la commande suivante :

  gcloud alloydb users list --cluster=CLUSTER_ID --region=REGION_ID

• 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 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

Remarque : Lorsque vous listez des utilisateurs à l'aide de la console Google Cloud ou de gcloud CLI, les utilisateurs ou les rôles configurés avec NOLOGIN sont exclus de la liste renvoyée. Si vous désactivez temporairement un utilisateur à l'aide de ALTER ROLE ... NOLOGIN, il est masqué dans ces listes du plan de contrôle. Pour afficher tous les rôles, connectez-vous à votre instance à l'aide de psql et interrogez directement pg_roles.

Console

1. Accédez à la page Clusters.

   accéder aux clusters

2. Cliquez sur le nom du cluster dont vous souhaitez afficher les utilisateurs.

3. Cliquez sur Utilisateurs.

psql

Utilisez la commande \du suivante pour afficher 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_ID

Remplacez 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 appartenant à cet utilisateur 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 tout compte disposant du rôle alloydbsuperuser, puis procédez comme suit pour chaque base de données de votre cluster :

1. Exécutez la requête suivante pour lister les objets (tables, vues, schémas et bases de données) 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 USERNAME par le nom de l'utilisateur que vous souhaitez supprimer.

2. Pour les objets identifiés, vous devez modifier la propriété de chaque objet.

   • 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 :

     • SCHEMA_NAME : nom du schéma contenant la table ou la vue.

     • OBJECT_NAME : nom de la table ou de la vue.

     • NEW_OWNER : nom de l'utilisateur auquel vous souhaitez accorder la propriété.

   • Pour modifier le propriétaire d'une base de données :

     ALTER DATABASE DATABASE_NAME OWNER TO NEW_OWNER;
     

     Remplacez les éléments suivants :

     • DATABASE_NAME : nom de la base de données.

     • NEW_OWNER : nom de l'utilisateur auquel vous souhaitez accorder la propriété.

3. Identifier et révoquer des droits. L'utilisateur peut également disposer de droits accordés sur d'autres objets ou d'appartenances à des rôles qui empêchent la suppression.

   • Pour lister les rôles attribués à 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'attribution d'un rôle :

     REVOKE ROLE_NAME FROM USERNAME;
     

     Remplacez les éléments suivants :

     • ROLE_NAME : appartenance au rôle que vous souhaitez révoquer.

     • USERNAME : nom de l'utilisateur dont vous souhaitez révoquer l'attribution de rôle.

   • Pour révoquer des droits sur une table :

     1. Attribuez le rôle de propriétaire de la table à l'utilisateur exécutant :

        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 de l'utilisateur qui exécute la commande, par exemple postgres.

     2. Révoquez les droits sur 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.

Supprimer un utilisateur de base de données

Avant de supprimer un utilisateur, vous devez supprimer ou réattribuer tous les objets qu'il possède, et révoquer tous les droits qui lui ont été accordés sur d'autres objets. Pour en savoir plus, consultez Supprimer les dépendances utilisateur.

Console

1. Accédez à la page Clusters.

   accéder aux clusters

2. Cliquez sur le nom du cluster contenant l'utilisateur.

3. Cliquez sur Utilisateurs.

4. Sur la ligne représentant l'utilisateur, cliquez sur Ouvrir les actions pour cet utilisateur.

5. Sélectionnez Supprimer.

6. 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_ID

Remplacez les éléments suivants :

• USERNAME : nom de l'utilisateur de base de données.

• CLUSTER_ID : ID du cluster auquel l'utilisateur appartient.

• REGION_ID : ID de la région dans laquelle se trouve le cluster. Exemple :us-central1

Étapes suivantes

• Gérer l'authentification IAM