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:
Show quoted text
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.
Is there any reason to have databases -not- owned by the users: Postgres or
root?
Thanks!
--
Jillian
Jillian,
Is there any reason to have databases -not- owned by the users: Postgres or
root?
Yes. If you have a multi-user database, you will want to restrict what the
users can change. In that case, the user has to be different from the
database owner, as the DB owner can change permissions.
Also, if your database is going to back up a web site, it's a good idea for
the web server to be a user with restricted permissions in case the site gets
hacked.
-Josh Berkus