PostgreSQL 删除角色

我们在使用 PostgreSQL 数据库的时候，经常会遇到这样的场景，就是某个角色，现在不需要了，我们需要删除。但是在删除的时候又提示你无法删除角色。下面看一下具体的情况。

DROP USER cloud_readonly
> ERROR:  role "cloud_readonly" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema public
privileges for database test
privileges for default privileges on new relations belonging to role postgres in schema public
privileges for table a

那么我们怎么删除不需要的用户角色呢？
-- 1. 确保重新分配所有对象的所有权  
REASSIGN OWNED BY cloud_readonly TO postgres;  

-- 2. 撤销表权限  
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM cloud_readonly;  

-- 3. 撤销序列权限  
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM cloud_readonly;  

-- 4. 撤销函数权限  
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM cloud_readonly;  

-- 5. 撤销默认权限  
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM cloud_readonly;   
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM cloud_readonly;  

-- 6. 确保撤销模式的权限  
REVOKE USAGE ON SCHEMA public FROM cloud_readonly;

-- 7. 确保撤销数据库的权限  
REVOKE ALL ON DATABASE test FROM cloud_readonly;  

-- 8. 最后，尝试删除角色  
DROP USER cloud_readonly;