Hi hackers,
Recently I've been working on a CASCADE option for ALTER ... OWNER TO
statement. Although it's still a working prototype, I think it's time to share
my work.
Introduction
============
As of now there's no way to transfer the ownership of an object and all its
dependent objects in one step. One has to manually alter the owner of each
object, be it a table, a schema or something else. This patch adds the
'CASCADE' option to every 'ALTER X OWNER TO' statement, including the 'ALTER
DATABASE db OWNER TO user CASCADE' which turns out to be a delicate matter.
Implementation
==============
There are two functions that process 'ALTER ... OWNER' statement:
ExecAlterOwnerStmt() and ATExecCmd(). The latter function deals with the tasks
that refer to all kinds of relations, while the first one handles the remaining
object types. Basically, all I had to do is to add 'cascade' flag to the
corresponding parsenodes and to make these functions call the dependency tree
walker function (which would change the ownership of the dependent objects if
needed). Of course, there are various corner cases for each kind of objects
that require special treatment, but the code speaks for itself.
The aforementioned 'ALTER DATABASE db ...' is handled in a special way. Since
objects that don't belong to the 'current database' are hidden, it is
impossible to change their owner directly, so we have to do the job in the
background worker that is connected to the 'db'. I'm not sure if this is the
best solution available, but anyway.
What works
==========
Actually, it seems to work in simple cases like 'a table with its inheritors'
or 'a schema full of tables', but of course there might be things I've
overlooked. There are some regression tests, though, and I'll continue to
write some more.
What's dubious
==============
It is unclear what kinds of objects should be transferred in case of database
ownership change, since there's no way to get the full list of objects that
depend on a given database. Currently the code changes ownership of all
schemas (excluding the 'information_schema' and some others) and their
contents, but this is a temporary limitation.
Feedback is welcome!
--
Dmitry Ivanov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company