Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL says must be owner of relation when you alter a table or change its owner

ErrorERROR: must be owner of relation

Short answer

Only a table's owner or a superuser may alter it, grant on it, or change its owner. Find the owner in pg_tables, then run the command as that role or as a superuser. To change an owner you must also be able to SET ROLE to the new owner, and the new owner needs CREATE on the schema.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare who you are with who owns the object. The pg_tables view has a tableowner column.

    SELECT current_user;
    SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';
  2. 2Match what you see, then jump to its fix

    1. 1ALTER TABLE, DROP, or GRANT fails for a role that did not create the tableYou are not the owner of the objectFix
    2. 2You own the table, yet OWNER TO still failsALTER ... OWNER TO is missing one of its own requirementsFix
    3. 3You are moving a whole app or removing a role and ALTER TABLE per object would take hoursMany objects need a new owner at onceFix

What does the error look like?

  • ERROR: must be owner of relation

Cause 1 of 3

You are not the owner of the object

1Check

The manual says you must own the table to use ALTER TABLE. Ordinarily only the object's owner or a superuser can grant or revoke privileges on it.

SELECT tablename, tableowner
FROM pg_tables
WHERE tablename = 'orders';

2Fix

Reconnect as the owner, or as a superuser, and run the command there. If your role should act as the owner, make it a member of the owner role first.

-- as a superuser or an admin of the owner role
GRANT orders_owner TO deploy_user;

3Verify

As the member role, run the alter you needed.

SET ROLE orders_owner;
ALTER TABLE orders ADD COLUMN note text;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

ALTER ... OWNER TO is missing one of its own requirements

1Check

To change an owner you must be able to SET ROLE to the new owning role, and that role must have CREATE on the table's schema. A superuser skips both rules.

\dn+ public

2Fix

Grant the new owner CREATE on the schema, and make sure your role is a member of the new owner with SET allowed (the default for GRANT of a role). Then run the change.

GRANT CREATE ON SCHEMA public TO new_owner;
GRANT new_owner TO current_owner_role;
ALTER TABLE orders OWNER TO new_owner;

3Verify

The new owner appears in the table list.

SELECT tablename, tableowner FROM pg_tables WHERE tablename = 'orders';

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

Many objects need a new owner at once

1Check

REASSIGN OWNED changes the owner of every object in the current database owned by the old role. It needs the same privileges ALTER ... OWNER TO would need, but the target role does not need CREATE on the containing schema.

SELECT schemaname, tablename FROM pg_tables WHERE tableowner = 'old_owner';

2Fix

Run REASSIGN OWNED in each database that holds objects of the old role.

REASSIGN OWNED BY old_owner TO new_owner;

3Verify

The old owner no longer owns tables in this database.

SELECT count(*) FROM pg_tables WHERE tableowner = 'old_owner';

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Run migrations as one dedicated owner role, so every table has the same owner.
  • Grant the owner role to people who need it, instead of sharing its password.
  • Create objects with the right owner from the start rather than fixing owners afterwards.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.