Thread: Commands to change name, schema, owner
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
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
> 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
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
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
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