Administra usuarios de PostgreSQL con autenticación integrada

En esta página, se describe cómo crear, administrar y quitar roles, usuarios y grupos de PostgreSQL y AlloyDB para PostgreSQL con los métodos de autenticación integrados de PostgreSQL basados en contraseñas.

Crea usuarios de PostgreSQL en AlloyDB

Cuando creas usuarios de PostgreSQL, estos solo comienzan con el privilegio LOGIN. Para obtener más información sobre estos privilegios, consulta CREATE USER.

Puedes cambiar los privilegios de cualquier usuario con el comando ALTER ROLE. Si creas un usuario nuevo con el cliente psql, puedes asociarlo con un rol diferente o asignarle privilegios diferentes.

Administra usuarios

Puedes crear y administrar usuarios de AlloyDB con Google Cloud CLI o con comandos de PostgreSQL. En las siguientes secciones, se muestran tareas comunes de administración de usuarios con ambos métodos.

Antes de comenzar

Para usar los comandos de PostgreSQL y administrar usuarios en un clúster, necesitas lo siguiente:

• Acceso al cliente de psql
• Acceso al usuario de la base de datos postgres o a otro rol de usuario con los privilegios administrativos adecuados

Para usar la Google Cloud consola o Google Cloud CLI para administrar usuarios en un clúster, ese clúster debe tener una instancia principal. Si tu clúster no tiene una instancia principal, debes crear una antes de poder administrar usuarios.

Crea un usuario de base de datos

Para crear un usuario de base de datos que se autentique directamente con la base de datos usando un nombre de usuario y una contraseña, también conocida como autenticación integrada, sigue los pasos que se indican en esta sección.

Si, en cambio, deseas crear un usuario de base de datos que use Identity and Access Management (IAM) para autenticarse, consulta Administra la autenticación de IAM.

Console

1. Ve a la página Clústeres.

   Ir a los clústeres

2. Haz clic en el nombre del clúster en el que deseas agregar un usuario.

3. Haz clic en Usuarios.

4. Haz clic en Agregar cuenta de usuario.

5. Deja seleccionada la opción Autenticación integrada.

   Si deseas usar IAM para autenticarte y acceder a tus clústeres de bases de datos, debes completar pasos adicionales para preparar tu proyecto y otorgar los permisos adecuados a tu usuario nuevo. Para obtener más información, consulta Administra la autenticación de IAM.

6. Ingresa un nombre de usuario y una contraseña para el usuario nuevo.

7. Para asignar roles que no sean el rol alloydbsuperuser predeterminado al usuario, sigue estos pasos:

   1. Ingresa el nombre del rol en Roles.
   2. Para agregar el rol, presiona Intro o Tab.

   3. Selecciona x para cualquier rol que quieras quitarle a ese usuario.

      Para obtener más información, consulta Roles predefinidos.

8. Haz clic en Agregar.

psql

CREATE USER USERNAME WITH PASSWORD PASSWORD;

Para obtener más información sobre cómo crear y definir usuarios de bases de datos, consulta CREATE USER.

Después de crear un usuario, puedes cambiar sus privilegios con el comando ALTER ROLE en el cliente psql.

gcloud

Usa el siguiente comando gcloud alloydb users create:

gcloud alloydb users create USERNAME \
--password=PASSWORD \
--cluster=CLUSTER_ID \
--region=REGION_ID

Reemplaza las siguientes variables:

• USERNAME: Nombre de usuario del usuario. USERNAME debe seguir las reglas para los identificadores de SQL: sin caracteres especiales o entre comillas dobles. Por ejemplo, db_user_1.

• PASSWORD: Contraseña del usuario. Para crear el usuario sin contraseña, omite este argumento.

• CLUSTER_ID: ID del clúster al que se agregará el usuario.

• REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

Después de crear un usuario, puedes cambiar sus privilegios con el comando gcloud alloydb users set-roles.

Además, puedes otorgar roles o privilegios de superusuario a un usuario mientras lo creas. Para ello, incluye los argumentos --db-roles o --superuser, como se describe en la siguiente sección.

Otorga roles a un usuario de la base de datos

Console

Para otorgar roles a un usuario de la base de datos, usa psql o gcloud CLI, en lugar de la consola de Google Cloud .

psql

GRANT ROLE to USERNAME;

Reemplaza las siguientes variables:

• USERNAME: Nombre de usuario del usuario.
• ROLE: Es el rol que se le otorgará al usuario. Por ejemplo, para otorgarle privilegios de superusuario, otórgale alloydbsuperuser.

Nota: No puedes usar este método para otorgar a los usuarios el rol de alloydbiamuser. Para obtener más información, consulta Acerca de la administración de usuarios de bases de datos en AlloyDB para PostgreSQL.

