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 todo
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. 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.
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.
--
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:
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.
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. 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.
I think there's some value in distinguishing between things which are
"only for adults" and things which are "almost certainly a bad idea".
--
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:
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?
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.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company