Обсуждение: DELETE FROM pg_class

Hello, I see that I can modify system tables even though I have
not set allow_system_table_mods...  Is this a feature or a bug?

Self contained code

postgres=# SELECT version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3
20070718 (prerelease) (Debian 4.1.2-14)
(1 row)

postgres=# SHOW allow_system_table_mods;
 allow_system_table_mods
-------------------------
 off
(1 row)

postgres=# CREATE DATABASE foo;
CREATE DATABASE
postgres=# \c foo
You are now connected to database "foo".
foo=# DELETE FROM pg_class;
DELETE 204
foo=# SELECT count(*) FROM pg_class;
ERROR:  could not find pg_class tuple for index 2662
foo=# \c postgres
You are now connected to database "postgres".
postgres=# \c foo
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
Previous connection kept
postgres=#


   Regards,
     Dawid

On Mon, 2007-09-24 at 14:44 +0200, Dawid Kuroczko wrote:

> Hello, I see that I can modify system tables even though I have
> not set allow_system_table_mods...  Is this a feature or a bug?

allow_system_table_mods allows you to modify the structure, not just the
data, i.e. add additional columns to system tables.

Superusers have the capability to modify data in catalog tables and many
other things besides, normal users don't.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 2007-09-24 at 14:44 +0200, Dawid Kuroczko wrote:
>> Hello, I see that I can modify system tables even though I have
>> not set allow_system_table_mods...  Is this a feature or a bug?

> allow_system_table_mods allows you to modify the structure, not just the
> data, i.e. add additional columns to system tables.

> Superusers have the capability to modify data in catalog tables and many
> other things besides, normal users don't.

It is possible to disable this by turning off your
pg_authid.rolcatupdate flag, but AFAIR there is no handy support for
that (eg, no separate ALTER ROLE option).

The better advice though is "don't run as superuser except when you
absolutely must".  You don't do random work as root, do you?

            regards, tom lane

On 9/24/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Mon, 2007-09-24 at 14:44 +0200, Dawid Kuroczko wrote:
> >> Hello, I see that I can modify system tables even though I have
> >> not set allow_system_table_mods...  Is this a feature or a bug?
>
> > allow_system_table_mods allows you to modify the structure, not just the
> > data, i.e. add additional columns to system tables.
>
> > Superusers have the capability to modify data in catalog tables and many
> > other things besides, normal users don't.
>
> It is possible to disable this by turning off your
> pg_authid.rolcatupdate flag, but AFAIR there is no handy support for
> that (eg, no separate ALTER ROLE option).
>
> The better advice though is "don't run as superuser except when you
> absolutely must".  You don't do random work as root, do you?

Nah, actually a friend (user of the other open source RDBMS) asked
me if you can overload PostgreSQL builtins (like new()).  And it was quite
simple.  I thought though, that I need allow_system_table_mods for it
and it surprised me that I just needed to become superuser...

Somehow, when I read documentation, my internal parser omitted
the "of the structure" of the "Allows modification of the structure of
system tables." sentence.  I feel a bit foolish for asking this question,
but now I am a bit wiser.

   Regards,
       Dawid