英文:
Unable to drop default privileged user in Postgresql
问题
当我们尝试在PostgreSQL数据库中删除一个特定的用户时，我们遇到以下错误。
postgres=# DROP user xyz;
ERROR: 角色 "xyz" 不能被删除，因为某些对象依赖于它
DETAIL: 对属于角色 xyz 的新函数的默认特权的所有者
然后我们尝试使用以下命令检查默认特权。
postgres=# \ddp xyz
默认访问特权
所有者 | 模式 | 类型 | 访问特权
---------+--------+----------+-------------------
xyz | | function |
我们还尝试了下面的命令，但没有成功。
postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz ;
ALTER DEFAULT PRIVILEGES
postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz ;
ALTER DEFAULT PRIVILEGES
postgres=# \ddp xyz
默认访问特权
所有者 | 模式 | 类型 | 访问特权
---------+--------+----------+-------------------
xyz | | function |
英文:
When we are trying to drop 1 particular user in PostgreSQL Datbase we are getting below error.
postgres=# DROP user xyz;
> ERROR: role "xyz" cannot be dropped because some objects depend on it<br>
> DETAIL: owner of default privileges on new functions belonging to role xyz
Then we tried to check the default privileges by below command.
postgres=# \ddp xyz
    Default access privileges
Owner  | Schema |   Type   | Access privileges
---------+--------+----------+-------------------
xyz |       | function | 
Also we tried below command but no luck for us.
postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz ;
ALTER DEFAULT PRIVILEGES
postgres=# ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz ;
ALTER DEFAULT PRIVILEGES
postgres=# \ddp xyz
     Default access privileges
Owner  | Schema |   Type   | Access privileges
---------+--------+----------+-------------------
xyz |      | function | 
答案1
得分: 1
自从函数的“默认”默认权限为“对所有人和所有者的执行”，您将需要恢复该默认设置：
ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO xyz;
然后您应该能够删除该角色。
英文:
Since the “default” default privilege for functions is “EXECUTE for everyone and the owner”. you'll have to restore that default:
ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE xyz GRANT EXECUTE ON FUNCTIONS TO xyz;
Then you should be able to drop the role;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论