Question: How to reassign all privileges to another role in PostgreSQL?

Question

How to reassign all privileges to another role in PostgreSQL?

Answers 0
Added at 2017-01-04 14:01
Tags
Question

​On an existing PostgreSQL server let say that I have two users "Alice" & "Bob" and I want to clean up my policy because they actually have the same job and should have exactly the same privileges on every objects of the database.

To do that I will create a new group role "Crew" from which they will both automatically inherit so that in the future, administrators only need to deal with the group role "Crew" and eventually add more users to it.

However because I need to be sure that neither Alice nor Bob will by embarrassed in any way by this change I need to transfer existing ownerships and privileges of both "Alice" & "Bob" to "Crew" while switching to this new policy.

To perform this I should:

  1. Create the Crew role
  2. Grant Crew to Alice & Bob (with INHERIT)
  3. Transfer Ownership of Alice & Bob to Crew
  4. Copy privileges from Alice & Bob to Crew (and keep more elevated privilege on conflict)

Step 3 is actually easy because there is a built-in command for that:

REASSIGN OWNED BY Alice TO Crew;
REASSIGN OWNED BY Bob TO Crew;

Step 4 however is more complicated because there is no magic command

REASSIGN PRIVILEGES FROM Alice TO Crew; --This don't actually exist too bad for me!


So what is the cleanest way transfer all privileges from "Alice" & "Bob" to "Crew" ?

Answers
Source Show
◀ Wstecz