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

Dmitry Ivanov <d.ivanov@postgrespro.ru> writes:

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.

TBH, this sounds like a completely terrible idea. There are far too many
sorts of dependencies across which one would not expect ownership to
propagate; for example, use of a function in a view, or even just a
foreign key dependency between two tables.

I'm not even clear that there are *any* cases where this behavior is
wanted, other than perhaps ALTER OWNER on an extension --- and even there,
what you would want is altering the ownership of the member objects, but
not everything that depends on the member objects.

So basically, a generic CASCADE facility sounds like a lot of work to
produce something that would seldom be anything but a foot-gun.

regards, tom lane

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

Dmitry Ivanov <d.ivanov@postgrespro.ru> writes:

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.

TBH, this sounds like a completely terrible idea. There are far too many
sorts of dependencies across which one would not expect ownership to
propagate; for example, use of a function in a view, or even just a
foreign key dependency between two tables.

Well, actually this is a statement of the fact, and I don't propose enabling
this option for every dependency possible. This patch includes an experimental
feature that anyone can try and discuss, nothing more. Besides, it acts a lot
like 'drop ... cascade' (the findDependentObjects() function is used under the
hood), so the behavior is totally predictable. It also writes down all objects
that have been touched, so no change goes unnoticed.

I'm not even clear that there are *any* cases where this behavior is
wanted, other than perhaps ALTER OWNER on an extension

At very least this might be useful in order to change owner of all tables
which inherit some table.

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

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

TBH, this sounds like a completely terrible idea. There are far too many
sorts of dependencies across which one would not expect ownership to
propagate; for example, use of a function in a view, or even just a
foreign key dependency between two tables.

I'm not even clear that there are *any* cases where this behavior is
wanted, other than perhaps ALTER OWNER on an extension --- and even there,
what you would want is altering the ownership of the member objects, but
not everything that depends on the member objects.

So basically, a generic CASCADE facility sounds like a lot of work to
produce something that would seldom be anything but a foot-gun.

DELETE FROM or TRUNCATE could be a foot-gun too, but it's not a reason to
remove tham. I faced with problem when I tried to change owner of datadase with
all objects inside. Think, this feature could be useful although it should
restricted to superuser obly.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

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

Teodor Sigaev <teodor@sigaev.ru> writes:

So basically, a generic CASCADE facility sounds like a lot of work to
produce something that would seldom be anything but a foot-gun.

DELETE FROM or TRUNCATE could be a foot-gun too, but it's not a reason to
remove tham. I faced with problem when I tried to change owner of datadase with
all objects inside. Think, this feature could be useful although it should
restricted to superuser obly.

That's a pretty weak argument, and I do not think you have thought through
all the consequences. It is not hard at all to imagine cases where using
this sort of thing could be a security vulnerability. Are you familiar
with the reasons why Unix systems don't typically allow users to "give
away" ownership of files? The same problem exists here.

To be concrete about it:

1. Alice does, say, "CREATE EXTENSION cube".

2. Bob creates a security-definer function owned by himself, using a
"cube"-type parameter so that it's dependent on the extension.
(It needn't actually do anything with that parameter.)

3. Alice does ALTER EXTENSION cube OWNER TO charlie CASCADE.

4. Bob now has a security-definer function owned by (and therefore
executing as) Charlie, whose contents were determined by Bob.
Game over for Charlie ... and for everyone else too, if Charlie is
a superuser, which is not unlikely for an extension owner.

The only way Alice can be sure that the ALTER EXTENSION is safe is if
she manually inspects every dependent object, in which case she might
as well not use CASCADE.

Moreover, the use case you've sketched (ie, change ownership of all
objects inside a database) doesn't actually have anything to do with
following dependencies. It's a lot closer to REASSIGN OWNED ... in
fact, it's not clear to me why REASSIGN OWNED doesn't solve that
use-case already.

I remain of the opinion that this is a terrible idea.

regards, tom lane

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

15 февр. 2016 г., в 19:25, Tom Lane <tgl@sss.pgh.pa.us> написал(а):

Teodor Sigaev <teodor@sigaev.ru> writes:

So basically, a generic CASCADE facility sounds like a lot of work to
produce something that would seldom be anything but a foot-gun.

DELETE FROM or TRUNCATE could be a foot-gun too, but it's not a reason to
remove tham. I faced with problem when I tried to change owner of datadase with
all objects inside. Think, this feature could be useful although it should
restricted to superuser obly.

That's a pretty weak argument, and I do not think you have thought through
all the consequences. It is not hard at all to imagine cases where using
this sort of thing could be a security vulnerability. Are you familiar
with the reasons why Unix systems don't typically allow users to "give
away" ownership of files? The same problem exists here.

To be concrete about it:

1. Alice does, say, "CREATE EXTENSION cube".

