how to create duplicate role of a user in postgres

Question

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
...

If a new User B is created, I need the same privileges as,

User B has SELECT privileges on Table1
User B has EXECUTE privileges on Table2
...

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.

Ihor Romanchenko · Answer 1 · 2013-05-16T20:10:04.730

18

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.

edited May 16 '13 at 20:10

answered May 16 '13 at 20:04

Ihor Romanchenko

26,995
8
48
44

1

Insufficient. If A is a superuser, you also need: ALTER USER B WITH SUPERUSER; – Henley Nov 30 '15 at 20:10
13

Notice: this will make role B "inherit" all privileges of role A. Roles will be coupled in some sort of an inheritance relation. This will not actually duplicate privileges. – Paun Narcis Iulian Dec 29 '17 at 08:23

Chris Farmiloe · Answer 2 · 2013-05-16T20:13:26.003

14

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;

edited May 16 '13 at 20:13

answered May 16 '13 at 20:03

Chris Farmiloe

13,935
5
48
57

You do not need to specify `INHERIT` for role A. Only for role B (who will inherit the rights). + `INHERIT` is a default, so no need to write it explicitly. – Ihor Romanchenko May 16 '13 at 20:08
Good point, I was just being explicit, but it's probably just annoying ... *updates* – Chris Farmiloe May 16 '13 at 20:13
what if I dont want to inherit the role privileges but instead grant all privileges from A to B and also be able to change privileges of A without affecting B – UserBSS1 May 17 '13 at 09:39
1

In that case you should have a third role. A good model is often to conceptually separate roles (things with priviledges) from users (things that can login). So you might have `alice` and `bob` roles, then also have `manager` and `reader` roles. `alice` might be just a `reader` and `bob` might be both a `reader` and a `manager`. You could of course then also grant specific privileges to each user role if needed. – Chris Farmiloe May 17 '13 at 11:26

score 3 · Accepted Answer · answered Jun 26 '13 at 07:50

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

what about column, function, views permissions? – Aruna Nov 11 '15 at 04:13 — Aruna, Nov 11 '15 at 04:13

score 3 · Answer 4 · answered Nov 02 '20 at 01:06

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.

score 0 · Answer 5 · answered Nov 12 '15 at 03:27

I used following method to create a new user same as an existing user using 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.

This worked for me just fine. Hope this will help someone.

how to create duplicate role of a user in postgres

5 Answers5

Example output

Linked