how to create duplicate role of a user in postgres
Try something like:
GRANT A TO B;
It will grant all right of role A to B.
For details read this
chapter of the manual.
First understand that roles
and users
are the same thing. In fact there isn't a thing called a user
really, it's just a ROLE
with a LOGIN
option.
Second roles
can be granted to other roles
.
Third priviledges on roles can be inherited.
So assuming you have created your user a
like:
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;
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.
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
Here's a quick way to create grant statements for newuser
, by copying all grants on db mydb
to grantee myuser
.
pg_dump mydb -s | egrep '^(GRANT|REVOKE).+TO "myuser"' | sed -E "s/\"myuser\"/\"newuser\"/g"
Note: The -s
flag makes pg_dump
execute quickly, because it's only dumping schema info.
Example output
GRANT SELECT,INSERT,UPDATE ON TABLE tabl1e TO "newuser";
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE table2 TO "newuser";
GRANT ALL ON PROCEDURE myprocedure(ids bigint[]) TO "newuser";
Simply run the output SQL grants or pipe them to psql
and you're all set.