PostgreSQL – Create or Copy New Superuser from Existing One

permissionspostgresqlusers

I have a superuser for a PostgreSQL database, let's call him userX.

How can I make a new superuser (let's call him userY) with all the same permissions, access, etc…?

Basically, I want a clone of everything about userX to userY and keep both users.

Best Answer

Well, if you have an existing superuser role called "super1", you could do:

CREATE ROLE super2 WITH SUPERUSER LOGIN;
GRANT super1 TO super2;

And, in theory at least, any of super1's privileges would be transferred over to super2. However, I'm not sure your question makes much sense, since superusers generally are allowed to override any privilege checks. As the documentation explains:

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.

Edit: and if you're interested in copying over the per-role configuration parameters (i.e. those documented under configuration parameters), then you could use a function like this (demo only, you may 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.
Related Question