postgresql 如何在postgres中创建用户的重复角色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16591557/
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 create duplicate role of a user in postgres
提问by Usersbs
I need a new user but it should be granted all those privileges that the other existing user/role has.
我需要一个新用户，但它应该被授予其他现有用户/角色拥有的所有权限。
e.g.
例如
- User A has SELECT privileges on Table1
- User A has EXECUTE privileges on Table2
- ...
- 用户 A 对 Table1 具有 SELECT 权限
- 用户 A 对 Table2 具有 EXECUTE 权限
- ...
If a new User B is created, I need the same privileges as,
如果创建了新用户 B，我需要与以下相同的权限，
- User B has SELECT privileges on Table1
- User B has EXECUTE privileges on Table2
- ...
- 用户 B 对 Table1 具有 SELECT 权限
- 用户 B 对 Table2 具有 EXECUTE 权限
- ...
Dont ask why :/
不要问为什么：/
Actually User A has custom privileges on different tables, schemas, and functions; so its very tedious and lengthy process to manually grant permissions to the new user. Any help would be good.
实际上，用户 A 对不同的表、模式和函数具有自定义权限；所以手动授予新用户权限的过程非常繁琐和冗长。任何帮助都会很好。
采纳答案by Usersbs
I had to write the pgpsql code to loop through the privileges of User A and grant it to User B. It was done without any problem.
我不得不编写 pgpsql 代码来遍历用户 A 的权限并将其授予用户 B。它没有任何问题地完成。
create or replace function update_user_privileges() returns text as
$$
declare
info record;
str text;
begin
/*Grant privileges to user B the same as with user A for a given table schema*/
str:='';
FOR info IN
select * from information_schema.table_privileges where table_schema='public' and grantee = 'A'
LOOP
/*append the tables' name, for which we are assigning privileges from user A to B*/
str:= str ||info.table_name || ',';
/*this is the main statement to grant any privilege*/
execute 'GRANT '|| info.privilege_type ||' on table public.'|| info.table_name || ' to B';
END LOOP;
return str;
end
$$ language 'plpgsql';
Usage: Copy/paste this code to crate this function and then do
用法：复制/粘贴此代码以创建此函数，然后执行
select update_user_privileges();
**You have to adapt it for your table-schema and table-names. Hope it helps anyone
**您必须根据您的表模式和表名对其进行调整。希望它可以帮助任何人
回答by Igor Romanchenko
回答by Chris Farmiloe
First understand that rolesand usersare the same thing. In fact there isn't a thing called a userreally, it's just a ROLEwith a LOGINoption.
首先明白roles和users是一回事。事实上，没有user真正的东西，它只是ROLE一个LOGIN选项。
Second rolescan be granted to other roles.
二roles是可以授予其他roles。
Third priviledges on roles can be inherited.
角色的第三权限可以继承。
So assuming you have created your user alike:
所以假设你已经创建了你的用户a：
CREATE ROLE A LOGIN;
GRANT SELECT ON table1 TO a;
GRANT EXECUTE ON FUNCTION xxx TO a;
You should be able to create a second role that mirrors the first role like:
您应该能够创建反映第一个角色的第二个角色，例如：
CREATE ROLE b LOGIN;
GRANT a TO b;
回答by Vajira Lasantha
I used following method to create a new user same as an existing user using Ubuntu.
我使用以下方法创建一个与使用 Ubuntu 的现有用户相同的新用户。
- Get a full dump of existing database.
Use the following command to extract every line with the user you want to clone.
cat /path/to/db_dump_file | grep "existing_user_name" >> /path/to/extract.sql
Open extract.sql with a text editor and replace existing username with new username.
- Remove unwanted queries (if any).
- Now you have new SQL queries to create the new user.
- 获取现有数据库的完整转储。
使用以下命令提取您要克隆的用户的每一行。
cat /path/to/db_dump_file | grep "existing_user_name" >> /path/to/extract.sql
使用文本编辑器打开 extract.sql 并用新用户名替换现有用户名。
- 删除不需要的查询（如果有）。
- 现在您有新的 SQL 查询来创建新用户。
This worked for me just fine. Hope this will help someone.
这对我来说很好。希望这会帮助某人。
