PostgreSQL - 如何快速删除具有现有权限的用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3023583/
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
PostgreSQL - how to quickly drop a user with existing privileges
提问by millimoose
I'm trying to make restricted DB users for the app I'm working on, and I want to drop the Postgres database user I'm using for experimenting. Is there any way to drop the user without having to revoke all his rights manually first, or revoke all the grants a user has?
我正在尝试为我正在开发的应用程序创建受限数据库用户，并且我想删除我用于试验的 Postgres 数据库用户。有什么方法可以删除用户而不必先手动撤销他的所有权限，或者撤销用户拥有的所有授权？
回答by Tim Kane
How about
怎么样
DROP USER <username>
This is actually an alias for DROP ROLE.
这实际上是 的别名DROP ROLE。
You have to explicity drop any privileges associated with that user, also to move its ownership to other roles (or drop the object).
您必须明确删除与该用户关联的任何权限，还将其所有权移至其他角色（或删除对象）。
This is best achieved by
这最好通过
REASSIGN OWNED BY <olduser> TO <newuser>
and
和
DROP OWNED BY <olduser>
The latter will remove any privileges granted to the user.
后者将删除授予用户的任何权限。
See the postgres docs for DROP ROLEand the more detailed descriptionof this.
请参阅DROP ROLE的 postgres 文档以及对此的更详细描述。
Addition:
添加：
Apparently, trying to drop a user by using the commands mentioned here will only work if you are executing them while being connected to the same database that the original GRANTS were made from, as discussed here:
显然，尝试使用此处提到的命令删除用户仅当您在连接到原始 GRANTS 所用的同一数据库时执行它们时才有效，如下所述：
回答by Timofey 'Sasha' Kondrashov
The accepted answer resulted in errors for me when attempting REASSIGN OWNED BY or DROP OWNED BY. The following worked for me:
在尝试 REASSIGN OWNED BY 或 DROP OWNED BY 时，接受的答案导致我出错。以下对我有用：
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;
DROP USER username;
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 '%username%';
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 Eric E
Also note, if you have explicitly granted:
另请注意，如果您已明确授予：
CONNECT ON DATABASE xxx TO GROUP,
CONNECT ON DATABASE xxx TO GROUP,
you will need to revoke this separately from DROP OWNED BY, using:
您需要使用以下方法将其与 DROP OWNED BY 分开撤销：
REVOKE CONNECT ON DATABASE xxx FROM GROUP
REVOKE CONNECT ON DATABASE xxx FROM GROUP
回答by CD4
I had to add one more line to REVOKE...
我不得不再添加一行到 REVOKE ......
After running:
运行后：
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;
I was still receiving the error: username cannot be dropped because some objects depend on it DETAIL: privileges for schema public
我仍然收到错误： 无法删除用户名，因为某些对象依赖于它详细信息：架构公共权限
I was missing this:
我错过了这个：
REVOKE USAGE ON SCHEMA public FROM username;
Then I was able to drop the role.
然后我就可以放弃这个角色了。
DROP USER username;
回答by Preti
Here's what's finally worked for me :
这是最终对我有用的东西：
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM user_mike;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM user_mike;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA myschem FROM user_mike;
REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM user_mike;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschem REVOKE ALL ON SEQUENCES FROM user_mike;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschem REVOKE ALL ON TABLES FROM user_mike;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschem REVOKE ALL ON FUNCTIONS FROM user_mike;
REVOKE USAGE ON SCHEMA myschem FROM user_mike;
REASSIGN OWNED BY user_mike TO masteruser;
DROP USER user_mike ;
回答by gavenkoa
There is no REVOKE ALL PRIVILEGES ON ALL VIEWS, so I ended with:
没有REVOKE ALL PRIVILEGES ON ALL VIEWS，所以我以：
do $$
DECLARE r record;
begin
for r in select * from pg_views where schemaname = 'myschem'
loop
execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from "XUSER"';
end loop;
end $$;
and usual:
和通常：
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM "XUSER";
REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM "XUSER";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM "XUSER";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM "XUSER";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA myschem FROM "XUSER";
for the following to succeed:
为了以下成功：
drop role "XUSER";
回答by SuperNova
In commandline, there is a command dropuseravailable to do drop user from postgres.
在命令行中，有一个命令dropuser可用于从 postgres 中删除用户。
$ dropuser someuser
回答by Meshach Marimuthu
I faced the same problem and now found a way to solve it. First you have to delete the database of the user that you wish to drop. Then the user can be easily deleted.
我遇到了同样的问题，现在找到了解决它的方法。首先，您必须删除要删除的用户的数据库。然后可以轻松删除用户。
I created an user named "msf" and struggled a while to delete the user and recreate it. I followed the below steps and Got succeeded.
我创建了一个名为“msf”的用户，并努力删除该用户并重新创建它。我按照以下步骤操作并成功了。
1) Drop the database
1）删除数据库
dropdb msf
2) drop the user
2）删除用户
dropuser msf
Now I got the user successfully dropped.
现在我成功删除了用户。
