¿Cómo puedo solucionar el error «user cannot be dropped» en Amazon Redshift?

Breve descripción

Al intentar eliminar un usuario en Amazon Redshift, es posible que aparezca uno de los siguientes mensajes de error:

• ERROR: user «username» cannot be dropped because some objects depend on it
• ERROR: user «username» cannot be dropped because the user has a privilege on some object
• ERROR: user «username» cannot be dropped because the user owns some object

Estos errores pueden producirse al intentar eliminar los siguientes tipos de usuarios:

• Un propietario o un usuario objetivo que tiene los permisos predeterminados.
• El propietario de cualquier objeto (como bases de datos, esquemas, tablas, vistas, procedimientos y bibliotecas).
• Un usuario que tiene permisos en relación con los objetos.

Para solucionar estos mensajes de error, primero debes eliminar los permisos de los usuarios. A continuación, transfiere la propiedad de los objetos o elimina la propiedad de grupo de dichos objetos.

Importante: Debes revocar los permisos de usuarios y grupos de todas las bases de datos en el clúster de Amazon Redshift. Para ver la lista de bases de datos del clúster, ejecuta el comando SHOW DATABASES. Para ver el nombre de la base de datos en la que has iniciado sesión, ejecuta el comando CURRENT_DATABASE.

Resolución

Instalación de scripts para crear vistas

Como superusuario, descarga los scripts v_generate_user_grant_revoke_ddl.sql y v_find_dropuser_objs.sql del repositorio de Laboratorios de AWS en el sitio web de GitHub e instálalos. Las vistas para los scripts v_generate_user_grant_revoke_ddl.sql y v_find_dropuser_objs.sql utilizan el esquema admin en su definición. Si no tienes un esquema admin en el clúster de Amazon Redshift, modifica la definición en un esquema existente. Como alternativa, crea un esquema admin.

Si cambias las columnas de la definición de la vista, elimina la vista existente antes de crear una nueva vista y una nueva definición. Cuando creas una nueva vista antes de eliminar la vista anterior, se mostrará el error not valid table.

Eliminación de un usuario

Sigue estos pasos:

1. Abre el editor de consultas v2. A continuación, busca el usuario que quieres eliminar para ver los permisos de usuario concedidos.

2. Para volver a conceder los permisos a otro usuario, ejecuta el siguiente comando:

   select regexp_replace(ddl,grantor,'superuser') from v_generate_user_grant_revoke_ddl where grantor='username' and ddltype='grant' and objtype <>'default acl' order by objseq,grantseq;

   Nota: Para volver a conceder los permisos, debes ser un usuario con permisos en relación con el objeto. Además, debes tener derechos para otorgar permisos a otro usuario. Si no tienes permisos de concesión, puedes volver a otorgar los permisos como superusuario.

3. Después de volver a conceder los permisos a otro usuario, ejecuta el siguiente comando para revocar los permisos del usuario:

   select ddl from v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='username' or grantor='username') order by objseq, grantseq desc;

   Nota: Sustituye grantor por el nombre del usuario que concede los permisos. Sustituye grantee por el nombre del usuario que recibe los permisos.
   Si tu consulta no devuelve ningún registro o si el comando DROP USER no surte efecto, ejecuta el siguiente comando:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and ddl ilike '%user-to-be-dropped%' order by objseq, grantseq desc;

   La consulta anterior muestra todos los permisos adicionales que debes revocar al usuario. Revoca estos permisos.

4. Para comprobar si hay listas de control de acceso (ACL) en blanco, ejecuta las siguientes consultas:

   select * from pg_user where username = 'username-to-be-dropped';   
   select * from pg_default_acl where defacluser= user-id;
   select pg_get_iam_role_by_user('user-name');

   Nota: Sustituye el username-to-be-dropped y user-name por tus valores. Para recuperar el nombre y el ID del usuario, busca las entradas de columna username y usesysid en la tabla PG_USER_INFO. Si hay entradas de usuario en la tabla PG_DEFAULT_ACL, no podrás eliminar el usuario.

5. Si el usuario sigue teniendo permisos en relación con algunos objetos, comprueba si tienes permisos assumerole:

   select pg_get_iam_role_by_user('user-name');

   Si el usuario tiene permisos assumerole, primero revoca assumerole del público. A continuación, ejecuta el siguiente comando para revocar username:

   revoke assumerole on all from public for all;  
   revoke assumerole on all from user-name for all;

6. (Opcional) Si el usuario sigue teniendo permisos sobre algunos objetos, comprueba si forma parte de otro grupo. Es posible que el usuario tenga permisos otorgados desde ese grupo. También puede ser que tenga permisos otorgados al grupo PUBLIC.
   Para comprobar los permisos que aún tiene el usuario, ejecuta las siguientes consultas:

   select * from pg_user where username = 'username-to-be-dropped';  
   select * from pg_group;

   En el resultado de la consulta pg_group, comprueba si hay entradas que contengan el usuario en la columna grolist. Si el usuario pertenece a otro grupo, la columna grolist muestra el ID de usuario. Para comprobar los permisos de un grupo, ejecuta el siguiente comando:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group group-name' ;
   select * from admin.v_generate_user_grant_revoke_ddl where objname='timecards' and schemaname='postgres' and grantee='PUBLIC' and ddltype='revoke';

   Nota: Sustituye objname y schemaname por tu tabla y tu esquema. De forma predeterminada, Amazon Redshift concede los permisos concedidos al grupo PUBLIC a todos los usuarios.

7. Busca todos los objetos que pertenezcan al usuario y, a continuación, transfiera la propiedad a otro usuario o administrador:

   select ddl||'newuser;' as ddl from admin.v_find_dropuser_objs where objowner = 'username-to-be-dropped';

   El resultado del comando anterior muestra un listado de los comandos que debes usar para transferir la propiedad a otro usuario nuevo.

8. Repite los pasos del 2 al 7 en cada base de datos del clúster de Amazon Redshift.
   Nota: Para enumerar las bases de datos, ejecuta el comando SHOW DATABASES.

9. Para eliminar el usuario de la base de datos, ejecuta el siguiente comando:

   drop user username-to-be-dropped;

Eliminación de un grupo

Nota: Antes de eliminar un grupo, debes revocar todos los permisos sobre objetos que tenga el grupo.

Sigue estos pasos:

1. Busca todos los permisos otorgados al grupo y, a continuación, revócalos:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group group-name';

2. Repite el paso 1 en cada base de datos del clúster de Amazon Redshift para revocar los permisos del grupo en todas las bases de datos.

3. Para eliminar el grupo de usuarios, ejecuta el comando DROP GROUP.