How to clone/mirror privileges from existing user to another user in p

If you have an existing superuser role called "user1", you could do: CREATE ROLE user2 WITH SUPERUSER LOGIN; GRANT user1 TO user2; And, in theory at least, any of user1 privileges would be transferred over to user2. However, I'm not sure your question makes much sense, since superusers generally are allowed to override any privilege checks. It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary. I'd be interested to hear what privileges your existing superuser role had which were not automatically granted to whatever new superuser role you created by default. (Demo: You need extra error handling, security considerations, etc. for production use) CREATE OR REPLACE FUNCTION copy_role_configs(source_role text, target_role text) RETURNS VOID AS $$ DECLARE setconfig_val text; eq_pos int; i int = 1; BEGIN LOOP SELECT setconfig[i] INTO setconfig_val FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = source_role); EXIT WHEN setconfig_val IS NULL; RAISE NOTICE 'Copying config % from % to %', setconfig_val, source_role, target_role; SELECT strpos(setconfig_val, '=') INTO eq_pos; EXECUTE 'ALTER ROLE ' || target_role || ' SET ' || substr(setconfig_val, 0, eq_pos + 1) || '''' || substr(setconfig_val, eq_pos + 1) || '''' ; i := i + 1; END LOOP; RETURN; END; $$ LANGUAGE plpgsql strict; -- Now call SELECT copy_role_configs('role1', 'role2') to copy configuration -- parameters from role1 to role2.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
Well, you can
* use \l+ in psql to list the ACLs and just write an according GRANT command
* use pg_dump -s -C -N \* -d database to get the ACL as GRANT/REVOKE commands and adopt the matching line(s)
+1
Sagar Lad5y ago
Hi Andy,

You can use this query : GRANT A TO B;

+1
Pranam Bhat5y ago
If you have an existing superuser role called "user1", you could do: CREATE ROLE user2 WITH SUPERUSER LOGIN; GRANT user1 TO user2; And, in theory at least, any of user1 privileges would be transferred over to user2. However, I'm not sure your question makes much sense, since superusers generally are allowed to override any privilege checks. It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary. I'd be interested to hear what privileges your existing superuser role had which were not automatically granted to whatever new superuser role you created by default. (Demo: You need extra error handling, security considerations, etc. for production use) CREATE OR REPLACE FUNCTION copy_role_configs(source_role text, target_role text) RETURNS VOID AS $$ DECLARE setconfig_val text; eq_pos int; i int = 1; BEGIN LOOP SELECT setconfig[i] INTO setconfig_val FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = source_role); EXIT WHEN setconfig_val IS NULL; RAISE NOTICE 'Copying config % from % to %', setconfig_val, source_role, target_role; SELECT strpos(setconfig_val, '=') INTO eq_pos; EXECUTE 'ALTER ROLE ' || target_role || ' SET ' || substr(setconfig_val, 0, eq_pos + 1) || '''' || substr(setconfig_val, eq_pos + 1) || '''' ; i := i + 1; END LOOP; RETURN; END; $$ LANGUAGE plpgsql strict; -- Now call SELECT copy_role_configs('role1', 'role2') to copy configuration -- parameters from role1 to role2.
+1