Como resolvo o erro “user cannot be dropped” (o usuário não pode ser descartado) no Amazon Redshift?

Breve descrição

Ao tentar descartar um usuário no Amazon Redshift, você pode ver uma destas mensagens de erro:

• 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

Esses erros podem ocorrer quando você tenta descartar os seguintes tipos de usuários:

• Um proprietário ou usuário alvo que tem permissões padrão.
• Proprietário de qualquer objeto, como bancos de dados, esquemas, tabelas, visualizações, procedimentos e bibliotecas.
• Um usuário que tem permissões em objetos.

Para resolver essas mensagens de erro, primeiro remova todas as permissões de usuário. Em seguida, transfira a propriedade do objeto ou remova a propriedade de grupo dos objetos.

Importante: você deve revogar as permissões de usuário e grupo de todos os bancos de dados no cluster do Amazon Redshift. Para visualizar a lista de bancos de dados no cluster, execute o comando SHOW DATABASES. Para ver o nome do banco de dados no qual você está conectado, execute o comando CURRENT_DATABASE.

Resolução

Instale scripts para criar visualizações

Como um superusuário Baixe e instale os scripts v_generate_user_grant_revoke_ddl.sql e v_find_dropuser_objs.sql do repositório do AWS Labs no site do GitHub. As visualizações dos scripts v_generate_user_grant_revoke_ddl.sql e v_find_dropuser_objs.sql usam o esquema admin em sua definição. Se você não tiver um esquema de admin no cluster do Amazon Redshift, modifique a definição em um esquema existente. Ou crie um esquema de admin.

Se houver alguma alteração na coluna em sua definição de visualização, descarte-a antes de criar uma nova visualização e definição. Ao criar uma nova exibição antes de descartar a exibição antiga, receberá um erro not valid table.

Descartar um usuário

Conclua as etapas a seguir:

1. Abra o editor de consultas v2. Em seguida, pesquise o usuário que você deseja descartar para ver as permissões de usuário concedidas.

2. Para reconceder as permissões a outro usuário, execute o seguinte 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;

   Observação: para reconceder as permissões, você deve ser um usuário com permissões no objeto. Ele deve conceder direitos de permissão a outro usuário. Se você não tiver permissões de concessão, poderá reconceder as permissões como superusuário.

3. Depois de reconceder as permissões a outro usuário, execute o seguinte comando para revogar as permissões do usuário:

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

   Observação: substitua grantor pelo nome do usuário que concede as permissões. Substitua grantee pelo nome do usuário que recebe as permissões.
   Se sua consulta não retornar nenhum registro ou se o comando DROP USER falhar, execute o seguinte 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;

   A consulta anterior lista todas as permissões adicionais que você deve revogar do usuário. Revogue essas permissões.

4. Para verificar se há listas de controle de acesso (ACLs) vazias, execute as seguintes 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');

   Observação: substitua username-to-be-dropped e user-name por seus valores. Para recuperar o nome e o ID do usuário, localize as entradas das colunas usename e usesysid na tabela PG_USER_INFO. Se houver entradas de usuário na tabela PG_DEFAULT_ACL, você não poderá descartar o usuário.

5. Se o usuário ainda tiver permissões em objetos, verifique se ele recebeu permissões assumerole:

   select pg_get_iam_role_by_user('user-name');

   Se o usuário tiver permissões assumerole, primeiro revogue assumerole do público. Em seguida, execute o seguinte comando para revogar username:

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

6. (Opcional) Se o usuário a ser descartado ainda tiver permissões em alguns objetos, verifique se ele faz parte de outro grupo. O usuário pode ter permissões concedidas por esse grupo. Ou o usuário pode ter permissões concedidas ao grupo PUBLIC.
   Para verificar as permissões que o usuário ainda tem, execute as seguintes consultas:

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

   Na saída da consulta pg_group, verifique se há entradas que listam o usuário na coluna grolist. Se o usuário pertencer a outro grupo, a coluna grolist mostrará o ID do usuário. Para verificar as permissões de um grupo, execute o seguinte 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';

   Observação: substitua objname e schemaname por sua tabela e esquema. Por padrão, o Amazon Redshift concede permissões concedidas ao grupo PUBLIC a todos os usuários.

7. Encontre todos os objetos que o usuário possui e transfira a propriedade para um usuário ou administrador diferente:

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

   A saída do comando anterior lista os comandos que você deve usar para transferir a propriedade para um novo usuário.

8. Repita as etapas de 2 a 7 em cada banco de dados no cluster do Amazon Redshift.
   Observação: para listar seus bancos de dados, execute o comando SHOW DATABASES.

9. Para remover o usuário do banco de dados, execute o seguinte comando:

   drop user username-to-be-dropped;

Descartar um grupo

Observação: antes de descartar um grupo, você deve revogar todas as permissões que ele tem para objetos.

Conclua as etapas a seguir:

1. Encontre todas as permissões concedidas ao grupo e, em seguida, revogue-as:

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

2. Repita a etapa 1 em cada banco de dados no cluster do Amazon Redshift para revogar as permissões do grupo em todos os bancos de dados.

3. Use o comando DROP GROUP para remover o grupo de usuários.