Обсуждение: Cannot create a type in pg_catalog

Should this work, or at least give a more appropriate error message?

demo=# SELECT version();
                                         version

------------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)
(1 row)

demo=# SELECT rolname, rolsuper, rolcatupdate FROM pg_authid;
 rolname  | rolsuper | rolcatupdate
----------+----------+--------------
 postgres | t        | t
(1 row)

demo=# CREATE TYPE pg_catalog.mytype AS (data text);
ERROR:  permission denied to create "pg_catalog.mytype"
DETAIL:  System catalog modifications are currently disallowed.

I can successfully create functions in pg_catalog, just not types or
relations.

Regards, Dave.

Dave Page <dpage@postgresql.org> writes:
> Should this work, or at least give a more appropriate error message?
> demo=# CREATE TYPE pg_catalog.mytype AS (data text);
> ERROR:  permission denied to create "pg_catalog.mytype"
> DETAIL:  System catalog modifications are currently disallowed.

Seems like a perfectly appropriate error message to me: it's disallowed.

            regards, tom lane

Tom Lane wrote:
> Dave Page <dpage@postgresql.org> writes:
>> Should this work, or at least give a more appropriate error message?
>> demo=# CREATE TYPE pg_catalog.mytype AS (data text);
>> ERROR:  permission denied to create "pg_catalog.mytype"
>> DETAIL:  System catalog modifications are currently disallowed.
>
> Seems like a perfectly appropriate error message to me: it's disallowed.

I see three issues:

- it's only partially disallowed - why can I create a function, but not
a type? Shouldn't both be either allowed or disallowed?


- The description in the docs of pg_authid.rolcatupdate reads:

Role may update system catalogs directly. (Even a superuser may not do
this unless this column is true)

However, even with it set to true, I cannot do so. Of course, this
depends on whether you define 'system catalogs' as the tables in
pg_catalog, or the pg_catalog schema itself. Either way, something's a
little wonky.


- "are currently disallowed" implies that sometimes it is allowed. How
does one control that, or is it implying something that is not the case,
or only the case in unusual circumstances such as a standalone backend?


Note that I'm making no comment on whether or not I should be able to do
 what I'm suggesting (thought preventing it will break adminpack) - just
that we have some inconsistencies at the very least.

Regards, Dave

Dave Page wrote:

> - "are currently disallowed" implies that sometimes it is allowed. How
> does one control that, or is it implying something that is not the case,
> or only the case in unusual circumstances such as a standalone backend?

It is allowed in standalone mode AFAIR (maybe when specifying a flag
like -P or -O).

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"No renuncies a nada. No te aferres a nada."

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Dave Page wrote:
>> - "are currently disallowed" implies that sometimes it is allowed. How
>> does one control that, or is it implying something that is not the case,
>> or only the case in unusual circumstances such as a standalone backend?

> It is allowed in standalone mode AFAIR (maybe when specifying a flag
> like -P or -O).

Yeah, you need one of those switches, I can never remember which is
which without looking.

The actual prohibition is on inserting a new pg_class row, which Dave's
command is trying to do because he's trying to create a composite type.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Dave Page wrote:
>>> - "are currently disallowed" implies that sometimes it is allowed. How
>>> does one control that, or is it implying something that is not the case,
>>> or only the case in unusual circumstances such as a standalone backend?
>
>> It is allowed in standalone mode AFAIR (maybe when specifying a flag
>> like -P or -O).
>
> Yeah, you need one of those switches, I can never remember which is
> which without looking.
>
> The actual prohibition is on inserting a new pg_class row, which Dave's
> command is trying to do because he's trying to create a composite type.

If thats the case, then there's another inconsistency as I can insert a
pg_class row manually without any problems:

demo=# INSERT INTO pg_class (relname, relnamespace, reltype, relowner,
relam, relfilenode, reltablespace, relpages, reltuples, reltoastrelid,
reltoastidxid, relhasindex, relisshared, relkind, relnatts, relchecks,
reltriggers, relukeys, relfkeys, relrefs, relhasoids, relhaspkey,
relhasrules, relhassubclass, relfrozenxid) VALUES ('x', 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, FALSE, FALSE, 't', 0, 0, 0, 0, 0, 0, FALSE, FALSE, FALSE,
FALSE, '0');
INSERT 17943 1


Regards, Dave

Dave Page <dpage@postgresql.org> writes:
> Tom Lane wrote:
>> The actual prohibition is on inserting a new pg_class row, which Dave's
>> command is trying to do because he's trying to create a composite type.

> If thats the case, then there's another inconsistency as I can insert a
> pg_class row manually without any problems:

Right, that's the end of it that's protected by rolcatupdate.  The
check that is firing on you is in heap_create(), which is invoked
for composite types.

The whole allow_system_table_mods mechanism dates from Berkeley days,
and I guess you could argue that we might not need it anymore given
the improvements since then in ACL enforcement.  I'm not in a big
hurry to rip it out though.  Not being allowed to TRUNCATE pg_class
seems like a Good Thing to me.

            regards, tom lane

Tom Lane wrote:
> Dave Page <dpage@postgresql.org> writes:
>> Tom Lane wrote:
>>> The actual prohibition is on inserting a new pg_class row, which Dave's
>>> command is trying to do because he's trying to create a composite type.
>
>> If thats the case, then there's another inconsistency as I can insert a
>> pg_class row manually without any problems:
>
> Right, that's the end of it that's protected by rolcatupdate.  The
> check that is firing on you is in heap_create(), which is invoked
> for composite types.
>
> The whole allow_system_table_mods mechanism dates from Berkeley days,
> and I guess you could argue that we might not need it anymore given
> the improvements since then in ACL enforcement.  I'm not in a big
> hurry to rip it out though.  Not being allowed to TRUNCATE pg_class
> seems like a Good Thing to me.

Well, yeah, that does seem like a good foot-guard. Perhaps if/when we
get a separate truncate privilege would be a more appropriate time to
look at cleaning it up.

Regards, Dave.