Thread: Changing ownership of objects

Friends,
    When I was young and foolish and setting up my DB initially I ended up with
the objects in my DB owned by random users.  I'd like to write a script to
change the user for all objects to be the same, but I don't see any way to
change the ownership of sequences.  Any ideas?
Thanks,
Peter Darley

"Peter Darley" <pdarley@kinesis-cem.com> writes:
> When I was young and foolish and setting up my DB initially I ended up with
> the objects in my DB owned by random users.  I'd like to write a script to
> change the user for all objects to be the same, but I don't see any way to
> change the ownership of sequences.  Any ideas?

ALTER TABLE OWNER should work on sequences (also indexes and views).
At least in 7.2 or later (didn't look at earlier versions).  You could
also just poke pg_class.relowner ;-)

            regards, tom lane

I can't answer your question with code but you should be able to alter the
system classes. Other tables will link their owner id (and integer) to
pg_users.usesysid. Just alter which user the object point to and viola!
reassignment.

Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022
10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201
11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211

On Tue, 28 May 2002, Peter Darley wrote:

> Friends,
>     When I was young and foolish and setting up my DB initially I ended up with
> the objects in my DB owned by random users.  I'd like to write a script to
> change the user for all objects to be the same, but I don't see any way to
> change the ownership of sequences.  Any ideas?
> Thanks,
> Peter Darley
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

"Peter Darley" <pdarley@kinesis-cem.com> writes:

> Friends, When I was young and foolish and setting up my DB initially
> I ended up with the objects in my DB owned by random users.  I'd
> like to write a script to change the user for all objects to be the
> same, but I don't see any way to change the ownership of sequences.
> Any ideas?  Thanks, Peter Darley

If you are using some sh descendant somthing like:

for x in `psql -c '\dts' mydb -tA | cut -d\| -f1`; do
  psql -c "ALTER TABLE $x OWNER TO newowner;" mydb
done

should do the work.

Regards,
Manuel.