We only have sporadic support to rename objects, change the owner of
objects, and no support to change the schema of an object.  So how about a
big bang to add support for these three operations for every object where
it is applicable?  I hope to do it without a separate parse structure and
routine for each kind of object and operation, so it can easily be
extended.

Are there any tricky problems with any of these operations?  For example,
what happens when an object you have used, say, in a view gets moved to a
schema that you don't have access to.  Bad luck?


Renaming is possible for: aggregate, constraint, conversion, database,
domain, function, group, index, language, operator, opclass, rule, schema,
sequence, table, trigger, type, user, view.

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.


Changing the owner is possible for: aggregate, conversion, database,
domain, function, operator, opclass, schema, sequence, table, type, view.

The command is: ALTER THING name AUTHORIZATION username;  (This is
consistent with the CREATE SCHEMA syntax.  Anyone like OWNER better?)

Requires being superuser.


Changing the schema is possible for: aggregate, conversion, domain,
function, operator, opclass, table, type, view.

The command is: ALTER THING name SCHEMA newschema;

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

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 20 Jun 2003 20:00:14 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Commands to change name, schema, owner
Peter Eisentraut <peter_e@gmx.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

Fri, 20 Jun 2003 20:24:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Commands to change name, schema, owner 
> The command is: ALTER THING name AUTHORIZATION username;  (This is
> consistent with the CREATE SCHEMA syntax.  Anyone like OWNER better?)
k
WHy not copy the exiting ALTER TABLE / OWNER TO syntax?

Chris



Sat, 21 Jun 2003 13:10:36 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Commands to change name, schema, owner
Tom Lane writes:

> 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.

Right.  This is mostly solved, so not really "tricky". ;-)

> 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?

Right, there's a global variable that stores the database name, but that
will have to disappear.  You'll have to look it up in the catalog like
everything else.

> Same goes for renaming a user who has active backends.

That was cleaned up years ago, otherwise the session user/current user
stack would not work.

> (Even if you can fix the instances within the backend, what about
> connected clients, for instance libpq's private state?

That is a good point, but I'm not sure if it's really a problem.  Perhaps
in the first cut, active the active database and the session user may not
be renamed.  Later we can reconsider relaxing that.

> And what if the rename means these clients should not have been allowed
> to connect, per pg_hba.conf?)

I'm not worried about that.  This is much the same as changing your
password: you should not have been allowed to connect because the password
you had entered isn't right anymore.

> Renaming operators would possibly change their precedence, which I don't
> *think* would break rule dumps, but it's something to consider.

I will check that.

> 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.

It sure does, but we already allow renaming of sequences, so this won't be
anything new.

> 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.

That will not be a problem, because the user stack is adjusted once before
each call, so the visible owner at the time of the call gets applied.
(What happens inside the function is, of course, anyone's guess.)

> > 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?

For those objects who don't live within schemas, you only need to own the
object or the containing table, respectively.  No privilege on any schema
is required.  In detail:

constraint -> owner of table
database -> owner of database
rule -> owner of table
schema -> owner of schema
trigger -> owner of table

Some of these call for more involved syntax:

ALTER TABLE name RENAME CONSTRAINT old TO new;
ALTER RULE oldname ON table RENAME TO newname;
ALTER TRIGGER oldname ON table RENAME TO newname;

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 21 Jun 2003 14:50:32 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Commands to change name, schema, owner 
Peter Eisentraut <peter_e@gmx.net> writes:
> Right, there's a global variable that stores the database name, but that
> will have to disappear.  You'll have to look it up in the catalog like
> everything else.

That answer is okay as long as we don't need to get at the value while
outside any transaction (or in a failed transaction).  Offhand I can't
think of a reason we'd need to, but you'll need to look closely at the
uses of that variable.

[ other answers look fine ]

> For those objects who don't live within schemas, you only need to own the
> object or the containing table, respectively.  No privilege on any schema
> is required.  In detail:

> constraint -> owner of table
> database -> owner of database
> rule -> owner of table
> schema -> owner of schema
> trigger -> owner of table

It could be argued that renaming a database should require CREATEDB
rights, and that renaming a schema should require create-schema rights
at the database level.  For example, if user joe is given a schema joe,
which he proceeds to rename to bob, it's not a lot different from him
having been able to make a schema bob in the first place.  ISTM if the
DBA had disallowed create-schema rights to joe, he'd see this as an
end run around that prohibition.

The constraint/rule/trigger cases look fine, since names of those
entities aren't super interesting anyway.

			regards, tom lane

Sat, 21 Jun 2003 11:27:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Commands to change name, schema, owner 
Christopher Kings-Lynne writes:

> > The command is: ALTER THING name AUTHORIZATION username;  (This is
> > consistent with the CREATE SCHEMA syntax.  Anyone like OWNER better?)
> k
> WHy not copy the exiting ALTER TABLE / OWNER TO syntax?

Because the standard specifies the syntax CREATE SCHEMA name AUTHORIZATION
username.  I'd rather generalize a standard syntax.  But we can have both
if people prefer that.

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 21 Jun 2003 21:15:48 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Commands to change name, schema, owner