Re: [WIP] ALTER ... OWNER TO ... CASCADE

> Sometimes I hit the following. You have created a database and schema inside
> it from the superuser (i.e. postgres). Than you want to change ownership of
> whole database to another user (i.e. alice), but only this database, not
> all other objects in all other databases.

Actually, it skips all files that belong to irrelevant databases:

/*
* We only operate on shared objects and objects in the current
* database
*/
if (sdepForm->dbid != MyDatabaseId &&
sdepForm->dbid != InvalidOid)
continue;

> It seems that REASSIGN OWNED doesn’t solve this already.

Yes, it doesn't solve this case. This is due to the fact that if the superuser
that created the database is 'pinned' (e.g. postgres), it is impossible to
track any object which depends on him, since such a dependency is not present
in the pg_shdepend (pay attention to the comment below):

if (isSharedObjectPinned(AuthIdRelationId, roleid, sdepRel))
{
.....
ereport(ERROR,
(errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
errmsg("cannot reassign ownership....

/*
* There's no need to tell the whole truth, which is that we
* didn't track these dependencies at all ...
*/
}

This prevents you from doing something like:

test=# reassign owned by postgres to test;
ERROR: cannot reassign ownership of objects owned by role postgres because
they are required by the database system

I think that my solution might fit better.

--
Dmitry Ivanov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company