gcloud

Usa el siguiente comando gcloud alloydb users set-roles para especificar todos los roles que deseas que tenga el usuario:

gcloud alloydb users set-roles USERNAME \
--db-roles=ROLES \
--cluster=CLUSTER_ID \
--region=REGION_ID

Reemplaza las siguientes variables:

• USERNAME: Nombre de usuario del usuario.

• ROLES: Lista separada por comas de todos los roles que deseas que tenga el usuario. Asegúrate de incluir todos los roles, tanto los existentes como los nuevos.

  Para encontrar la lista de roles existentes asignados al usuario, ejecuta el siguiente comando:

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

  Reemplaza las siguientes variables:

  • CLUSTER_ID: Es el ID del clúster que contiene al usuario.

  • REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

  Para otorgarle privilegios de superusuario a un usuario, puedes otorgarle manualmente el rol alloydbsuperuser con psql o ejecutar el comando gcloud alloydb users set-superuser con el argumento --superuser=true:

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

  Reemplaza las siguientes variables:

  • CLUSTER_ID: Es el ID del clúster que contiene al usuario.

  • REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

Cambia la contraseña de un usuario de la base de datos

Para establecer una contraseña nueva para un usuario estándar de la base de datos de PostgreSQL, sigue los pasos que se indican en esta sección.

No puedes cambiar ni establecer la contraseña de un usuario basado en IAM porque los usuarios de IAM no usan contraseñas para autenticarse. Para obtener más información, consulta Conéctate con una cuenta de IAM.

Console

1. Ve a la página Clústeres.

   Ir a los clústeres

2. Haz clic en el nombre del clúster que contiene el usuario de la base de datos.

3. Haz clic en Usuarios.

4. En la fila que representa al usuario, haz clic en Abrir acciones para este usuario.

5. Selecciona Cambiar contraseña.

6. Especifica una contraseña:

   • Para establecer una contraseña nueva para este usuario, escribe la contraseña en el campo Contraseña.

   • Para no establecer una contraseña para este usuario, selecciona la casilla de verificación Sin contraseña.

7. Haz clic en Aceptar.

psql

ALTER USER USERNAME WITH PASSWORD PASSWORD;

Para obtener más información sobre cómo cambiar la contraseña de un usuario, consulta ALTER ROLE.

gcloud

Usa el siguiente comando gcloud alloydb users set-password:

gcloud alloydb users set-password USERNAME \
--password=PASSWORD \
--cluster=CLUSTER_ID \
--region=REGION_ID

Reemplaza las siguientes variables:

• USERNAME: Nombre de usuario del usuario.

• PASSWORD: Es la contraseña nueva que se asignará al usuario.

• CLUSTER_ID: Es el ID del clúster al que pertenece el usuario.

• REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

Cómo revocar un rol de un usuario de la base de datos

Console

Para revocar roles de un usuario, usa psql o gcloud CLI, en lugar de la consola de Google Cloud .

psql

Puedes revocar los privilegios otorgados anteriormente a uno o más roles, o bien revocar la membresía de un usuario en un rol, como se muestra a continuación:

REVOKE ROLE FROM USERNAME;

Para quitar los privilegios de superusuario de un usuario, revoca el rol alloydbsuperuser de ese usuario.

gcloud

Para revocar un rol con Google Cloud CLI, usa el siguiente comando gcloud alloydb users set-roles para especificar todos los roles que deseas que tenga el usuario y omitir los roles que se revocarán del usuario:

gcloud alloydb users set-roles USERNAME \
--db-roles=ROLES \
--cluster=CLUSTER_ID \
--region=REGION_ID

Reemplaza las siguientes variables:

• USERNAME: Nombre de usuario del usuario.

• ROLES: Lista separada por comas de todos los roles que deseas que tenga el usuario. Asegúrate de incluir todos los roles, tanto los existentes como los nuevos.

  Para encontrar la lista de roles existentes asignados al usuario, ejecuta el siguiente comando:

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

• CLUSTER_ID: Es el ID del clúster que contiene al usuario.

• REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

Para revocar los privilegios de superusuario, puedes quitar manualmente el rol alloydbsuperuser de un usuario con psql o ejecutar el comando gcloud alloydb users set-superuser con el argumento --superuser=false.

Cómo ver una lista de usuarios de la base de datos

Nota: Cuando se enumeran los usuarios con la consola de Google Cloud o la gcloud CLI, los usuarios o los roles configurados con NOLOGIN se filtran de la lista que se devuelve. Si inhabilitas temporalmente a un usuario con ALTER ROLE ... NOLOGIN, se ocultará de estos listados del plano de control. Para ver todos los roles, conéctate a tu instancia con psql y consulta pg_roles directamente.

