Обсуждение: UPDATE pg_catalog.pg_class as NO Superuser??

Dear list,

on my system I have multiple user.

I want that the user xy (who is no superuser) can Update a systemtable with:

UPDATE pg_catalog.pg_class SET reltriggers = 0;

With psql I already wrote:

ALTER TABLE pg_catalog.pg_class OWNER TO xy;

and

GRANT ALL PRIVILEGES ON pg_catalog.pg_class TO xy;

I check it with \dS
and see:

                     List of relations
   Schema   |           Name           | Type  |   Owner
------------+--------------------------+-------+-----------
 pg_catalog | pg_aggregate             | table | postgres
 pg_catalog | pg_am                    | table | postgres
 pg_catalog | pg_amop                  | table | postgres
 pg_catalog | pg_amproc                | table | postgres
 pg_catalog | pg_attrdef               | table | postgres
 pg_catalog | pg_attribute             | table | postgres
 pg_catalog | pg_auth_members          | table | postgres
 pg_catalog | pg_authid                | table | postgres
 pg_catalog | pg_autovacuum            | table | postgres
 pg_catalog | pg_cast                  | table | postgres
 pg_catalog | pg_class                 | table | xy


But always if I sent as user xy the
"UPDATE pg_catalog.pg_class SET reltriggers = 0;"-command I get:

"SQL error:

ERROR:  permission denied for relation pg_class"

Why is that? MUST I be a superuser for that?
Is there any way to set that command as user xy?

Thank you very much in advance, Kai





--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

am  Thu, dem 13.09.2007, um 11:25:39 +0200 mailte Kai Behncke folgendes:
> But always if I sent as user xy the
> "UPDATE pg_catalog.pg_class SET reltriggers = 0;"-command I get:
>
> "SQL error:
>
> ERROR:  permission denied for relation pg_class"
>
> Why is that? MUST I be a superuser for that?


Write a function with SECURITY DEFINER for that.

*untested*


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Thu, Sep 13, 2007 at 11:25:39AM +0200, Kai Behncke wrote:
> I want that the user xy (who is no superuser) can Update a systemtable with:
> UPDATE pg_catalog.pg_class SET reltriggers = 0;

why dont you simply alter table disable trigger?

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Hidepesz,
-------- Original-Nachricht --------
> Datum: Thu, 13 Sep 2007 12:25:51 +0200
> Von: hubert depesz lubaczewski <depesz@depesz.com>
> An: Kai Behncke <Kai-Behncke@gmx.de>
> CC: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??

> On Thu, Sep 13, 2007 at 11:25:39AM +0200, Kai Behncke wrote:
> > I want that the user xy (who is no superuser) can Update a systemtable
> with:
> > UPDATE pg_catalog.pg_class SET reltriggers = 0;
>
> why dont you simply alter table disable trigger?
>
> depesz
>
Could you give me an example for that please?
Thank you very much :-), Kai
--
GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS.
Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail

On Thu, Sep 13, 2007 at 01:06:11PM +0200, Kai Behncke wrote:
> Could you give me an example for that please?
> Thank you very much :-), Kai

i think i gave.
ok. again:

alter table some_table disable trigger all;

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

am  Thu, dem 13.09.2007, um 13:06:11 +0200 mailte Kai Behncke folgendes:
> > why dont you simply alter table disable trigger?
> >
> > depesz
> >
> Could you give me an example for that please?
> Thank you very much :-), Kai

Open psql and type: \h alter table


test=*# \h alter table
Command:     ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ ONLY ] name [ * ]
....



Okay:

ALTER TABLE foo DISABLE TRIGGER ALL;


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net