PostgreSQL | Role (User) ROLE Creation | Change Ownership of Objects Owned by a Specified Role to Another Role (REASSIGN OWNED)

Tags:

• PostgreSQL
• Database

This page explains how to change ownership of database objects owned by a specified role to another role by using the REASSIGN OWNED command. This is used, for example, when changing ownership to another role before deleting a role.

Change Object Ownership to Another Role

With the REASSIGN OWNED command, you can change ownership of all database objects that a specified role owns in a database to another role. The syntax is as follows.

REASSIGN OWNED BY old_role [, ...] TO new_role

This changes ownership of objects owned by the specified role (old_role) to another role (new_role).

–

Try it directly. The current devkuma database has a mykuma schema owned by the mykuma role.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 mykuma   | mykuma
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=#

The public schema also has a book table.

devkuma=# \dt
      릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주
--------+------+--------+----------
 public | blog | 테이블 | postgres
 public | book | 테이블 | mykuma
 public | memo | 테이블 | postgres
(3개 행)


devkuma=#

The mykuma role has SELECT privilege on the blog table in the public schema.

devkuma=# \dp
                                액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
 public | blog | 테이블 | postgres=arwdDxt/postgres+|               |
        |      |        | mykuma=r/postgres         |               |
 public | book | 테이블 |                           |               |
 public | memo | 테이블 | postgres=arwdDxt/postgres |               |
(3개 행)


devkuma=#

Now change ownership of the objects owned by the mykuma role in the devkuma database to the superkuma role. Run the following.

devkuma=# reassign owned by mykuma to superkuma;
REASSIGN OWNED
devkuma=#

Object ownership has been changed to the superkuma role.

–

Check ownership of the mykuma schema.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+-----------
 mykuma   | superkuma
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=#

You can confirm that ownership of the mykuma schema has changed to the superkuma role.

Next, check ownership of the book table created in the public schema.

devkuma=# \dt
      릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주
--------+------+--------+-----------
 public | blog | 테이블 | postgres
 public | book | 테이블 | superkuma
 public | memo | 테이블 | postgres
(3개 행)


devkuma=#

You can confirm that ownership of the book table has changed to the superkuma role.

Finally, check the privileges added to the mykuma role on the blog table in the public schema.

devkuma=# \dp
                                액세스 권한
 스키마 | 이름 |  종류  |        액세스 권한        | 칼럼 접근권한 | 정책
--------+------+--------+---------------------------+---------------+------
 public | blog | 테이블 | postgres=arwdDxt/postgres+|               |
        |      |        | mykuma=r/postgres         |               |
 public | book | 테이블 |                           |               |
 public | memo | 테이블 | postgres=arwdDxt/postgres |               |
(3개 행)


devkuma=#

Privileges granted to the mykuma role remain unchanged. The REASSIGN OWNED command does not change privileges.

–

This page explained how to change ownership of database objects owned by a specified role to another role by using the REASSIGN OWNED command.