Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

REASSIGN OWNED BY ​

Change the ownership of database objects that belong to a database role.

REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
TO { new_role | CURRENT_USER | SESSION_USER }

This command is typically run before a specific user is dropped. This command only affects objects within the current database, so you may have to repeat it in each database that contains objects owned by the user in question. Even then you may find that privileges on other objects need to be revoked before the user can be dropped. The REASSIGN OWNED BY command does not affect any privileges granted to roles on objects not owned by them.

The REASSIGN OWNED BY command must be run by a user with privileges on both the old role (or roles) and the new role.

The DROP OWNED BY command is an alternative command that drops all the database objects owned by the specified role or roles (assuming there are no dependencies).

Example ​

For example, reassign ownership of objects from user bobr to user yb100. Other objects in the database may be owned by bobr, not just the tables listed in this example. The REASSIGN OWNED BY command transfers ownership of all objects in the premdb database that are currently owned by bobr.

premdb=> \d
          List of relations
 Schema |    Name     | Type  |  Owner  
--------+-------------+-------+---------
 public | awayteam    | table | brumsby
 public | bobr_match  | table | bobr
 public | bobr_season | table | bobr
 public | bobr_team   | table | bobr
 public | hometeam    | table | brumsby
 public | match       | table | brumsby
 public | season      | table | brumsby
 public | team        | table | brumsby
(8 rows)

premdb=> \c premdb yellowbrick
Password for user yellowbrick: 
You are now connected to database "premdb" as user "yellowbrick".

premdb=# create user yb100;
CREATE ROLE
premdb=# reassign owned by bobr to yb100;
REASSIGN OWNED
premdb=# \d
          List of relations
 Schema |    Name     | Type  |  Owner  
--------+-------------+-------+---------
 public | awayteam    | table | brumsby
 public | bobr_match  | table | yb100
 public | bobr_season | table | yb100
 public | bobr_team   | table | yb100
 public | hometeam    | table | brumsby
 public | match       | table | brumsby
 public | season      | table | brumsby
 public | team        | table | brumsby
(8 rows)

Parent topic:SQL Commands