SQL REASSIGN

What is the SQL REASSIGN OWNED command used for?

Transfers ownership of all database objects owned by one or more roles to another role.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REASSIGN: PostgreSQL 8.2+, Amazon Redshift (limited), compatible forks like Greenplum. Not available in MySQL, SQL Server, Oracle, or SQLite.

SQL REASSIGN Full Explanation

REASSIGN OWNED is a PostgreSQL utility command that changes the ownership of every object (tables, views, sequences, functions, types, schemas, etc.) currently owned by one or more roles and assigns them to a different role. It is typically used before dropping a role so that dependent objects are not orphaned or accidentally removed. Only superusers or roles with INHERIT privileges on both the source and target roles can execute the command. The statement does not modify privileges granted on those objects; it only alters the owner attribute. For databases with many objects, the operation can take noticeable time because PostgreSQL rewrites catalog entries for each object.

SQL REASSIGN Syntax

REASSIGN OWNED BY source_role [, ...] TO target_role;

SQL REASSIGN Parameters

source_role (name) - One or more existing roles whose objects will be reassigned.
target_role (name) - The role that will become the new owner of the objects.

Example Queries Using SQL REASSIGN

-- Reassign all objects owned by alice to the dba role
REASSIGN OWNED BY alice TO dba;

-- Reassign objects for multiple departing contractors
REASSIGN OWNED BY contractor1, contractor2 TO data_engineer;

Expected Output Using SQL REASSIGN

The ownership column of every affected catalog entry is updated so the specified target role is recorded as owner
No result rows are returned; the server reports COMMAND OK

Use Cases with SQL REASSIGN

Preparing to DROP ROLE after an employee leaves
Centralizing ownership under a service account before implementing stricter permissions
Migrating objects from multiple legacy roles to a single standardized role

Common Mistakes with SQL REASSIGN

Forgetting the BY keyword and writing `REASSIGN OWNED alice TO bob` which is invalid syntax
Running the command without superuser or adequate role privileges and receiving an error
Assuming it also moves object privileges; REASSIGN OWNED does not touch GRANTs
Executing inside a database that is not connected to the objects you intend to change (the command only affects the current database)

First Introduced In

PostgreSQL 8.2

Frequently Asked Questions

What does REASSIGN OWNED do in PostgreSQL?

It changes the owner of every database object currently owned by one or more roles and assigns those objects to a new role.

Do I need superuser rights to run REASSIGN OWNED?

Yes. You must be a superuser or have privileges that let you SET ROLE to both the source and target roles; otherwise PostgreSQL raises an error.

Does REASSIGN OWNED modify object privileges?

No. The command only updates ownership. Existing GRANT or REVOKE permissions stay exactly as they were.

How can I undo a REASSIGN OWNED if I made a mistake?

Run the command inside a BEGIN...ROLLBACK block or issue another REASSIGN OWNED that swaps ownership back to the original role.