From: Dmitry Ivanov <d(dot)ivanov(at)postgrespro(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Date: 2016-02-15 10:54:41
Message-ID: 3625246.JlcfNafjaX@abook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.


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.


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

Attachment Content-Type Size
alter_owner_cascade.patch text/x-patch 68.0 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2016-02-15 10:56:03 Re: innocuous: pgbench does FD_ISSET on invalid socket
Previous Message Fabien COELHO 2016-02-15 09:58:22 Re: extend pgbench expressions with functions