2. Bob creates a security-definer function owned by himself, using a
"cube"-type parameter so that it's dependent on the extension.
(It needn't actually do anything with that parameter.)

3. Alice does ALTER EXTENSION cube OWNER TO charlie CASCADE.

4. Bob now has a security-definer function owned by (and therefore
executing as) Charlie, whose contents were determined by Bob.
Game over for Charlie ... and for everyone else too, if Charlie is
a superuser, which is not unlikely for an extension owner.

The only way Alice can be sure that the ALTER EXTENSION is safe is if
she manually inspects every dependent object, in which case she might
as well not use CASCADE.

Seems to be a problem that should be addressed.

Moreover, the use case you've sketched (ie, change ownership of all
objects inside a database) doesn't actually have anything to do with
following dependencies. It's a lot closer to REASSIGN OWNED ... in
fact, it's not clear to me why REASSIGN OWNED doesn't solve that
use-case already.

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. It seems that REASSIGN OWNED doesn’t solve this already.

I remain of the opinion that this is a terrible idea.

regards, tom lane

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

--
May the force be with you…
https://simply.name

On Mon, Feb 15, 2016 at 7:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Teodor Sigaev <teodor@sigaev.ru> writes:

So basically, a generic CASCADE facility sounds like a lot of work to
produce something that would seldom be anything but a foot-gun.

DELETE FROM or TRUNCATE could be a foot-gun too, but it's not a reason

to

remove tham. I faced with problem when I tried to change owner of

datadase with

all objects inside. Think, this feature could be useful although it

should

restricted to superuser obly.

That's a pretty weak argument, and I do not think you have thought through
all the consequences. It is not hard at all to imagine cases where using
this sort of thing could be a security vulnerability. Are you familiar
with the reasons why Unix systems don't typically allow users to "give
away" ownership of files? The same problem exists here.

yes, I remember AT&T and BSD :)

To be concrete about it:

1. Alice does, say, "CREATE EXTENSION cube".

2. Bob creates a security-definer function owned by himself, using a
"cube"-type parameter so that it's dependent on the extension.
(It needn't actually do anything with that parameter.)

3. Alice does ALTER EXTENSION cube OWNER TO charlie CASCADE.

4. Bob now has a security-definer function owned by (and therefore
executing as) Charlie, whose contents were determined by Bob.
Game over for Charlie ... and for everyone else too, if Charlie is
a superuser, which is not unlikely for an extension owner.

The only way Alice can be sure that the ALTER EXTENSION is safe is if
she manually inspects every dependent object, in which case she might
as well not use CASCADE.

Moreover, the use case you've sketched (ie, change ownership of all
objects inside a database) doesn't actually have anything to do with
following dependencies. It's a lot closer to REASSIGN OWNED ... in
fact, it's not clear to me why REASSIGN OWNED doesn't solve that
use-case already.

I remain of the opinion that this is a terrible idea.

+1, I also suggest to check REASSIGN OWNED.

regards, tom lane

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

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

Vladimir Borodin wrote:

Moreover, the use case you've sketched (ie, change ownership of all
objects inside a database) doesn't actually have anything to do with
following dependencies. It's a lot closer to REASSIGN OWNED ... in
fact, it's not clear to me why REASSIGN OWNED doesn't solve that
use-case already.

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. It seems
that REASSIGN OWNED doesn’t solve this already.

So essentially you want to change all the objects in the database except
those that were created together with the database itself (i.e. those
that were copied from the template database). That seems a reasonable
use-case, but I'm not sure that this ALTER .. OWNER CASCADE is the right
thing for that -- What object would you start with? Each schema other
than pg_catalog, pg_toast, information_schema? As I recall, the problem
is that REASSIGN OWNED refuses to work on pinned objects. Maybe what
you want is something like
REASSIGN OWNED BY xyz IN SCHEMA public TO xyzxxz
i.e., an extension of the current REASSIGN OWNED BY command?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

16 февр. 2016 г., в 18:20, Alvaro Herrera <alvherre@2ndquadrant.com> написал(а):

Vladimir Borodin wrote:

Moreover, the use case you've sketched (ie, change ownership of all
objects inside a database) doesn't actually have anything to do with
following dependencies. It's a lot closer to REASSIGN OWNED ... in
fact, it's not clear to me why REASSIGN OWNED doesn't solve that
use-case already.

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. It seems
that REASSIGN OWNED doesn’t solve this already.

So essentially you want to change all the objects in the database except
those that were created together with the database itself (i.e. those
that were copied from the template database).

Yes. Without such syntax it is now done in a really awful way now, i.e. [0]https://github.com/saltstack/salt/blob/405d0aef1cf11bb56b5d2320b176f6992e6cdf3b/salt/modules/postgres.py#L1806-L1847.

[0]: https://github.com/saltstack/salt/blob/405d0aef1cf11bb56b5d2320b176f6992e6cdf3b/salt/modules/postgres.py#L1806-L1847

That seems a reasonable
use-case, but I'm not sure that this ALTER .. OWNER CASCADE is the right
thing for that -- What object would you start with? Each schema other
than pg_catalog, pg_toast, information_schema? As I recall, the problem
is that REASSIGN OWNED refuses to work on pinned objects. Maybe what
you want is something like
REASSIGN OWNED BY xyz IN SCHEMA public TO xyzxxz
i.e., an extension of the current REASSIGN OWNED BY command?

Well, I don’t know what syntax and implementation would be correct. I just want to give a specific user all rights to manage all objects in a specific database (which was created from postgres user earlier). It would be really useful.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Да пребудет с вами сила…
https://simply.name/ru