Administra usuarios de PostgreSQL con autenticación integrada

Para controlar el acceso a la base de datos con credenciales basadas en contraseñas, puedes crear, administrar y quitar roles, usuarios y grupos de PostgreSQL y AlloyDB para PostgreSQL con la autenticación integrada.

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 cualquiera de los 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 de administrador adecuados

Para usar la consola de Google Cloud o Google Cloud CLI con el objetivo de 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 para poder administrar usuarios.

Crea un usuario de base de datos

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

Para 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 al 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 base 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 predeterminado alloydbsuperuser 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 en cualquier rol que quieras quitar para ese usuario.

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

8. Haz clic en Agregar.

psql

Ejecuta la siguiente sentencia CREATE USER:

CREATE USER USERNAME WITH PASSWORD 'PASSWORD';

Reemplaza lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos.

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

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 lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos. USERNAME debe seguir las reglas para los identificadores de SQL: sin caracteres especiales o entre comillas dobles. Por ejemplo, db_user_1.

• PASSWORD: Es la contraseña del usuario de la base de datos. Para crear el usuario sin una contraseña, omite este argumento.

• CLUSTER_ID: Es el 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 base de datos existente en la consola de Google Cloud , conéctate a tu base de datos con AlloyDB Studio y ejecuta la siguiente sentencia GRANT:

GRANT ROLE TO USERNAME;

Reemplaza lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos.

• ROLE: Es el rol que se otorgará al usuario. Por ejemplo, para otorgar al usuario privilegios de superusuario, asígnale el rol alloydbsuperuser.

psql

GRANT ROLE TO USERNAME;

Reemplaza lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos.

• ROLE: Es el rol que se otorgará al usuario. Por ejemplo, para otorgar al usuario privilegios de superusuario, asígnale el rol alloydbsuperuser.

Nota: No puedes usar este método para otorgar a los usuarios el rol 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 quieres que tenga el usuario:

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

Reemplaza lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos.

• ROLES: Es una lista separada por comas de todos los roles que deseas que tenga el usuario. Enumera todos los roles, lo que incluye los roles existentes y 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 lo siguiente:

  • 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 otorgar privilegios de superusuario a un usuario, puedes otorgarle de forma manual el rol alloydbsuperuser con psql, o bien 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 lo siguiente:

  • USERNAME: Es el nombre de usuario de la base de datos.

  • 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 de base de datos de PostgreSQL estándar, sigue los pasos de 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, ingrésala 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 lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos.

• 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

Revoca un rol de un usuario de la base de datos

Console

Para revocar roles de un usuario en la consola de Google Cloud , conéctate a tu base de datos con AlloyDB Studio y ejecuta la siguiente sentencia REVOKE:

REVOKE ROLE FROM USERNAME;

Reemplaza lo siguiente:

• ROLE: Es el rol que se revocará del usuario. Para quitar los privilegios de superusuario de un usuario, revoca el rol alloydbsuperuser de ese usuario.

• USERNAME: Es el nombre de usuario de la base de datos.

psql

Puedes revocar los privilegios otorgados con anterioridad de uno o más roles o 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 lo siguiente:

• USERNAME: Es el nombre de usuario de la base de datos.

• ROLES: Es una lista separada por comas de todos los roles que deseas que tenga el usuario. Enumera todos los roles, incluidos los existentes y 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 de forma manual el rol alloydbsuperuser de un usuario con psql o ejecutar el comando gcloud alloydb users set-superuser con el argumento --superuser=false.

Visualiza una lista de usuarios de la base de datos

Nota: Cuando se enumeran los usuarios con la consola de Google Cloud o gcloud CLI, los usuarios o roles configurados con NOLOGIN se filtran de la lista que se muestra. Si inhabilitas temporalmente a un usuario con ALTER ROLE ... NOLOGIN, este se ocultará de estas listas 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

Quita las dependencias de los usuarios

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 psql como el usuario postgres o cualquier cuenta con el rol 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 quieres borrar.

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

   • Para cambiar el propietario de una tabla o vista, sigue estos pasos:

     ALTER TABLE SCHEMA_NAME.OBJECT_NAME OWNER TO NEW_OWNER;
     

     Reemplaza lo siguiente:

     • SCHEMA_NAME: Es el nombre del esquema que contiene la tabla o vista.

     • OBJECT_NAME: Es el nombre de la tabla o vista.

     • NEW_OWNER: Es el nombre de usuario al que deseas otorgar la propiedad.

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

     ALTER DATABASE DATABASE_NAME OWNER TO NEW_OWNER;
     

     Reemplaza lo siguiente:

     • DATABASE_NAME: Es el nombre de la base de datos.

     • NEW_OWNER: Es el nombre de usuario del usuario al que deseas otorgar la propiedad.

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 lo siguiente:

     • ROLE_NAME: Es la membresía del rol que deseas revocar.

     • USERNAME: Es el nombre del usuario cuya membresía de rol deseas revocar.

   • Para revocar privilegios en una tabla, sigue estos pasos:

     1. Otorga el rol de propietario de la tabla al usuario que ejecuta la consulta:

        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, 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 quitar o reasignar todos los objetos que posee y revocar todos los privilegios que se le hayan otorgado en otros objetos. Para obtener más información, consulta Cómo quitar dependencias de usuarios.

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.

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 ¿Quieres quitar la 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: Es el nombre de usuario de la base de datos.

• 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