Re: Trying to change the owner of some tables

I actually did one step better than that. I dropped the database and
recreated it and then imported the schema again. Working much better since
then, but I'm still having some issues with dropping a group role which is
telling me that the role has privileges for over 4000 objects currently. I
tried querying all the schemas and doing a revoke all privileges on each
schema for this role, but it's still arguing with me.

On Thu, Jun 25, 2015 at 8:10 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> John Scalia wrote:
> > Thanks guys, I was not aware of that command, but it did NOT succeed in
> changing these strangely missing tables. The tables appear, when I try to
> drop the old owner as:
> >
> > second_schema.partition_table_name_one;
> >
> > then two, three, etc., I've only been successful using
> >
> > alter table second_schema.partition_table_name_one owner to userB;
> >
> > But I'd rather not do that for 2000+ entries.
>
> I think the current situation is that the pg_class entries point to one
> user, but the pg_shdepend entries point to the original owner. That is
> an example case of catalog "corruption" -- or rather inconsistency.
>
> I think what you should do is update pg_class back to the original
> state, and then apply REASSIGN OWNED BY. If the new owner had tables
> other than those owned by the one you're trying to delete, this would be
> pretty complicated, though -- in this case you should probably compare
> the pg_shdepend entries to find out which tables were owned by the other
> user, so that you know which ones to update. This should doable, if a
> bit tricky.
>
> Hopefully this episode demonstrates the folly of updating catalogs "by
> hand". I suggest never to do that again, and if somebody else tells you
> to, point them here.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>