Re: Commands to change name, schema, owner

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Are there any tricky problems with any of these operations?

A few. Moving a table across schemas would require moving its indexes
and rowtype as well; conversely you should forbid moving the indexes and
rowtype by themselves, or altering their owners separately from the
table, or renaming the rowtype by itself. I am not real sure that
renaming a database is safe if there are active backends in it; doesn't
a backend have its dbname stored statically in a few places? Same goes
for renaming a user who has active backends. (Even if you can fix the
instances within the backend, what about connected clients, for instance
libpq's private state? And what if the rename means these clients
should not have been allowed to connect, per pg_hba.conf?) Renaming
operators would possibly change their precedence, which I don't *think*
would break rule dumps, but it's something to consider. Renaming
sequences would break nextval() and related calls on them, since we
don't have any way to find the references and update the text strings.
Changing a function owner might be interesting for SECURITY DEFINER
functions; I'm not sure what is likely to happen for active or
already-planned calls on the function.

> The command is: ALTER THING oldname RENAME TO newname;

> Requires being the owner of the object (or superuser for group, user,
> language) and CREATE privilege on containing schema.

The privilege considerations are doubtless different for the several
kinds of objects that don't live within schemas; could we see a more
complete spec?

> The command is: ALTER THING name SCHEMA newschema;

> Requires USAGE on old schema(?), owner of object, CREATE in new schema.

If you got as far as executing the command, you have USAGE on the old
schema, else you could never have looked up the object.

regards, tom lane