refactoring a database owner without "reassign owned"

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. 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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general