A modest proposal: get rid of GUC's USERLIMIT variable category
I'd like to propose that we get rid of GUC's USERLIMIT category and
convert all the variables in it to plain SUSET. In my mind, USERLIMIT
is a failed experiment: it's way too complicated, and it still doesn't
do quite what it was intended to do, because there are times when it
can't check whether you're a superuser.
The only variables that are in the category are log-verbosity-related:
regression=# select name from pg_settings where context = 'userlimit';
name
----------------------------
log_duration
log_executor_stats
log_min_duration_statement
log_min_error_statement
log_min_messages
log_parser_stats
log_planner_stats
log_statement
log_statement_stats
(9 rows)
What the USERLIMIT code tries to do is allow non-superusers to
"increase" but not "decrease" the logging verbosity for their sessions
only. (For instance, a non-superuser could turn log_duration on, but
can't turn it off if the DBA has turned it on.) However, the usefulness
of this capability is really pretty debatable. A non-superuser
presumably doesn't have access to the postmaster log file anyhow, so why
does he need to be able to turn up the logging? You could even argue
that being able to flood the logs with stuff the DBA doesn't want is a
mild form of DOS attack.
If we do get rid of USERLIMIT, another benefit accrues: we can assume
that ALTER USER and ALTER DATABASE settings were fully checked when they
were installed, and thereby accept them at session start without any
extra permissions check. This would mean that, for example, a superuser
could use ALTER USER to set these variables on a per-user basis for
non-superusers, and it would actually work. Right now the value is
rechecked as if the setting were being issued by the non-superuser,
and so it may fail.
For more discussion see this thread in pgsql-bugs:
http://archives.postgresql.org/pgsql-bugs/2004-11/msg00101.php
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
I'd like to propose that we get rid of GUC's USERLIMIT category and
convert all the variables in it to plain SUSET. In my mind, USERLIMIT
is a failed experiment: it's way too complicated, and it still doesn't
do quite what it was intended to do, because there are times when it
can't check whether you're a superuser.The only variables that are in the category are log-verbosity-related:
Would that mean I wouldn't be able to change the logging level on the fly at
all?
That would disappoint at least one user, myself. I've found the best debugging
compromise is to leave log_statement off in general but have a magic parameter
I can pass to the application that will set log_statement = true for a single
transaction.
That way I can look at what queries transpired in my session without having to
dig through hundreds of other queries from other sessions. And have the
complete logs for the session I'm debugging without the performance impact in
the normal case.
--
greg
Greg Stark wrote:
Tom Lane <tgl@sss.pgh.pa.us> writes:
I'd like to propose that we get rid of GUC's USERLIMIT category and
convert all the variables in it to plain SUSET. In my mind, USERLIMIT
is a failed experiment: it's way too complicated, and it still doesn't
do quite what it was intended to do, because there are times when it
can't check whether you're a superuser.The only variables that are in the category are log-verbosity-related:
Would that mean I wouldn't be able to change the logging level on the fly at
all?That would disappoint at least one user, myself. I've found the best debugging
compromise is to leave log_statement off in general but have a magic parameter
I can pass to the application that will set log_statement = true for a single
transaction.That way I can look at what queries transpired in my session without having to
dig through hundreds of other queries from other sessions. And have the
complete logs for the session I'm debugging without the performance impact in
the normal case.
Yes, this would not be possible for non-super users with the new
proposal. You could set the setting for non-super users per-user but
not per-session.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 2004-11-09 at 17:27 -0500, Tom Lane wrote:
What the USERLIMIT code tries to do is allow non-superusers to
"increase" but not "decrease" the logging verbosity for their sessions
only. (For instance, a non-superuser could turn log_duration on, but
can't turn it off if the DBA has turned it on.) However, the usefulness
of this capability is really pretty debatable. A non-superuser
presumably doesn't have access to the postmaster log file anyhow, so why
does he need to be able to turn up the logging? You could even argue
that being able to flood the logs with stuff the DBA doesn't want is a
mild form of DOS attack.
Hi Tom,
While it may be true that a user can't see those logs, in a narrow
sense, it is not true in a wider sense. How many systems/accounts are
you currently logged into yourself?
The set of "people who control SQL queries" does have a large overlap
with other sets like "people who write and debug software" and "people
who have access to system logs".
When tracking down gnarly problems in heavily multi-user applications
enabling higher log levels at selective points has the potential to help
_a lot_ with diagnostic detail, without smothering you in _every_
detail.
Regards,
Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
Where are they now? http://schoolreunions.co.nz/
-------------------------------------------------------------------------
On Tue, 10 Nov 2004, Greg Stark wrote:
Tom Lane <tgl@sss.pgh.pa.us> writes:
I'd like to propose that we get rid of GUC's USERLIMIT category and
convert all the variables in it to plain SUSET. In my mind, USERLIMIT
is a failed experiment: it's way too complicated, and it still doesn't
do quite what it was intended to do, because there are times when it
can't check whether you're a superuser.The only variables that are in the category are log-verbosity-related:
Would that mean I wouldn't be able to change the logging level on the fly at
all?
I would think you'd still be able to do it through a security definer
wrapper function owned by a superuser.
Andrew McMillan <andrew@catalyst.net.nz> writes:
When tracking down gnarly problems in heavily multi-user applications
enabling higher log levels at selective points has the potential to help
_a lot_ with diagnostic detail, without smothering you in _every_
detail.
Sure. As I pointed out in the other thread, if you want to allow an app
to do this, you can make available a SECURITY DEFINER function that
performs the desired SET on its behalf. By setting execute permissions
on the function and/or including restrictions in the function's code,
you can make this as tight or as loose a loophole as you like. So it's
certainly possible to do what you want in any case. I think the issue
at hand is what's appropriate to provide as hard-wired functionality.
regards, tom lane
Greg Stark <gsstark@mit.edu> writes:
Tom Lane <tgl@sss.pgh.pa.us> writes:
I'd like to propose that we get rid of GUC's USERLIMIT category and
convert all the variables in it to plain SUSET.
Would that mean I wouldn't be able to change the logging level on the fly at
all?
No, it would mean that you'd need to be superuser to change it.
regards, tom lane
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
Would that mean I wouldn't be able to change the logging level on the fly at
all?I would think you'd still be able to do it through a security definer
wrapper function owned by a superuser.
Oh yeah, well that would be sufficient for my purposes.
I must say I thought the behaviour of being able to raise but not lower
logging levels beyond what the system had set was pretty slick when I first
found out about it. But it's not the most important thing in the world, as
long as there's an escape hatch.
--
greg
On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote:
Andrew McMillan <andrew@catalyst.net.nz> writes:
When tracking down gnarly problems in heavily multi-user applications
enabling higher log levels at selective points has the potential to help
_a lot_ with diagnostic detail, without smothering you in _every_
detail.Sure. As I pointed out in the other thread, if you want to allow an app
to do this, you can make available a SECURITY DEFINER function that
performs the desired SET on its behalf. By setting execute permissions
on the function and/or including restrictions in the function's code,
you can make this as tight or as loose a loophole as you like. So it's
certainly possible to do what you want in any case. I think the issue
at hand is what's appropriate to provide as hard-wired functionality.
That sounds excellent - I hadn't realised that this workaround would be
possible, and indeed with this in place that will provide even better
control over the facility.
Regards,
Andrew.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
The secret of being a bore is to say everything -- Voltaire
-------------------------------------------------------------------------
Andrew McMillan wrote:
-- Start of PGP signed section.
On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote:
Andrew McMillan <andrew@catalyst.net.nz> writes:
When tracking down gnarly problems in heavily multi-user applications
enabling higher log levels at selective points has the potential to help
_a lot_ with diagnostic detail, without smothering you in _every_
detail.Sure. As I pointed out in the other thread, if you want to allow an app
to do this, you can make available a SECURITY DEFINER function that
performs the desired SET on its behalf. By setting execute permissions
on the function and/or including restrictions in the function's code,
you can make this as tight or as loose a loophole as you like. So it's
certainly possible to do what you want in any case. I think the issue
at hand is what's appropriate to provide as hard-wired functionality.That sounds excellent - I hadn't realised that this workaround would be
possible, and indeed with this in place that will provide even better
control over the facility.
OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you
were the person who originally mentioned the problem. You don't think
the function creation is hard. Perhaps that's the way to go then.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Thu, 2004-11-11 at 23:05 -0500, Bruce Momjian wrote:
Andrew McMillan wrote:
-- Start of PGP signed section.On Wed, 2004-11-10 at 11:45 -0500, Tom Lane wrote:
Andrew McMillan <andrew@catalyst.net.nz> writes:
When tracking down gnarly problems in heavily multi-user applications
enabling higher log levels at selective points has the potential to help
_a lot_ with diagnostic detail, without smothering you in _every_
detail.Sure. As I pointed out in the other thread, if you want to allow an app
to do this, you can make available a SECURITY DEFINER function that
performs the desired SET on its behalf. By setting execute permissions
on the function and/or including restrictions in the function's code,
you can make this as tight or as loose a loophole as you like. So it's
certainly possible to do what you want in any case. I think the issue
at hand is what's appropriate to provide as hard-wired functionality.That sounds excellent - I hadn't realised that this workaround would be
possible, and indeed with this in place that will provide even better
control over the facility.OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you
were the person who originally mentioned the problem. You don't think
the function creation is hard. Perhaps that's the way to go then.
Yes, I agree - it seems good.
Also, I don't see that this function would need to be "written under
stress" as ISTR you suggested elsewhere - any analysis like this is
going to be following on from review of other statistics - I think it
would normally be a well-planned process.
Cheers,
Andrew.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
How many things I can do without! -- Socrates
-------------------------------------------------------------------------
Andrew McMillan wrote:
That sounds excellent - I hadn't realised that this workaround would be
possible, and indeed with this in place that will provide even better
control over the facility.OK, here is one vote for the ALTER USER/remove USERLIMIT croud, and you
were the person who originally mentioned the problem. You don't think
the function creation is hard. Perhaps that's the way to go then.Yes, I agree - it seems good.
Also, I don't see that this function would need to be "written under
stress" as ISTR you suggested elsewhere - any analysis like this is
going to be following on from review of other statistics - I think it
would normally be a well-planned process.
OK, Tom please go ahead with the patch.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073