Subject: Table owner change issue

From: Benjamin Patrick <[email-masked]>
Date: Thu, 21 Nov 2024 20:42:23 +0000

I am using PGAdmin 4.8.8.  New to the group and hoping this the correct group for assistance.
My instance is an AWS instance so there isn't a viable superuser.
I had a user create table with the table owner being themself.  user.schema.tablename
I wanted to change the owner to the dbowner, but was unable in pgadmin or script. dbowner.schema.tablename
I had the user run a script to the change the table owner to the dbowner but the user received an error unable to set role.  This occurred both in script and pgadmin.

The table owner was able to change to a mutual role that both the user and the dbowner are members.  From there the dbowner was still unable to change the table owner to the dbowner.

Any assistance would be super helpful.

---

From: Holger Jakobs <[email-masked]>
Date: Thu, 21 Nov 2024 21:54:18 +0100

Am 21.11.24 um 21:42 schrieb Benjamin Patrick:
> I had the user run a script to the change the table owner to the 
> dbowner but the user received an error unable to set role.  This 
> occurred both in script and pgadmin.
>
> The table owner was able to change to a mutual role that both the user 
> and the dbowner are members.  From there the dbowner was still unable 
> to change the table owner to the dbowner.
>
Actually, PGAdmin problems should be discussed on the mailing list of 
PGAdmin.

But since the question isn't related to PGAdmin, but rather PostgreSQL 
server, I can give an answer here.

Some hints:

  * Include the server version, at least the major version. Current is 17.1.
  * Include the commands you have tried. Don't type them, copy them!
  * Include all messages you get from the server.
  * If something doesn't work in PGAdmin, always try again using psql,
    because this is the only frontend maintained by the PostgreSQL
    developers.

It's not possible to change ownership of an object (table, view, schema, 
function, ...) to a role you aren't a member of. Otherwise, you might 
change it and lock yourself out.

And of course, you have to be owner of an object to alter it. Unless you 
are a superuser.

Kind Regards,

Holger

-- 

Holger Jakobs, Bergisch Gladbach

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 21 Nov 2024 23:12:20 +0100

> I wanted to change the owner to the dbowner, but was unable in pgadmin or script. dbowner.schema.tablename

See the documentation: https://www.postgresql.org/docs/current/sql-altertable.html

  "To alter the owner, you must be able to SET ROLE to the new owning role,
  and that role must have CREATE privilege on the table's schema."

Yours,
Laurenz Albe