Thread: superuser unable to modify settings of a system table
On PG 8.4.4 I am unable to set per-table autovacuum setting for the pg_listener table. Being a superuser, I'd expect Postgres to allow me to do that. postgres=# select version(); version ----------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20081105 (Red Hat 4.3.2-7), 32-bit (1 row) postgres=# select user; current_user -------------- postgres (1 row) postgres=# alter table pg_listener set (autovacuum_vacuum_threshold=100); ERROR: permission denied: "pg_listener" is a system catalog postgres=# select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+----------+----------+----------- postgres | 10 | t | t | t | ******** | | (1 row) Regards, PS: RhodiumToad prompted me to post here. -- gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.EnterpriseDB.com singh.gurjeet@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
Gurjeet Singh <singh.gurjeet@gmail.com> writes:
> On PG 8.4.4 I am unable to set per-table autovacuum setting for the
> pg_listener table. Being a superuser, I'd expect Postgres to allow me to do
> that.
See allow_system_table_mods. Even superusers should think twice before
fooling with system catalogs...
regards, tom lane
On Thu, Jun 3, 2010 at 1:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Gurjeet Singh <singh.gurjeet@gmail.com> writes: > > On PG 8.4.4 I am unable to set per-table autovacuum setting for the > > pg_listener table. Being a superuser, I'd expect Postgres to allow me to > do > > that. > > See allow_system_table_mods. Even superusers should think twice before > fooling with system catalogs... > > :) Well, I assume a lot of thought does go into the process of altering the system table, but when it is needed, it is really needed. In the setup that I am handling right now, LISTEN/NOTIFY gets used a lot, and allowing autovacuum to manage it with default settings is really counter productive. allow_system_table_mods needs a restart :( .Yet another parameter I wish was changeable on the fly. Regards, -- gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.EnterpriseDB.com singh.gurjeet@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
Gurjeet Singh <singh.gurjeet@gmail.com> writes:
> allow_system_table_mods needs a restart :( .Yet another parameter I wish was
> changeable on the fly.
I'm not sure there's any compelling reason why it couldn't be SUSET.
Maybe a TODO ...
regards, tom lane
On Thu, Jun 3, 2010 at 1:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Gurjeet Singh <singh.gurjeet@gmail.com> writes: >> allow_system_table_mods needs a restart :( .Yet another parameter I wish was >> changeable on the fly. > > I'm not sure there's any compelling reason why it couldn't be SUSET. > Maybe a TODO ... Personally, I think it would be better to put some work into making allow_system_table_mods a little less simple-minded. Right now, !allow_system_table_mods prohibits you from doing perfectly sensible things (as in the OP's original example) yet still allows you to do things that are totally nuts (like DELETE FROM pg_class, which causes every subsequent connection attempt for that database to panic). Perfection may be too much to ask for but I'd take "modest improvement"... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes:
> Personally, I think it would be better to put some work into making
> allow_system_table_mods a little less simple-minded. Right now,
> !allow_system_table_mods prohibits you from doing perfectly sensible
> things (as in the OP's original example) yet still allows you to do
> things that are totally nuts (like DELETE FROM pg_class, which causes
> every subsequent connection attempt for that database to panic).
> Perfection may be too much to ask for but I'd take "modest
> improvement"...
Nope, that is the wrong viewpoint entirely. allow_system_table_mods
is intended to prevent you from modifying the *structure* of the
system catalogs, which is fairly critical because the backend C code
tends to depend on that. Modifying the *content* of the catalogs
is another matter, and in fact we let any superuser do that without
having set allow_system_table_mods. There is no practical way to
distinguish a benign catalog-content change from a disastrous one,
so we don't try.
It's possible that reloptions is a special case and we should treat it
as being more nearly in the content than structure category. Not sure.
regards, tom lane
On Fri, Jun 4, 2010 at 4:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> Personally, I think it would be better to put some work into making >> allow_system_table_mods a little less simple-minded. =A0Right now, >> !allow_system_table_mods prohibits you from doing perfectly sensible >> things (as in the OP's original example) yet still allows you to do >> things that are totally nuts (like DELETE FROM pg_class, which causes >> every subsequent connection attempt for that database to panic). >> Perfection may be too much to ask for but I'd take "modest >> improvement"... > > Nope, that is the wrong viewpoint entirely. =A0allow_system_table_mods > is intended to prevent you from modifying the *structure* of the > system catalogs, which is fairly critical because the backend C code > tends to depend on that. =A0Modifying the *content* of the catalogs > is another matter, and in fact we let any superuser do that without > having set allow_system_table_mods. =A0There is no practical way to > distinguish a benign catalog-content change from a disastrous one, > so we don't try. > > It's possible that reloptions is a special case and we should treat it > as being more nearly in the content than structure category. =A0Not sure. The backend C code also depends on the critical system indexes being present in the system catalogs, yet we still allow them to be deleted. Is there really a use case for users fiddling with pg_proc, pg_class, etc. directly? At any rate, I'd be happy to drop that part of the proposal. It would be a step forward just to permit (even without allow_system_table_mods) those changes which don't alter the structure of the catalog. For ALTER TABLE, the SET STATISTICS, (RE)SET (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and (RE)SET (reloptions) forms are all things that fall into this category, I believe. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes:
> Is there really a use case for users fiddling with pg_proc, pg_class,
> etc. directly?
There's a use case for *superusers* to fiddle with them, yes.
(Superusers are presumed to be adults.) I think I recommend a quick
UPDATE on some catalog at least once a month on the lists.
You might care to consider the fact that no modern Unix system prevents
root from doing rm -rf /, even though that's "obviously" disastrous.
Yet (stretching the analogy all out of shape) there's no convenient user
tool for rearranging the contents of all the inodes on a filesystem.
> At any rate, I'd be happy to drop that part of the proposal. It would
> be a step forward just to permit (even without
> allow_system_table_mods) those changes which don't alter the structure
> of the catalog. For ALTER TABLE, the SET STATISTICS, (RE)SET
> (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and
> (RE)SET (reloptions) forms are all things that fall into this
> category, I believe.
It would be far less work to just drop allow_system_table_mods to SUSET.
And we wouldn't get questions about which forms of ALTER TABLE require
it.
regards, tom lane
On Fri, Jun 4, 2010 at 5:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> =A0Is there really a use case for users fiddling with pg_proc, pg_class, >> etc. directly? > > There's a use case for *superusers* to fiddle with them, yes. > (Superusers are presumed to be adults.) =A0I think I recommend a quick > UPDATE on some catalog at least once a month on the lists. > > You might care to consider the fact that no modern Unix system prevents > root from doing rm -rf /, even though that's "obviously" disastrous. > Yet (stretching the analogy all out of shape) there's no convenient user > tool for rearranging the contents of all the inodes on a filesystem. Sure. I guess it boils down to how much use case you think there is for updating system catalogs directly (rather than using DDL). I don't follow all the lists so I haven't seen these recommendations. >> At any rate, I'd be happy to drop that part of the proposal. =A0It would >> be a step forward just to permit (even without >> allow_system_table_mods) those changes which don't alter the structure >> of the catalog. =A0For ALTER TABLE, the SET STATISTICS, (RE)SET >> (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and >> (RE)SET (reloptions) forms are all things that fall into this >> category, I believe. > > It would be far less work to just drop allow_system_table_mods to SUSET. > And we wouldn't get questions about which forms of ALTER TABLE require > it. I think there's some value in distinguishing between things which are "only for adults" and things which are "almost certainly a bad idea". --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > Is there really a use case for users fiddling with pg_proc, pg_class, > > etc. directly? > > There's a use case for *superusers* to fiddle with them, yes. > (Superusers are presumed to be adults.) I think I recommend a quick > UPDATE on some catalog at least once a month on the lists. > > You might care to consider the fact that no modern Unix system prevents > root from doing rm -rf /, even though that's "obviously" disastrous. > Yet (stretching the analogy all out of shape) there's no convenient user > tool for rearranging the contents of all the inodes on a filesystem. > > > At any rate, I'd be happy to drop that part of the proposal. It would > > be a step forward just to permit (even without > > allow_system_table_mods) those changes which don't alter the structure > > of the catalog. For ALTER TABLE, the SET STATISTICS, (RE)SET > > (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and > > (RE)SET (reloptions) forms are all things that fall into this > > category, I believe. > > It would be far less work to just drop allow_system_table_mods to SUSET. > And we wouldn't get questions about which forms of ALTER TABLE require > it. Are we going to make the allow_system_table_mods to SUSET change? Is it a TODO? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Feb 4, 2011 at 10:45 PM, Bruce Momjian <bruce@momjian.us> wrote: > Tom Lane wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >> > =A0Is there really a use case for users fiddling with pg_proc, pg_clas= s, >> > etc. directly? >> >> There's a use case for *superusers* to fiddle with them, yes. >> (Superusers are presumed to be adults.) =A0I think I recommend a quick >> UPDATE on some catalog at least once a month on the lists. >> >> You might care to consider the fact that no modern Unix system prevents >> root from doing rm -rf /, even though that's "obviously" disastrous. >> Yet (stretching the analogy all out of shape) there's no convenient user >> tool for rearranging the contents of all the inodes on a filesystem. >> >> > At any rate, I'd be happy to drop that part of the proposal. =A0It wou= ld >> > be a step forward just to permit (even without >> > allow_system_table_mods) those changes which don't alter the structure >> > of the catalog. =A0For ALTER TABLE, the SET STATISTICS, (RE)SET >> > (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and >> > (RE)SET (reloptions) forms are all things that fall into this >> > category, I believe. >> >> It would be far less work to just drop allow_system_table_mods to SUSET. >> And we wouldn't get questions about which forms of ALTER TABLE require >> it. > > Are we going to make the allow_system_table_mods to SUSET change? =A0Is it > a TODO? I'd rather not drop it to SUSET. If we're going to make an effort in this area, I'd rather do the work to distinguish the destroy-your-database cases from the ones that are reasonable for an admin to want to do. I wouldn't make it a TODO, though. We have no consensus on what, if anything, is worth doing. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company