I notice you can use most of the RENAME TO commands in postgres to 
rename system objects.  Renaming a system table is disallowed:

test=# alter table pg_namespace rename to blah;
ERROR:  permission denied: "pg_namespace" is a system catalog

But mucking with any other system object is not:

test=# alter function abbrev(inet) rename to bad;
ALTER FUNCTION

Is there logic in that?

Also, what about when I implement ALTER OWNER for everything?  Should 
that be banned on system objects?

Chris


Sat, 12 Jun 2004 18:51:02 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Bug in RENAME TO?
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I notice you can use most of the RENAME TO commands in postgres to 
> rename system objects.  Renaming a system table is disallowed:

> test=# alter table pg_namespace rename to blah;
> ERROR:  permission denied: "pg_namespace" is a system catalog

> But mucking with any other system object is not:

> test=# alter function abbrev(inet) rename to bad;
> ALTER FUNCTION

> Is there logic in that?

Yes: superusers should be allowed to shoot themselves in the foot
whenever possible.

We *know* the code will stop working if you rename a system table or
index, because those things are referred to *by name* in the C code.
So that prohibition seems reasonable.  On the other end of the scale,
there is no reason whatever to prohibit someone from mucking with the
inet datatype to their heart's content.  It's only builtin as an
artifact of our packaging, not because anything depends on it.

It's likely that there are some pg_proc entries that you can break the
system beyond repair by renaming, but I'd expect they are a relatively
small minority (the system's hardwired references are by OID not name).
We shouldn't put a blanket prohibition on it to prevent problems we
aren't even certain exist.

As an example of why superusers should have as few restrictions as
possible, I refer you to the 7.4.2 release notes:
http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-2
Without the ability for superusers to muck with the system catalogs,
we'd have had no choice but to force initdb for 7.4.2 to fix those
errors.

			regards, tom lane

Sat, 12 Jun 2004 12:58:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in RENAME TO? 
I said:
> It's likely that there are some pg_proc entries that you can break the
> system beyond repair by renaming, but I'd expect they are a relatively
> small minority (the system's hardwired references are by OID not name).

Just for fun I tried

d=# update pg_proc set proname = 'z' || proname;
UPDATE 1727

The database got a bit unpleasant to use, mainly because most of
psql's \d commands broke:

d=# \d foo
ERROR:  function pg_catalog.pg_table_is_visible(oid) does not exist

but standard SQL operations still worked, and in fact I was able to
undo the damage with

d=# update pg_proc set proname = zsubstring(proname,2);
UPDATE 1727

So I say there isn't any reason to prohibit renaming functions just
because they were created at initdb time.  The worst-case scenario
is you have to rename 'em back.  Likewise for ALTER OWNER.

			regards, tom lane

Sat, 12 Jun 2004 14:02:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug in RENAME TO? 
> As an example of why superusers should have as few restrictions as
> possible, I refer you to the 7.4.2 release notes:
> http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-2
> Without the ability for superusers to muck with the system catalogs,
> we'd have had no choice but to force initdb for 7.4.2 to fix those
> errors.

My point being that the 7.4 release stuff is done via the catupd flag, 
not via standard SQL commands.  Seems to me that you should be protected 
from shooting yourself in the foot so long as you work through the 
standard SQL commands...surely?

The other thing of course is that any renames or owner changes they make 
to system objects will not be propogated at pg_dump time.

Chris


Sun, 13 Jun 2004 11:07:02 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Bug in RENAME TO?
> d=# update pg_proc set proname = zsubstring(proname,2);
> UPDATE 1727
> 
> So I say there isn't any reason to prohibit renaming functions just
> because they were created at initdb time.  The worst-case scenario
> is you have to rename 'em back.  Likewise for ALTER OWNER.

Again, no reason to stop them doing it via catalog hacking, but really 
good reasons to stop them doing it via SQL commands....

Chris


Sun, 13 Jun 2004 11:08:56 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Bug in RENAME TO?