Thread: refactoring a database owner without "reassign owned"
Let's say you have a database which is owned (as well as all the contents within it) by the postgres internal user.
Having created or inherited a mess, how do you fix it?
database1=# REASSIGN OWNED BY postgres TO foo ;
ERROR: cannot reassign ownership of objects owned by role postgres because they are required by the database system
Is there some way simpler than going through every object of every type and doing an "ALTER <TYPE> <OBJECTNAME> OWNER to..." ?
This is on 9.2.4, but I think it applies to all versions.
Cheers,
Jeff
Le mercredi 08 mai 2013 à 14:11 -0700, Jeff Janes a écrit : > Let's say you have a database which is owned (as well as all the > contents within it) by the postgres internal user. > > > Having created or inherited a mess, how do you fix it? > with sed on Linux/Unix, you could do this : pg_dump -f mess.out mess sed -i 's/OWNER TO postgres/OWNER TO proper_username/' mess.out createdb clean psql -f mess.out clean You'll also want to modify the REVOKE ALL ON SCHEMA/GRANT ALL ON SCHEMA that are at the end of the dump file -- Salutations, Vincent Veyron http://marica.fr/site/demonstration Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
On Mon, May 13, 2013 at 9:13 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
Le mercredi 08 mai 2013 à 14:11 -0700, Jeff Janes a écrit :> Let's say you have a database which is owned (as well as all thewith sed on Linux/Unix, you could do this :
> contents within it) by the postgres internal user.
>
>
> Having created or inherited a mess, how do you fix it?
>
pg_dump -f mess.out mess
sed -i 's/OWNER TO postgres/OWNER TO proper_username/' mess.out
I wouldn't mind using that in a pinch, but I would be leary of doing it blindly as that text might occur in the data of the dump itself (for example, if I had inserted the body of your email into a table). It would be cleaner to dump just the schema and apply that sed script, then dump the data in a separate run without filtering.
But even more clean would be to do the pg_dump with the -O flag, and then "psql -U proper_username -f ...", I think, although I don't know that those two things are identical--I'm sure there must be some corner cases where they are not.
But it seems like there should be a good way to do this without needing a dump/restore.
I think "reassign owned" should detect that it is being invoked on the internal user (as it does now) but then instead of refusing to run, it should DWIM. I suppose that was not implemented because it is difficult to do so (but of course that is all the more reason not to leave it to the dba to figure out how to do it themselves). Perhaps this is a todo item?
Cheers,
Jeff
Jeff Janes escribió: > I think "reassign owned" should detect that it is being invoked on the > internal user (as it does now) but then instead of refusing to run, it > should DWIM. I suppose that was not implemented because it is difficult to > do so (but of course that is all the more reason not to leave it to the dba > to figure out how to do it themselves). Perhaps this is a todo item? Hm, so what would you have it do, precisely? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Mon, May 13, 2013 at 2:00 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Jeff Janes escribió:Hm, so what would you have it do, precisely?
> I think "reassign owned" should detect that it is being invoked on the
> internal user (as it does now) but then instead of refusing to run, it
> should DWIM. I suppose that was not implemented because it is difficult to
> do so (but of course that is all the more reason not to leave it to the dba
> to figure out how to do it themselves). Perhaps this is a todo item?
From the users perspective, I would have it reassign ownership of exactly those objects which are not "required by the database system", as the error message puts it.
From the implementers perspective, I don't really know. It does occur to me that pg_dump must know which objects those are, but how to get that knowledge into "reassign owned" may be another matter. Maybe I'll transfer this over to the hackers list once I have some time to look into it.
But knowing that pg_dump knows how to do this, leads me to this semi-automated solution to the original question (assuming you already ran "make installcheck" to obtain the database you want to refactor):
psql -c 'create role regression login;'
pg_dump -s regression | \
perl -lne 's/^(ALTER.*OWNER TO) postgres;/$1 regression;/ and print' | \
psql regression postgres
psql -c 'alter database regression owner to regression'
I don't know if there is any circumstance in which pg_dump will split the ALTER.*OWNER TO over more than one line.
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
> On Mon, May 13, 2013 at 2:00 PM, Alvaro Herrera <alvherre@2ndquadrant.com>wrote:
>> Jeff Janes escribi�:
>>> I think "reassign owned" should detect that it is being invoked on the
>>> internal user (as it does now) but then instead of refusing to run, it
>>> should DWIM.
>> Hm, so what would you have it do, precisely?
>> From the users perspective, I would have it reassign ownership of exactly
> those objects which are not "required by the database system", as the error
> message puts it.
ISTM this is precisely *not* what REASSIGN OWNED should do. Its charter
is to reassign all objects owned by the target role, not to second-guess
which ones the user meant.
I can see the possible value in a tool that would do what you suggest,
but I'm wary of sticking that functionality into REASSIGN OWNED. In
practice, it seems likely that people who are in this kind of fix would
need more fine-grained control than that anyway. Perhaps the right
thing is something close to your hack with pg_dump, wherein the tool
produces a file of ALTER OWNER commands and then the user can hand-edit
that before pulling the trigger.
regards, tom lane