Thread: 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