postgresql 如何从 Postgres 数据库中删除用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34071195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Drop User from Postgres Database
提问by psvj
I have the following postgres users which I can view by invoking the \du command in the terminal as follows:
我有以下 postgres 用户，我可以通过在终端中调用 \du 命令来查看这些用户，如下所示：
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
tutorial1 | | {}
According to the postgres documentation, I should be able to drop the user called "tutorial1" by entering the following command:
根据postgres 文档，我应该能够通过输入以下命令删除名为“tutorial1”的用户：
postgres=# DROP USER tutorial1
However, when I use that command nothing happens. The documentation doesn't provide any hints as to why this isn't working, nor does it provide clear examples.
但是，当我使用该命令时，什么也没有发生。该文档没有提供有关为什么这不起作用的任何提示，也没有提供明确的示例。
That being said-- what is the command to drop this user?
话虽如此 - 删除此用户的命令是什么？
回答by Patrick Herrera
I've wasted way too much time on trying to find all the things to unwind. In addition to the above (or possibly as a complete replacement), refer to this answer to a similar question: https://stackoverflow.com/a/11750309/647581
我浪费了太多时间试图找到所有可以放松的东西。除了上述（或可能作为一个完整的替代品），参考这个类似问题的答案：https: //stackoverflow.com/a/11750309/647581
DROP OWNED BY your_user;
DROP USER your_user;
did the trick for me
对我有用
回答by Timofey 'Sasha' Kondrashov
If you find yourself here (like me) because you are unable to drop the user, the following template may be helpful:
如果您发现自己在这里（像我一样）因为无法删除用户，以下模板可能会有所帮助：
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM tutorial1;
DROP USER tutorial1;
The user may have privileges in other schemas, in which case you will have to run the appropriate REVOKE line with "public" replaced by the correct schema. To show all of the schemas and privilege types for a user, I edited the \dp command to make this query:
用户可能在其他模式中拥有特权，在这种情况下，您必须运行适当的 REVOKE 行，并用正确的模式替换“public”。为了显示用户的所有模式和权限类型，我编辑了 \dp 命令以进行以下查询：
SELECT
n.nspname as "Schema",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'S' THEN 'sequence'
WHEN 'f' THEN 'foreign table'
END as "Type"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.array_to_string(c.relacl, E'\n') LIKE '%postgres%';
I'm not sure which privilege types correspond to revoking on TABLES, SEQUENCES, or FUNCTIONS, but I think all of them fall under one of the three.
我不确定哪些特权类型对应于对 TABLES、SEQUENCES 或 FUNCTIONS 的撤销，但我认为它们都属于三者之一。
回答by Shubho Shaha
Your command is ok but you forget to put ;at the end of the command.
你的命令没问题，但你忘记把它放在;命令的末尾。
Try like this
像这样尝试
postgres=# DROP USER tutorial1; (Note I put semicolon at the end)
回答by Benyamin Jafari
First in Ubuntu terminal actions:
首先在 Ubuntu 终端操作中：
$ sudo -su postgres
$ psql postgres
then in postgres terminal:
然后在 postgres 终端中：
# \du
# drop user 'user_name';
# \du
[NOTE]:
[注意]：
Don't forget the semicolon ";"
不要忘记分号 ";"
[UPDATE]:
[更新]：
回答by DatabaseShouter
The answer from Timofey almost worked for me, but I also needed to revoke permissions at schema level. So my drop script looked like this:
Timofey 的答案几乎对我有用，但我还需要在架构级别撤销权限。所以我的 drop 脚本如下所示：
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM tutorial1;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM tutorial1;
DROP USER tutorial1;
So revoking may need to happen at any of the levels that happen here:
因此，可能需要在此处发生的任何级别进行撤销：
回答by Tadas Bublys
In case you have dot in user name test.user
如果您在用户名 test.user 中有点
drop user test.user;
ERROR: 42601: syntax error at or near "."
drop user "test.user";
Should solve it.
应该解决。
