pgsql - change schema owner?

[csn]

Is there a way to change the owner of a schema (without deleting/recreating it)?

[Sxooter]

Actually, there's been a whole thread on one of the mailing lists this week about implementing it and how to do it. I don't think it's really resolved or implemented anywhere yet.

[csn]

Looks like they'll do something like "ALTER OWNER ..."?

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&group=comp.databases.postgresql.hackers

[chriskl]

Yep, that was me in that thread, writing ALTER OWNER.

csn - if you're up for a bit of catalog hacking, all you need to do is:

UPDATE pg_namespace SET nspowner=(SELECT usesysid FROM pg_shadow WHERE usename='<newuser>') AND nspname='<schemaname>';

And you need to do it as a superuser.

Chris

[chriskl]

Oh yeah, and you probably want to revoke all perms and re-grant them all on that schema 🙂