Console

1. Ve a la página Clústeres.

   Ir a los clústeres

2. Haz clic en el nombre del clúster cuyos usuarios deseas ver.

3. Haz clic en Usuarios.

psql

Usa el siguiente comando \du para imprimir una tabla de todos los usuarios de la base de datos, que incluye sus membresías de grupo:

\du

gcloud

Usa el siguiente comando gcloud alloydb users list:

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

Reemplaza lo siguiente:

• CLUSTER_ID: Es el ID del clúster que contiene los usuarios.

• REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

Cómo quitar dependencias del usuario

Antes de borrar un usuario de la base de datos, debes quitar todos los objetos de la base de datos que le pertenezcan y revocar todos los privilegios que se le hayan otorgado. De lo contrario, el intento de eliminación fallará con un error (por ejemplo, ERROR: role "username" cannot be dropped because some objects depend on it).

Para identificar y quitar estas dependencias, conéctate a tu base de datos con el cliente de psql como el usuario postgres o cualquier cuenta con el rol de alloydbsuperuser, y realiza los siguientes pasos para cada base de datos de tu clúster:

1. Ejecuta la siguiente consulta para enumerar los objetos, como tablas, vistas, esquemas y bases de datos que pertenecen al usuario que deseas borrar:

   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';
   

   Reemplaza USERNAME por el nombre del usuario que deseas borrar.

2. Para los objetos identificados, debes cambiar la propiedad de cada uno.

   • Sigue estos pasos para cambiar el propietario de una tabla o vista:

     ALTER TABLE SCHEMA_NAME.OBJECT_NAME OWNER TO NEW_OWNER;
     

     Reemplaza lo siguiente:

     • NEW_OWNER: Es el nombre de usuario de la persona a la que deseas otorgarle la propiedad.

   • Para cambiar el propietario de una base de datos, haz lo siguiente:

     ALTER DATABASE DATABASE_NAME OWNER TO NEW_OWNER;
     

3. Identificar y revocar privilegios Es posible que el usuario también tenga privilegios otorgados en otros objetos o membresías de roles que impidan la eliminación.

   • Para enumerar las membresías de roles del usuario, haz lo siguiente:

     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;
     

   • Para revocar la membresía de un rol, haz lo siguiente:

     REVOKE ROLE_NAME FROM USERNAME;
     

     Reemplaza ROLE_NAME por la membresía de rol que deseas revocar.

   • Para revocar privilegios en una tabla, haz lo siguiente:

     1. Otorga el rol de propietario de la tabla al usuario que ejecuta la acción:

        GRANT TABLE_OWNER TO EXECUTOR_USER;
        

        Reemplaza lo siguiente:

        • TABLE_OWNER: Es el nombre de usuario o el rol que posee la tabla.
        • EXECUTOR_USER: Es el nombre de usuario del usuario que ejecuta la acción, por ejemplo, postgres.

     2. Revoca los privilegios en la tabla:

        REVOKE ALL PRIVILEGES ON TABLE SCHEMA_NAME.TABLE_NAME FROM USERNAME;
        

        Reemplaza lo siguiente:

        • SCHEMA_NAME: Es el nombre del esquema que contiene la tabla.
        • TABLE_NAME: Es el nombre de la tabla.
        • USERNAME: Es el nombre del usuario cuyos privilegios deseas revocar.

Borrar un usuario de la base de datos

Antes de borrar un usuario, debes descartar o reasignar todos los objetos que posee y revocar todos los privilegios que se le hayan otorgado al usuario en otros objetos. Para obtener más información, consulta Cómo quitar dependencias del usuario.

Console

1. Ve a la página Clústeres.

   Ir a los clústeres

2. Haz clic en el nombre del clúster que contiene al usuario.

3. Haz clic en Usuarios.

4. En la fila que representa al usuario, haz clic en Abrir acciones para este usuario.

5. Selecciona Quitar.

6. En el cuadro de diálogo ¿Quitar cuenta de usuario?, haz clic en Quitar.

psql

Para ello, usa los siguientes recursos:

DROP ROLE USERNAME;

gcloud

Usa el siguiente comando gcloud alloydb users delete:

gcloud alloydb users delete USERNAME \
--cluster=CLUSTER_ID \
--region=REGION_ID

Reemplaza lo siguiente:

• USERNAME: Nombre de usuario del usuario.

• CLUSTER_ID: Es el ID del clúster al que pertenece el usuario.

• REGION_ID: Es el ID de la región en la que reside el clúster. Por ejemplo, us-central1

¿Qué sigue?

• Administra la autenticación de IAM